QUERY Help: Time Differences for 2 fields on same table?

[floridaboy2004]

Hello all,

I have a table named 'CUSTOMERS.' This table has, among others, 2 fields called 'purchase time' and 'initial entry time.'

I want to evaluate the shopping pace of my customers and I want to select out ONLY those customers that made a 'purchase (time)' within 2 hours or less of their 'initial entry time' in my store.

FYI, the 'purchase time' and the 'initial entry time' are in Short Time (03:45) format (with no A.M. or P.M. designation, etc.).

Is there a way to specify for this during the creation of a query?

Any help/suggestions for formulas would be most helpful.

 

[Norie]

I think you need to make an expression in the query using the DateDiff function.

eg

ElapsedHours:DateDiff("h", InitialEntryTime, PurchaseTime)

or

ElapsedMins:DateDiff("n", InitialEntryTime, PurchaseTime)

You could then apply appropriate criteria against the expression.

 

[floridaboy2004]

Hello Norie,

I tried doing what you suggested and using '<2' (for 2 hours or less) as my criteria but it is giving me values such as '-3064425' when I use 2 times such as '21:29' and '22:15.'

How should I write my criteria to give me ALL values where the purchase time is less than 2 hours of the initial entry time?

Thanks for your patience and generosity!

 

[tonnic]

Hi,

the following should work

for time-diff in minutes:
Datediff("n","21:29","22:30")

for time-diff in seconds:
Datediff("s","21:29","22:30")


For example, a query could look like this:

SELECT * FROM [TABLE_NAME]
WHERE datediff("n",enter,purchase)<=120

Don't compare the times in hours, because the Differenz between 19:15 and 21:00 will be 2 hours for access ...

hope this help you