Extracting Date/Time

[patriot76]

How, in a query can pull out a range of time values. I have data being inported that show activity in chronology order and what to pull out certain time ranges
Date/Time format and data example:

Field name: Date/Time
Data: 01/02/2005 06:00 am

 

[mresann]

Use the Format function. In your case, assuming you are using American date standard, the code is:

Format([Date/Time],"mm/dd/yyyy hh:nn am/pm")

Incidentally, if you could create field values without special characters (such as a forward slash), it would greatly help your coding down the road.

Also, take some time to review the Format function in detail. You will be amazed at the diversity of different formats you can produce.

 

[patriot76]

Sample data
01/05/2005 0555 am
01/05/2005 0600 am
01/05/2005 0625 am
01/05/2005 0631 am
01/05/2005 0657 am

Example of data I need to query is a range of time value, ex: between 0600 am and 0645 am.

The query would return a value of:01/05/2005 0600 am
01/05/2005 0625 am
01/05/2005 0631 am

My three fields are Lname, FName, Date/Time. Employee is suppose to be logged on at a certain time and I would like a summary of the variance.
Thank You

 

[Pat Hartman]

Use the TimeValue function to extract just the time part of the date.

Where TimeValue(YourDate) Between #6:00 AM# And #6:45 PM#;

 

[mresann]

OK, gotcha. You need to extract the time from the data shown and compare it to other time parameters.

First, I"m using your FIRST data criterion for the answer, i.e.,

01/03/2007 03:32 pm

as opposed to your second post with the time format missing a colon:

01/03/2007 0332 pm

The first criterion is recognized by Access and VBA as a proper date format, while the second criterion is not. For instance,

IsDate(#01/03/2007 03:32 pm#) returns true, while

IsDate(#01/03/2007 0332 pm#) returns false.

The simple solution would be to convert the column containing the date fields from Text to Date/Time. Once you do that, then the following Where clause in the Date/Time field,

([datetime]-Int([datetime]))>=#6:00:00 AM# And ([datetime]-Int([datetime]))<=#6:45:00 AM#

returns only the data that you define in your parameters.

If you don't want to change the table field type to Date/Time, or your data does not include the colon in the hour format, then you will have to use string manipulation to extract the "0632 am" segment from the full data field, add the colon, convert the value to a time value, then compare it with the parameters as above. All in all, this would take considerable time to set up.

 

[patriot76]

Pat hartman / mresann

Hay! Works great, Than you so much.

Now if I can take this one step futher, to be able to return the earliest time stamp form each day. I would like to know by day when the employee logged on to our system.

Thanks