Converting a date and time string to date/time format

[dmorand]

I've got two fields in my database. There are date and time fields, but they are both type text. I need to concatenate these fields together and convert them to a date/time data type. I've tried a few things in my query, but am not having any luck. How should I go about doing this?

[missinglinq]

In a blank field in your query

CombinedDateTime: [YourDateField] & " " & [YourTimeField]
 

Then, in a form or report, use CombinedDateTime as you would any other field. You don't say what you'll be using it for, but the field will work in Access date functions like DateAdd() and DateDiff() without doing anything else. If you should have trouble with it in your particular situation, you could coerce it to a Date datatype by using CDate(CombinedDateTime)

Linq ;0)>

[dmorand]

In a blank field in your query

CombinedDateTime: [YourDateField] & " " & [YourTimeField]
 

Then, in a form or report, use CombinedDateTime as you would any other field. You don't say what you'll be using it for, but the field will work in Access date functions like DateAdd() and DateDiff() without doing anything else. If you should have trouble with it in your particular situation, you could coerce it to a Date datatype by using CDate(CombinedDateTime)

Linq ;0)>

CDate will only convert a string to a date. It won't do a time field as well will it?

[missinglinq]

Not sure exactly what you're trying to do, or exactly what you mean by a "time" field, but in point of fact, there is no such thing in Access as a "Time" field! All fields defined in the underlying table to a hold Date and/or Time values are actually Date/Time datatype.

If only the "date" part is entered, Access completes the field by adding 00:00:00 for hours/minutes/seconds. The opposite is true as well. If a "time" is entered, Access adds the default date of 12/30/1899 to it.

Access internally stores Date/Times as Doubles. 1/21/2008 is actually stored as 39468, which is the number of days since the previously mentioned 12/30/1899. The time part is stored as the part of a day, so that 1/21/2008 12:00:00 (noon) would be stored as 39468.5.

As I said before, for most functions Access will accept a "date" if it looks like a date, and a "time" if it looks like a time. CDate() will coerce any expression into a date that can be reasonably be interrpreted as a date.

CDate("1/21/2008") will return 1/21/2008, even though "1/21/2008" is a string. CDate(39468) will also return 1/21/2008 a date because, in Access' eyes, the expression 39468 can be interpreted as a date.

When do you use CDate()? Don't really know; I've never had to. Perhaps in SQL which is not as forgiving as VBA.

Linq ;0)>

[dmorand]

I found out that the issue is the time I'm trying to piece together with the date is in military time, and not a 12 hour format (AM/PM). I am hoping there's an easy way to go from military to 12 hour.

[missinglinq]

You do that with formatting. If the value in YourDateField is

12/31/2007 13:45:00

then

Format(YourDateField, "mm/dd/yyyy hh:hh:ss am/pm")

will return

12/31/2007 1:45:00 pm

Linq ;0)>

[dmorand]

Thanks, let me give that a try