Data Missing when Exporting from Access to Excel

[merry_fay]

I've written a database with a button to a couple of tables to a floppy in
excel format. Unfortunately sometimes, some of the data does not export -the
row is still there but just one of the dates on that row doesn't export.
On the other side, I have the same problem with importing -sometimes, not
all the time a date doesn't import. How can I stop this happening? -this
missing information could affect peoples bonuses & jobs.

 

[John Nurick]

There must be something distinctive about the dates or rows that are not
importing/exporting. Can you identify it?

In the database, are the dates stored in date/time fields or in text
fields?

When importing from Excel sheets, is it possible that the problematic
dates are actually text values rather than date/time values?

 

[merry_fay]

That's the first thing I checked. They are definitely date/time fields in
both the table being exported from to excel & then on the table being
exported to on another machine.
The most awkward bit is they don't always do it -exactly the same field can
export OK one week & not the next with no changes being made to it.

Thanks!

 

[John Nurick]

I don't understand what you mean by "the table being exported to on
another machine", and, in your original post, by "on the other side".
Can you give more, and more precise, information on exactly what you are
doing and how things aren't coming out as you expect?

Please also give examples of particular date values in an Access
date/time field that seem not to be being exported correctly to Excel,
along with the actual values that end up in the corresponding cell.

Are you exporting a table or a query? How are you doing it (macro, VBA
code, SQL statement...)?

What versions and service packs of Windows and Office are you using?

Does the data export correctly if the destination is a hard drive rather
than a floppy?

 

[merry_fay]

Hiya
-a number of individuals each have a database on their laptops.
They click on a button which activates a macro to export 2 tables onto a
floppy. This floppy is then sent to a 'team leader' who has a button on their
database to run a macro to import the 2 tables from the floppy to tables in
their database, collating the data.
The 'team leaders' then, using the same method send the data to me & I
collate it for everybody.
Some people are using the 97 version of access while others have xp, but
this doesn't seem to make a difference, neither does exporting it to the hard
drive instead.
All dates are set in the format dd/mm/yyyy, the most recent example of a
missing date brought to my attention is 01/08/2005 -I have been checking they
are entered correctly when it's noticed they're missing.
Thanks

 

[John Nurick]

I've never encountered this problem before, and so far can't find any
other reports of it that apply to multiple databases, multiple machines,
more than one version of Access, and both importing and exporting.

You say that a value that may export OK one day may disappear another
day. When you export the same data multiple times from the same database
without updating the database in between, are the exported tables
identical or do their contents vary? If they vary, what are the
differences and what do they tell you about what's happening? If they're
identical, does that suggest that the problem is in the way the
databases are being used, i.e. the users?

Here are some of the things I'd try:

1) Check the macros to make sure they're not doing anything silly.

2) Compact and repair the databases.

3) Make sure that the latest Jet service packs are installed.

4) Use CSV as the "transport" medium rather than Excel.