US English Date problem

[SteveOll]

Hi All

I’m fairly new to Access so forgive me if this is a newbie question.

Access 2003 SP2
Excel 2003 SP2
Keyboard English UK

I have a software package that produces an Excel sheet with the date displayed as follows in the Created At column “02/18/2007 @ 03:53pm” the field is in text format. I’m not interested in the time stamp so I have a query in Access to strip out the text I am interested in Query “Date_US: Left([Created At],10) “
This gives me the date but its still in text format, my problem is if I change it to be date it stores the date incorrectly. For example 02/05/2007 is changed to 2nd of May when it’s really the 5th Feb not until the day is above the number of months does it display it correctly. I understand why it is doing this but don’t know how to correct it.

Any help greatly received

Steve

 

[RuralGuy]

Dates are *always* stored the same way but can be displayed any way you want. This link will be of some help and CDate() can convert your string to a date. If you surround a string with the octothorpe "#" then Access will convert it to a date but it can make some incorrect assumptions on ambiguous dates and leans towards the American format. Access has a ton of functions to deal with dates. Use VBA help to look up DateSerial() and then all of the see also choices.

 

[Dennisk]

you could use Left$/Mid$/Right$ to change the order of the Day/Month or the dateSerial function will do it.

 

[SteveOll]

Solution

Hi All

Thanks for the replies, below is what I used and it seems to work


CMonth_Ex: Abs(Left([created at],2))

CDay_Ex: Abs(Mid([created at],4,2))

CYear_Ex: Abs(Mid([created at],7,4))

Create Date: Format(([Cday_ex] & "/" & [Cmonth_ex] & "/" & [CYear_ex]),"dd/mm/yyyy")

 

[RuralGuy]

There are pleanty of ways to skin a cat in Access. Glad you got it sorted.