Comparison Queries in Access 1

[cbetofop]

I need to build an expression to query the following..

2 fields with TIMES in them i.e. 20:47:26 (Hour/Minute/Seconds)

Compare data in each field and identify instances where the difference between each is greater than 90 seconds (or any other difference if possible)

I can't find any way of doing this in Microsoft manuals and the Microsoft website is about as helpful as an ashtray on a motorbike!!

Any ideas?

CBETOFOP

 

[jrbarnett]

Hi,

Use the DateDiff function, it works great for time values as well.

John

 

[cbetofop]

Thanks JR,

But for someone with my level of knowledge....

What should the query look like? All I get is endless data mismatch messages and operand errors.

Any help wouldbe appreciated.

CBETOFOP

 

[BNPMike]

DateDiff requires date fields. Strings are not converted automatically. Could that be the problem?

 

[jrbarnett]

Hi,

This is the sort of code you would need to do it:

Select Field1, Field2, Over90SecsDiff: Iif(DateDiff(datediff ("s", Field1, Field2)) > 90, "Yes", "No&quot
From Table

John

 

[cbetofop]

John,

Many many thanks for taking the time to look at this, however as I say "my level of knowledge" and all...


do I type in the WHOLE thread i.e.

"Select Field1, Field2, Over90SecsDiff" and the rest,

or is it just from the "Iif(DateDiff(datediff ("s", Field1, Field2)) > 90, "Yes", "No&quot
From Table"

The syntax of these things is very difficult to get head around if your'e just starting out.

Cheers

Joe

 

[jrbarnett]

Joe

This is an SQL select statement to tell you the time difference.
The statement will return "Yes" if there is a > 90 seconds gap between Field1 and Field2, if not it will say "No".

You need to change "Field1" to the name of the first field, and "Field2" to the name of the second one.
The word "Table" needs to be changed to the name of the table that they come from.
You can then go into a new query in design mode and either copy the adapted part of the line into a top level criteria window, eg
ExpressionTitle: Iif(DateDiff(datediff ("s", Field1, Field2)) > 90, "Yes", "No&quot

this would call the column "ExpressionTitle".

John