Convert text to dates

[BrendaR]

Hello. I see many similar topics but unfortunately I still am doing something wrong.

My date field is in text format and looks like this:
20121102 (yyyymmdd)

Here is what I've tried:
DATEVALUE(LEFT([FIELDNAME],4),MID([FIELDNAME],5,2),RIGHT([FIELDNAME],2))
Result: The expression you entered has a function containing the wrong number of arguments

DATEVALUE(LEFT([FIELDNAME],8))
Result: Enter parameter value

DATEVALUE(MID([FIELDNAME],5,2)&"/"&MID([FIELDNAME],7,2)&"/"&LEFT([FIELDNAME],4))
Result: Enter parameter value

The conversion works in Excel using this:
DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

We just updated our system which uses this date format in all files so I need to revise all of my existing queries as they are not working. Your help is appreciated!

 

[pbaldy]

Try the first one with DateSerial instead of DateValue.

 

[BrendaR]

Thank you, Paul. I tried that but it still asks me for a parameter value for my date field.

 

[pbaldy]

Where are you using it? The parameter prompt is Access telling you it can't find the field. You either have it spelled wrong or are using it someplace where it's not available.

 

[BrendaR]

Ah, one problem solved. The field is for "origin date" which is what I used in my expression, but they named it "ORGIN DATE" (it's a UDF), so that's what happened.

However, I tried DateSerial and the result is a column of #Error, and the DateValue still says my expression has a function containing the wrong number of arguments.

 

[pbaldy]

Can you post the db here?

 

[pbaldy]

Is CDate() more efficient than DateSerial() Pat? Seems like the concatenation would make it less so, but I haven't tested.

 

[BrendaR]

Here is a mini database with the date formats.

 

[pbaldy]

This works:

SELECT [test table].ITEMCODE, [test table].UDF_RETIRE_DATE, [test table].UDF_ORGIN_DATE, [test table].DATECREATED, [test table].DATEUPDATED,DateSerial(LEFT([UDF_ORGIN_DATE],4),MID([UDF_ORGIN_DATE],5,2),RIGHT([UDF_ORGIN_DATE],2)) As Whatever
FROM [test table];

But will error on the empty fields, which is easy enough to get around if they could exist.

 

[BrendaR]

Yes, it does work! You are wonderful, thank you.

Apparently it had worked before, the #Error was because the field for those particular rows did not have any data in the column I was converting. I just didn't page down far enough.

Thanks so much!
Brenda

 

[pbaldy]

Happy to help! One of many ways to avoid the error on empty fields:

IIf(Len([UDF_ORGIN_DATE] & "")=0,Null, DateSerial(Left([UDF_ORGIN_DATE],4),Mid([UDF_ORGIN_DATE],5,2),Right([UDF_ORGIN_DATE],2)))

 

[mdlueck]

Use cDate() and concatenate slashes as separators between the date parts.

To avoid Access date nonsense, I usually treat dates as a text string in Access / VBA. When I have need to make them really a date, then I use CDate() to convert the date string to an actual date datatype.

So "+1" for the CDate recommendation to convert text dates back to date dates.

 

[pbaldy]

I'd still like to hear why CDate() would be preferable to DateSerial(). A brief search would indicate it is slower:

http://www.tek-tips.com/viewthread.cfm?qid=820165

 

[boblarson]

And I use DateSerial to be consistent across my code since DateSerial handles leap years and other things that CDate doesn't like. Like getting the last day of the month and when the year changes doing
DateSerial(Year(Date()), 1, 0) yields December 31st of last year.

 

[mdlueck]

And I use DateSerial to be consistent across my code since DateSerial handles leap years and other things that CDate doesn't like.

But to translate "01/02/2012" into the date datatype, I would certainly hope that calling CDate for that trivial task would be safe, no?

I could see your point about using DateSerial for more abstract calculations, such as you provided an example of.

As I use C*() type functions for other casting operations, CDate for me was the obvious choice.