Text to Date - Got most of it but...

[Guest]

I imported a spreadsheet where they didn't format date values... so i get
dates including 12/12/2006, 1/1/2006, 12/1/2006, 1/12/2006 (all mo/day/yr)
and I'm trying to import them in as a date value. Here's what I did that
works until I get a 1/1/2006 type of entry
Format(DateSerial(Right([f1],4),Left([f1],InStr([f1],"/")-1),Mid([f1],InStr([f1],"/")+1,InStrRev([f1],"/")-4)),"mm/dd/yyyy")
so the year and month is no prob; I just need help with the middle 'day'
value when they can be 1 or 2 digits....thanks

 

[Guest]

You're working too hard! The CDate funtion will convert a string to a date
providing that the string can be seen as a valid date. That they are all
month-day-year format is a very good thing.

Debug.Print CDate("1/1/2006") = 1/1/2006
Debug.Print CDate("01/01/2006") = 1/1/2006

Now one problem with CDate is that it bombs out on nulls, empty strings, or
invalid dates with invalid use of null or type mismatch errors. Therefore I
use the IsDate function to check first. Something like this in a query

ChangeDate: IIf(IsDate([datefield])=True,Cdate([datefield]),#1/1/1950#)

The above will either convert the string to a date OR plug in the bogus date
of 1/1/1950 if it can not. Then you could go back and "fix" those bogus days.

 

[Guest]

yeah, I tried that... so what I figured out was that I had a copy of the [F1]
field in a different location and was trying to filter out the text labels
and nulls in the date field, so it was messing the query up. So I went in,
deleted the extra F1 field, but had to use an additional query to filter out
nulls and text labels before using the date conversion query.

You're working too hard! The CDate funtion will convert a string to a date
providing that the string can be seen as a valid date. That they are all
month-day-year format is a very good thing.

Debug.Print CDate("1/1/2006") = 1/1/2006
Debug.Print CDate("01/01/2006") = 1/1/2006

Now one problem with CDate is that it bombs out on nulls, empty strings, or
invalid dates with invalid use of null or type mismatch errors. Therefore I
use the IsDate function to check first. Something like this in a query

ChangeDate: IIf(IsDate([datefield])=True,Cdate([datefield]),#1/1/1950#)

The above will either convert the string to a date OR plug in the bogus date
of 1/1/1950 if it can not. Then you could go back and "fix" those bogus days.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I imported a spreadsheet where they didn't format date values... so i get
dates including 12/12/2006, 1/1/2006, 12/1/2006, 1/12/2006 (all mo/day/yr)
and I'm trying to import them in as a date value. Here's what I did that
works until I get a 1/1/2006 type of entry:
Format(DateSerial(Right([f1],4),Left([f1],InStr([f1],"/")-1),Mid([f1],InStr([f1],"/")+1,InStrRev([f1],"/")-4)),"mm/dd/yyyy")
so the year and month is no prob; I just need help with the middle 'day'
value when they can be 1 or 2 digits....thanks