CDate function to convert from dd.mm.yyyy format

[GregTheSquarePeg]

Hello,



I have a linked text file that imports all fileds as text type. I want to use CDate (or possibly CVDate?) to convert the text dates to a real date format (not just to look like dates)

My text dates are in this format: dd.mm.yyyy
I would like to convert them to dates like: dd/mm/yyyy

When I use criteria such as: CDate([FieldName]) - the function fails to convert the dates.
My query is appending to a 'template' table that has the relevant field set to the Date/Time format.

Any idea's?

Many thanks,
Gregg

[Kirtap]

When I try convertedDate = CDate(Format("03.04.1988", "mm/dd/yyyy")), I get a Type Mismatch Error.
If your dates are text, might I recommend a Replace function to replace the /'s.
So it'd be like CDate(Format(Replace("03.04.1988", "mm/dd/yyyy"),".","/"))?
When I did a message box, this worked.

[GregTheSquarePeg]

Hi Kirtap,

Thanks for taking the time to look at this.

So following your suggestion, we have: CDate(Format(Replace([Created on],"mm/dd/yyyy"),".","/"))

Although this comes back with error: The expression you entered has a function containing the wrong number of arguments

Could it be because I'm using a field name ([Created on]) rather than an actual dd.mm.yyyy string in the first criteria?

Thanks,
Gregg

[Kirtap]

Wrong number of arguements? I think I might've posted things wrong.

CDate(Format(Replace([Created On],".","/"), "mm/dd/yyyy"))

My bad, that's what happens when I copy my text wrong, edit it multiple times. lol

Edit: Also, if its a field name, maybe you need to add .Value?

[Bob Fitz]

Gregg

I have a linked text file that imports all fileds as text type. I want to use CDate (or possibly CVDate?) to convert the text dates to a real date format (not just to look like dates)

My text dates are in this format: dd.mm.yyyy
I would like to convert them to dates like: dd/mm/yyyy

Access does not store dates in either format, rather as a number. The CDate function converts anything that access can read as a date into such a number. The help files say:

Any expression that can be interpreted as a date, including date literals, numbers that look like dates, strings that look like dates, and dates returned from functions. A date expression is limited to numbers or strings, in any combination, that can represent a date from January 1, 100 – December 31, 9999.
Dates are stored as part of a real number. Values to the left of the decimal represent the date; values to the right of the decimal represent the time. Negative numbers represent dates prior to December 30, 1899.

So CDate will convert a value to a numeric value. How you see that value (dd.mm.yyyy or dd/mm/yyyy or mm/dd/yyyy etc) is only about formatting.

[GregTheSquarePeg]

Kirtap - thank you very much, that works a treat (although I was confused for a second - you changed the date format to mm/dd in your reply, serves me right for being lazy, copying and pasting rather than editing my own expression myself!)
Thanks again for your help.

Bob - thanks for the info - I had read the help section, but struggled slightly to process Microsofts explanation. I better understand it now.

Cheers both, keep up the good work.

Gregg

[ItsMe]

Gregg, just some FYI

As long as the text you are placing into your field with data type "Date" is in the correct order, i.e. mm/dd/yy, there should not be a problem. If your SQL grabs text that is in a different order, I believe this is a problem.

For example, Oct 1, 2013 may look like this: 01.10.2013

Paste 01/10/2013 into a date field and you will get Jan 10, 2013. Access determines this as American format.

Paste 30/01/2013 into a date field and you will get Jan 30, 2013 because the day is >12.

Example 2 in this Allen Browne link touches on the subject of international date formatting.
http://allenbrowne.com/ser-36.html

[GregTheSquarePeg]

Thanks ItsMe, well explained. Will have a look at the link too.

Gregg