Splitting Date and time field

H

Hicksy

Hello

I have a general date field which stores both date and time.
Is there a way I can split the two for a query?

Many thanks
 
H

Hicksy

Could you explain how as i have tried but cant get it to work.
To clarify, I would like to split the date/time fieldso that results from a
table will show the date in one field and the time in another.##thanks
 
R

Rob Parker

Hi Hicksy,

You can use the standard Access functions DateValue and TimeValue to split a
Date/Time field.

If you do this in a query, you will get an error from these functions if the
DateTime value is null, so you need to wrap them in an Iif expression.
Something like this should do what you want:

SELECT MyTable.MyDateTimeField,
IIf(IsNull([MyDateTimeField]),Null,DateValue([MyDateTimeField])) AS
DatePart, IIf(IsNull([MyDateTimeField]),Null,TimeValue([MyDateTimeField]))
AS TimePart
FROM MyTable;

HTH,

Rob
 
Top