Splitting Date and time field

[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

 

[Douglas J. Steele]

Use the DateValue and TimeValue functions.

 

[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

 

[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