QUERY HELP: Time element / Ordering Issue

[floridaboy2004]

Hello,

I have a table titled "CUSTOMERS." In that table there are 2 (short-time) time fields:

"DESSERT TIME"
"MEAL TIME"

I want to establish a query that ONLY collects those customers that ordered 'DESSERT' PRIOR to ordering their 'MEAL' by utilizing the 2 times.

FYI, both times are in the format of '03:45' (short time) time with NO A.M. or P.M. designation.

Thanks a million to those who help and have taken the time to read!

 

[Norie]

As in your other post I think you need to create an expression using DateDiff.

eg

MinsBetweenOrders:DateDiff("n", MealTime, DessertTime)

Then apply appropriate criteria.

 

[floridaboy2004]

Hello Norie,

I tried doing what you suggested and using '<4' (for 4 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!

 

[Norie]

The expression I posted counts minutes not hours.

I don't know why you are getting the result you mention - it worked for me.

You say the format is short time but what are the actual values?

 

[SydneyGeek]

Maybe you could flag records where DessertTime is less than MealTime --
DessertFirst:IIF([DessertTime]<[MealTime],1,0)
Alternatively, IIF([DessertTime]<[MealTime],DateDiff("n",[MealTime],[DessertTime],0)

Denis