Convert Dates

[ncook12]

Can someone help me on this.
I have some raw date in a csv format that I import into Access. The raw data has a dat format of MM/DD/YYYY hh:mm:ss
but the field can not be specified as a date.
When i import to access I have an update query that removes the extra information so that I can specifi the field as a date. Hoever even though i select the long date as the field type it still displays the date as MM/DD/YYYY. I want it as DD/MM/YYYY.

Can this be done as it is throwing all the data out wrong.
Thanks In Advance

 

[DrSimon]

Assuming it really is now a date field, use [green]format(Datefield,"DD/MM/YYY")[/green]

 

[SkipVought]

Hi,

If the field "can not be specified as a date" then it is NOT a date. It must be TEXT, correct?

If it were me, I'd create a new column for the date and convert the text to a date
[tt]
DateSerial(mid([DateStr],7, 4), left(([DateStr],1,2), mid(([DateStr],4,2))
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[DrSimon]

Good point Skip, though I find I often have to validate dates from other sources because you can't always rely on the format.
If the raw date data is always a fixed format then go for Skip's suggestion.