date format problem

V

vik

I was wondering if anyone can help. I have two files with
dates in them that I would like to cross match. One is an
odbc link with date in the format dd/mm/yyyy, the other is
an imported text file with date in the format dd.mm.yy.
Can anyone help me to change them to the same format?
Thanks
 
M

Michel Walsh

Hi,


Format is generally not the real stuff, it is just a decoration based on the
real data, hidden behind the "format". The odbc link probably supply data
which is, after, displayed using your regional setting, as dd/mm/yyyy, but
with another setting, say like a US setting, it would be mm/dd/yyyy (without
changing anything else) or, with a Finland's setting, dd.mm.yyyy. On the
other hand, the second imported text file is "probably" text, not a real
DATE (as far as the pc understand it)... so, changing your regional setting
won't change it, as it did for your first data supplied through the odbc
link. We can work on that string (it is a poor idea to change the regional
setting, or to force one in particular).

Left(mystring, 2), & "/" & Mid(myString, 3, 2) & "/" &
if( Mid(myString, 7, 1) <= "3", "20", "19" ) & Right(myString, 2)


should supply a string in the format dd/mm/yyyy where the century is:
1940-2039 (required to fill the four year digits, based on the only two
we have)


Note that assumes you always have two digits for the day and for the month:
02.08.04 and not 2.8.4


Hoping it may help,
Vanderghast, Access MVP
 
J

John Spencer (MVP)

Pardon me.

Why do you say "The case of "MM" is important"?

I've never noticed that is makes a difference in Access. Is there something I
have missed?

Sincerely,

John Spencer
 
Top