Date and Time ?

[danbl]

I have a fiield that has date and time (00/00/000 00:00). I want to take the date portion and combined it with another time value.

How can this be done?

Thanks

 

[EMP]

DateAndNewTime: DateValue([DateAndTimeField]) + #22:33#

^

 

[boblarson]

You should use the ampersand (&) instead of the plus sign for concatenation. Even though the plus sign works for many things, it can cause you unintened problems when dealing with numbers (and date/time is actually a number).

 

[danbl]

Thanks for the assist. I'll give this a try and let you know.

 

[EMP]

Originally posted by boblarson
You should use the ampersand (&) instead of the plus sign for concatenation. Even though the plus sign works for many things, it can cause you unintened problems when dealing with numbers (and date/time is actually a number).

Bob - Date plus Time is a numeric calculation, not a concatenation.

Since date/time data type is internally a double precision number with the integer portion representing the date and the decimal portion representing the time, we do need the + sign if we want to keep the date/time data type as the + sign is generally used for numeric calculations.

On the other hand, & is generally used for text concatenations. If you use & instead of + e.g.
DateValue([DateAndTimeField]) & " " & #22:33#

what you get is actually a text string.


There is an easy way to see their differences in query results. Being internally a number, date/time data type is right justified whereas text data type is left justified.

A test in the Immediate Window can also verify which of + and & can return date/time data type:-

X = Date() + #22:33#
? X
22/5/2007 10:33:00 PM 
? TypeName(X)
Date

Y = Date() & " " & #22:33#
? Y
22/5/2007 10:33:00 PM
? TypeName(Y)
String

^

 

[Jon K]

X = Date() + #22:33#
? X
22/5/2007 10:33:00 PM
? TypeName(X)
Date

Y = Date() & " " & #22:33#
? Y
22/5/2007 10:33:00 PM
? TypeName(Y)
String

It's just like adding Dollars and Cents. We would naturally use the + sign, though Dollars & "." & Cents would also give us something that looks like 18.25, but it's text and not currency that we can do calculations with.

Similarly, Date + Time remains true date/time values that we can do calculations with as well as formatting.
.