Need Help Converting a Date that is defined as Text into a date field so I can calc (1 Viewer)

mbranham

New member
Local time
Yesterday, 18:29
Joined
Nov 7, 2014
Messages
4
I have a table from an outside database. The date/time is stored as a text field in the format of 2014-06-18 08:00:00.0000000.

I have a start time and an end time field, both in that format. I need to calculate the time between, but it won't do it because it is a text field.

I am trying to build an Access Query to do calculations. If I try to use CDate in my query to convert the field, I get a compile error. Here is the code that I'm using in my query:

Start: CDate ( Mid( [tmpMelony]![staffing_start_dt] , 6, 2) & "/"& Mid( [tmpMelony]![staffing_start_dt] , 9, 2) &"/"& Left( [tmpMelony]![staffing_start_dt] , 4) )

I get a Compile Error.

Please help!
 
just get the str 2014-06-18 from 2014-06-18 08:00:00.0000000 and place it into a cDate.

CDate("2014-06-18") should work no?
 
No, that won't give me what I need. I need to calculate the number of hours between the 2 fields, start.date and end.date. But the field is stored as a text field and it's formatted like 2014-06-18 08:00:00.0000000.
 
FYI, moved from watercooler.

I think CDate() will work, but you have to trim off the last bit:

?cdate("2014-06-18 08:00:00")
6/18/2014 8:00:00 AM
 
So PBaldy,

On what you put

?cdate("2014-06-18 08:00:00")
6/18/2014 8:00:00 AM

Would I replace what you have in quotes with my field name? I tried that, but I am getting a compile error.
 
It would help to see what you actually tried. It should work with the field name (without quotes), modified to drop the portion after the decimal.
 
I got it to work. Thank you so much for all of your help.

Here is what I ended up doing. I did this on both fields and then calculated the new fields. Works like a charm :)

Start: CDate(Left([staffing_start_dt],19))
 

Users who are viewing this thread

Back
Top Bottom