DateTime Anomolies

[grenee]

Good Day All.

I am applying DateTime function to 2 different fields, both with Date/time data types. However when datetime wraps the values in field 1 every thing works sort of fine, but as soon as values in field 2 is wrapped with the datetime function there is an error message. Below is my Sql code for reference:

SELECT TimeValue([Schedule Arrival]) AS Expr1, TimeValue([Time Entered by Officer]) AS exp2
FROM tblSignIn INNER JOIN tblSigninDetails ON tblSignIn.tblSigninID = tblSigninDetails.TblsignInID
WHERE (((TimeValue([Schedule Arrival]))=(#11:0:0#)) AND ((TimeValue([Time Entered by Officer]))=(#11:0:0#)));

My error says: Data type mismatched in criteria selection.

I say sort of fine for field 1 because it has been changing the time entered to a date. Here is the code when field 1 alone is used:

 SELECT TimeValue([Schedule Arrival]) AS Expr1
FROM tblSignIn
WHERE (((TimeValue([Schedule Arrival]))=(#12/30/1899 11:0:0#)));

 

[Minty]

Date and Time fields in Access are actually always a Date & Time. They are stored as a decimal number where the Integer part stores the date and the fractional part of the decimal stores the time portion.

If you don't enter a date it will put in a default of which evaluates to 12/30/1899.

So you need to include a date portion in your evaluations.

 

[CJ_London]

try

WHERE (((TimeValue([Schedule Arrival]))=(#11:00:00#)));

 

[Minty]

As an real example of this if you paste the following into the SQL view of a new query and then select Datasheet view you will see a couple of interesting things;

SELECT DISTINCT Now() AS CurrentDateTime, CLng(Now()) AS Day_Intger_Value, CDbl(TimeValue(Now())) AS Time_Dec_Value, CDbl(Now()) AS Date_Time_Value;

Results

CurrentDateTime	 20/07/2016 12:43:54       
Day_Intger_Value	 42572
Time_Dec_Value	 0.530486111111111	
Date_Time_Value	 42571.5304861111

Note that trying to compare the two time portion values would fail as they are not mathematically the same.

 

[grenee]

Thanks Minty for your kind detail explanation. I has led to an awareness of time date system. Consequently I have solve my problem and sure would be able to solve many more on my own.