Filtering by Time Segment

[Stanski21]

Hi folks,

I am writing a query that will highlight aggregations within time periods.
So I have a full date_time field on each row (TRANSACTION_DATE_TIME) and a date only field as well (Last Contact Date). I want to be able to use the time element (regardless of the date) and perform calculations using time segments.

E.g. I'd like to perform an aggregation on all where time is between 00:00 and 07:00.

<b>TimeValue(Format([DATA]![TRANSACTION_DATE_TIME]-([DATA]![Last Contact Date]),"Short Time"))</b> gets me the time element fine, but I cannot seem to set the criteria to make sure the query is working without getting a Data Mismatch error.

I've tried as a string, as a date, as an integer and it doesn't like it. I pasted the results into Excel and the date was returned as 00-Jan-1900... so I was thinking that Access knew that the returned result wasn't a date.
So I tried adding a day onto the transaction date, or subtracting a day from the Last Contact Date... still not returning a datetime result I can set criteria on.

I have also tried converting it to a string and running a <b>Left(Time, 2) between</b> criteria.

In my experience I've usually over-complicated things... so what am I missing? Any ideas?

 

[Minty]

Dates and DateTimes are stored as numbers in Access. The date is the integer value and the time is the decimal fraction. Dates on 00-Jan-1900 are stored as 0.0 as far as access is concerned.

So if you subtract the date from the date time you will be left with a datetime value set to 00-Jan-1900 02:35 if 2 hours 35 minutes was the time portion of the recorded value.

 

[Galaxiom]

Dates on 00-Jan-1900 are stored as 0.0 as far as access is concerned.

Incorrect. Zero date in Access is 30 Dec 1899.

 

[Minty]

Incorrect. Zero date in Access is 30 Dec 1899.

Bummer - that's doing to much in SQL server. I'm correct for a linked SQL server table. /clutching at straws/

 

[Galaxiom]

Also note that arithmetic operations on DateTime do not work for dates before the zero day. This is because the time part remains positive even when the date part is negative.

 

[Galaxiom]

Bummer - that's doing to much in SQL server. I'm correct for a linked SQL server table. /clutching at straws/

No actually. SQL server times linked to Access behave very strangely under certain circumstances.

 

[Stanski21]

Thanks guys. Figured it out.
I'm now using the date element of the Date/Time field to subtract from itself (rather than the separate date field - don't even know why that's there anyway I'll have to ask the supplier) and it's coming out with a nice, normal, easy to use integer!

[testDATA]![TRANSACTION_DATE_TIME]-DateValue([testDATA]![TRANSACTION_DATE_TIME])