MS Access remove date but keep time

[prwbblue]

Hello,

I have a date/time field that either contains the date and time "1/1/1900 5:30:00 PM" or just the time "6:30:00 PM". I would like to remove the date if it's there and only keep the time when I run my query. Can someone help?

Thanks,
Adam

 

[Joe4]

Welcome to the Board!

You may be able to just use the TIMEVALUE function.

It is important to understand how Access and Excel track date and time. Basically, they store them as numbers, specifically the number of days since 1/0/1900.
So, the days are just integers, and the time is a fraction (the fractional amount of one day). So, you can also get just the time by subtracting the Integer component of the date, i.e.
[MyDate]-Int([MyDate])
and just format the result as time.

 

[prwbblue]

Thanks Joe for the quick response! Can you give the full formula example including the format results as time? I'm new at this but I'm learning.
Thanks!

 

[Joe4]

Have you tried just adding a calculated field to your query that uses the TIMEVALUE function to see if it returns what you want, i.e.

CalcField: DateValue([YourDateTimeField])

I am assuming that you know how to create calculated fields in a query. If you do not, just do a Google search on "creating calculated fields in an Access query", and you will finds lots of example, videos, and tutorials.