date format problem

  • Thread starter Thread starter vik
  • Start date Start date
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
 
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
 
In one important sense, you cannot do what you want to do, since the
two formats do not encode identical data - the second format does not
contain century information. You can convert the first format into the
second (losing the century information) by:

strDate2 = Format(dtDate1,"dd.MM.yy") ' The case of "MM" is important

To do the conversion the other way, you need to know the century of
the dates, when:

dtDate1 = DateSerial(Century + Cint(Mid$(strDate2,7)),
Cint(Mid$(strDate2,4,2)), Cint(Mid$(strdate2,1,2)))

The above should be on a single line.


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
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
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
 
I was confused!

I have recently been doing a fair amount of programming in VB for
..NET. In that context, the case of an "M" in a date/time format string
for the Format () Function does matter, since lower case gives you
minutes and upper case gives you months!
<Goes off muttering to himself....>

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
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top