Convert Sring Into Date Fomat

[MadMax7]

I hope some one can help

I currently get sent a table daily that has a field (Trans date)that is formated as text and has dates in the format 23May2005. what i need to be able to do is convert this date into a correct date format ie 22/05/2005 so that i can query the data using a calender control.

 

[KenReay]

Hi

try this

Make a Query based on your table, include a caculated column:

ADate:CDate(left(txtdate,2)& "/" & Mid(txtdate,3,3) & "/" & mid(txtDate,6,4))

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MadMax7]

Thank you Ken that works fine

 

[MadMax7]

Ken

the initial part of the query works fine but i was wondering if there is a way to change the number of characters it looks at in the mid section of the code automatically for example

for next month the data is going to start to say 01June2005 so i will need to look at the middle 5 characters

 

[PHV]

Quick and dirty (typed, untested):
ADate:CDate(Left(txtdate,2) & "/" & Mid(txtdate,3,InStr(3,txtdate,"2")-3) & "/" & Right(txtdate,4))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Perhaps simpler:
ADate: CDate(Left(txtdate,2) & "/" & Mid(txtdate,3,Len(txtdate)-6) & "/" & Right(txtdate,4)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886