In Query: Add time and date together & getting error

[oliviar]

Hi Guys,
I have a date column and a time column with values in them. I want to add the two together to produce a date AND time column. I don't care if its formatted as a number.
When I try it the 'easy' (and clearly wrong) way, I get an error.

What I'm doing is:
Expr1: [date]+[time]

What SHOULD I be doing?

Thanks

 

[xenou]

Maybe:
Exp1: CDate([Date]) + CDate([Time])
Does that work? If not, what are the data types of the fields called here [Date] and [Time]?

Note: I would urge you not to name columns Date or Time as these are reserved words that refer to the VBA Date and VBA Time functions. They introduce difficult to trace errors at times when used as column names. Perhaps you chose these terms here only by way of example.

ξ

 

[oliviar]

Hi Xenou,
They are not called date and time, they are [etc etc etc etc date mmddyy] and [etc etc etc etc time hhmm]. I just called them that in here to make it easier to write.

They are both Date/Time fields, which is why I am so confused they won't just add together.

 

[xenou]

I'm afraid to tell you that it works fine for me:

[MyDate] --> a datetime field with value 5/5/2011
[MyTime] --> a datetime field with value 5:11 PM

Query:
Select [MyDate] + [MyTime] As Exp1 FROM Table1;

Result:
5/5/2011 5:11 PM


Note that also fine is:
Select CDate([MyDate]) + CDate([MyTime]) As Exp1 FROM Table1;
As per my original post.

 

[oliviar]

HI Xenou,
Your Cdate solution works for me too. Thank you so much.
But still, trying to add them straight up only produces an error.

What IS that Cdate function doing that is so special?

 

[xenou]

Are these fields in a table?
ξ

 

[oliviar]

A linked table, in a read only excel file. But not an imported table in Access.

 

[xenou]

No idea really. CDate is simply a good "precaution" in case Access doesn't know the "dates" really are dates.