I'm importing spreadsheet into Access using a routine
that process the data a line at a time so I can validate
each record. I've got one field that is a date field.
These spreadsheets come from many sources and are
inconsistent. When I look at two spreadsheets from
different sources the dates look the same and have the
same format 'yyyymmdd'. However, when I put the data into
an access table sometimes it goes in correctly and
sometimes it goes in with day and month reversed. Could
this be because the system these spreadsheets were
created on have different region settings and how can I
tell.

Any clues to this would be appreciated.
Paul

Look at the dateserial number stored in the cell to get the date.

Use the Value2 property of the range.
--
Regards,
Tom Ogilvy

Paul wrote in message
...
I'm importing spreadsheet into Access using a routine
that process the data a line at a time so I can validate
each record. I've got one field that is a date field.
These spreadsheets come from many sources and are
inconsistent. When I look at two spreadsheets from
different sources the dates look the same and have the
same format 'yyyymmdd'. However, when I put the data into
an access table sometimes it goes in correctly and
sometimes it goes in with day and month reversed. Could
this be because the system these spreadsheets were
created on have different region settings and how can I
tell.

Any clues to this would be appreciated.
Paul

Tom,

I've looked at value2 and the number is correct for the
date that's in there. I've tried to put value2 in the
acces table and it still reverses the month and day. I'm
completely bewildered by this.

Paul
-----Original Message-----
Look at the dateserial number stored in the cell to get
the date.

Use the Value2 property of the range.
--
Regards,
Tom Ogilvy

Paul wrote in message
...
I'm importing spreadsheet into Access using a routine
that process the data a line at a time so I can
validate
each record. I've got one field that is a date field.
These spreadsheets come from many sources and are
inconsistent. When I look at two spreadsheets from
different sources the dates look the same and have the
same format 'yyyymmdd'. However, when I put the data
into
an access table sometimes it goes in correctly and
sometimes it goes in with day and month reversed. Could
this be because the system these spreadsheets were
created on have different region settings and how can I
tell.

Any clues to this would be appreciated.
Paul


.

I don't know how access treats or stores dates, but you can convert the
serial to a date string in any format you want; so try using something that
can't be misinterpreted

format(cell.Value2,"mmm dd, yyyy")

--
Regards,
Tom Ogilvy




Paul wrote in message
...
Tom,

I've looked at value2 and the number is correct for the
date that's in there. I've tried to put value2 in the
acces table and it still reverses the month and day. I'm
completely bewildered by this.

Paul
-----Original Message-----
Look at the dateserial number stored in the cell to get
the date.

Use the Value2 property of the range.
--
Regards,
Tom Ogilvy

Paul wrote in message
...
I'm importing spreadsheet into Access using a routine
that process the data a line at a time so I can
validate
each record. I've got one field that is a date field.
These spreadsheets come from many sources and are
inconsistent. When I look at two spreadsheets from
different sources the dates look the same and have the
same format 'yyyymmdd'. However, when I put the data
into
an access table sometimes it goes in correctly and
sometimes it goes in with day and month reversed. Could
this be because the system these spreadsheets were
created on have different region settings and how can I
tell.

Any clues to this would be appreciated.
Paul


.

Tom,

I've tried that. This is bizarre becasue from one
spreadsheet it works and from another it doesn't. I've
not got much hair as it is but I'm losing it all on this
one.

Paul
-----Original Message-----
I don't know how access treats or stores dates, but you
can convert the
serial to a date string in any format you want; so try
using something that
can't be misinterpreted

format(cell.Value2,"mmm dd, yyyy")

--
Regards,
Tom Ogilvy




Paul wrote in message
...
Tom,

I've looked at value2 and the number is correct for the
date that's in there. I've tried to put value2 in the
acces table and it still reverses the month and day.
I'm
completely bewildered by this.

Paul
-----Original Message-----
Look at the dateserial number stored in the cell to
get
the date.

Use the Value2 property of the range.
--
Regards,
Tom Ogilvy

Paul wrote in message
...
I'm importing spreadsheet into Access using a
routine
that process the data a line at a time so I can
validate
each record. I've got one field that is a date
field.
These spreadsheets come from many sources and are
inconsistent. When I look at two spreadsheets from
different sources the dates look the same and have
the
same format 'yyyymmdd'. However, when I put the data
into
an access table sometimes it goes in correctly and
sometimes it goes in with day and month reversed.
Could
this be because the system these spreadsheets were
created on have different region settings and how
can I
tell.

Any clues to this would be appreciated.
Paul


.



.