Another Date Problem

[skiphooper]

Hi Everyone,

I have some text coming in a delimited file. there are 3 date fields. Here are the fields as they come in, and the results I received.

120301 results 05/15/2229
12-10-01 results 12/10/2001
12192001 results overflow

my system short date is set to MM/dd/yy
long date dddd,MMMM dd,yyyy

my format string is.
mydate = format(mytext, ""mm/dd/yyyy")

what am doing wrong.

Thanks in Advance
Skip

 

[Alexandre]

Quoting Pat Hartman:

Dates are stored internally as serial numbers with the integer portion representing the date and the decimal portion representing the time. Negative values are used for dates prior to Dec 31, 1899. The only thing the date format affects is how a date is displayed. It has nothing to do with how the date is stored.

[It had to happen sooner or later, Pat. That is what we call 'La rançon de la gloire'

]

Alex

 

[skiphooper]

Alex,

Now that you got that out of the way,

try reading what I wrote.

I'm reading in text from a .txt file and trying to create
a date field in a table.

the first one creates a date of 05/15/2229
the second one is a correct date
the last one doesn't create anything.


Skip

[This message has been edited by skiphooper (edited 02-27-2002).]

[This message has been edited by skiphooper (edited 02-27-2002).]

 

[jatfill]

Since you're dealing with text, you may need to split the field & reassemble it, i.e. something like:

For the first one:

Format(Left(strDate,2) & "/" & Mid(strdate,3,2) & "/" & Right(strDate, 2), "Short Date")


The third (the first one would actually work for both one and three, unless you have a wild range of dates that you're dealing with and want to make sure that you grab the correct year):

Format(Left(strDate,2) & "/" & Mid(strdate,3,2) & "/" & Right(strDate, 4), "Short Date")

It sounds like since you have varying date formats, you'll either need to evaluate each string & go from there, or possibly convert them all to a fixed format string prior to doing the import. Are these the only three formats you'll be getting, and if so, are they consistent? If this is the case, it shouldn't be too hard to evaulate each type, since they're so much different:

If Mid(strDate, 3) = "-" Then
'Date is good... keep it & continue
Else
'Run the statement above


replacing "strDate" with the variable/information in the date field, of course...

good luck!

 

[Alexandre]

Think a bit about it and use your little fingers. You will deduce from the above that when you deal with dates Access will try to interpret integers and strings as dates, as far as possible:
- Integers : If 0 is 31/12/1899, 120301 is ... 05/15/2229. 12192001 is out of scope since it would be beyond year 35270 and date type are limited to year 9999 (see access help)
- String: When correctly formatted, are correctly intepreted: 12-10-01 results 12/10/2001


You will have to do some string processing on your input data to properly convert them into a recognized format like xx-xx-xxxx, and to avoid that Access interprets them as integers.

Alex

[This message has been edited by Alexandre (edited 02-27-2002).]

 

[skiphooper]

Hi jatfill,
Thanks for the reply.

It looks like that's exactly what I'll have to do. There are a few more formats, and one where they only send me the month and year. They dont care what the day is.

Thanks again
Skip

 

[skiphooper]

Alex

Thanks

Skip