Cdate() -->Converting text to date and time

Hello,
First sorry I put this question originally as a response to a similar question and got spanked - now I know better!

I have a large text file that I receive regularly from our CAD server. There are four date/time fields that have to go to text when coming in to access. I have set up a temporary import table and then have a query where I am trying to convert the text date time to a real date time field and then will append it to the actual data table for analysis. The text field [Response_Date] looks like this:

20090301 00:00:32.000

I have added a column next to it and in the Field row of my new column used CDate([Response_Date]). When I run the query I get #Error in the new column.

Any suggestions, help or advice would be greatly appreciated!

Thanks,
Gal

PS - I have also tried:
Cdate(left([Response_Date], 5,2) & "/" &
Mid([Response_Date], 7,2) & "/" &
Mid([Response_Date], 1,4) & " " &
Mid([Response_Date], 10))

But I get the following error:
"The expression you entered contains invalid sytax...."

The last part of your equation you don't need if you are calculating days only. the 00:00:32.000 part of your string is the hours, minutes and seconds which doesn't appear to matter for your purposes.

Thanks for the response Repeare!

Actually, I do use the time also. I will manage how long it takes from response to arriving at the scene and the how long the call took, so I use all of the field.

However, even just using the date portion as suggested, I still get "The expression you entered contains invalid syntax. You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks."

Perhaps I have been starring at this for far too long...but I just can't seem to get this worked out.

Do you have any other suggestions? THANK YOU very much for your help!!

This works for me, I just changed the mid(1,4) statement to the left(4) statement.

Do you have any leading spaces in your field name? that will screw things up. What I would do is check your code before trying to do the cdate function

just build the query so that it says



and see what it gives you. If it's giving you something that is correctly building a string date then you can use the cdate function. I suspect you have leading spaces or some such. You will probably also have to convert the hours, minutes, seconds too I've never dealt with that portion of the date except in the long format like:

12/31/2008 12:34:00 PM

but let's solve the date part first.

Thank you again!
Still same problem/error message. I have renamed the field on both the table and query to ResponseDate (removed the underscore between them) It is formatted as text in the table that the query is using. 20090301 00:00:32.000 it looks just like that...I can not see and leading spaces in the field name or data in the field.

Did you try what I said and remove the cdate expression from your query.

Make a basic query using the table with the date field in it (it doesn't matter what you call it) add only the date field then in a second column put in mid(<date field name>,5,2) and see what it gives you, if it gives you the month portion of your expected date then go from there, if it's giving you something unexpected you may have to trim the field.

For instance if you SEE in your field:

20090301 00:00:32:000

and your expression ( mid(<date field name>,5,2) ) gives you '30' you know your formula is off.

then you could try something like

mid(trim(<date field name>),5,2) to see if it's a problem with leading spaces.

Forget trying to use the cdate function until you figure out if it's building the string correctly.

Not using Cdate and in a brand new query with only [ResponseDate] and the string you provided I still get the invalid sytax error.

Also tried....

Mid(trim([ResponseDate],5,2)

Invalid Sytax.....

This is my test db. See if it runs on your machine.

Mid(trim([ResponseDate],5,2)

This will give you invalid syntax you have to have () around the responsedate so it would be

mid(trim([responsedate]),5,2)

It works just fine. I just don't understand! This is crazy!!

Still invalid sytax

I sent you a PM with one of my email addresses. Send me a copy of your db and I'll post it for you.

Your current Expr1 says:

Expr1: cdate([responsedate])

I changed it to

Expr1: CDate(Mid([ResponseDate],5,2) & "/" & Mid([responsedate],7,2) & "/" & Left([responsedate],4))

and it works just fine.

Apparently I need to quit bleaching my hair-it is starting to seep in to the brain cells! THANK YOU so much for spending time on this with me. It works great!

Take care...gal...