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

M

Maarkr

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
 
J

Jerry Whittle

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.
 
M

Maarkr

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.

Jerry Whittle said:
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.

Maarkr said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top