field conversion

[MarcoPerez21]

I need to convert a text field containing a date to a Date/Time field.... this is coming from an odbc link that I cannot manipulate.

thanks in advance for your help!

 

[jrbarnett]

Use the CDate function

datDatefield = CDate (Fieldname)

John

 

[MarcoPerez21]

thanks for the tip, but I am still not getting the correct output, I have keyed this expression in:

ticketdae: cdate(mid([ticketdate],5,2) & right([ticketdate],2) & left([ticketdate]),2))

whwn i run the querie i get this error:
data type mismatch in the criteria expression.

why is this so?

thanks for your help John it is much appreciated!!!

 

[jrbarnett]

What format is the text in?

If it is a recognised format with day, month and year as numbers or 3 character abbreviations, CDate should do it with no further work, but if not, or it interprets US dates as UK ones or vice versa then further work may be needed.

John

 

[MarcoPerez21]

the format for "ticketdate" is text on on the odbc table that I'm runnning theis qry on. the way the date is stored in the field is as follows:
20030101 (the field is format is text though) & what i need it to do be is 01/01/2003 or something of the like.

I'm a bit puzzled as to why the cdate() expression that i have above is not giving me the correct results.

thanks John for your help!!

 

[amorous]

you may want to try this:

converteddate= Right("ticketdate", 2) & "/" & Mid("ticketdate", 5, 2) & "/" &
Left("ticketdate", 4)

Hope this helps.

VJ

 

[jrbarnett]

The reason the CDate function wouldn't work with items such as 20030101 is that there is no delimiter between items.
Amorous' solution would fix it - assuming that the format is:
ddmmyyyy or mmddyyyy

Please note that the way it will be interpreted is down to the regional settings in Control panel. For example, while 20030101 makes no difference, 20030625 makes a big difference, because there are not 25 months in a year.

John