Using IIF to set Lunch or Dinner

  • Thread starter Thread starter Lee E. Johnson
  • Start date Start date
L

Lee E. Johnson

Alright, I want to select records and set a column to either 1 or 2
depending on when the record occured. I know I'm close, but I get a date
type mismatch error with the following:

SELECT IIf(Format(OrderHeaders.OrderDateTime,"hh:nn:ss")>"00:00:00" And
Format(OrderHeaders.OrderDateTime,"hh:nnm:ss")<"04:30:00",DateAdd("d","-1",[OrderDateTime]),[OrderDateTime])
AS CorrectedDate, Sum(OrderHeaders.SubTotal) AS [Sales Total],
Sum(OrderHeaders.GuestNumber) AS SumOfGuestNumber,
IIf(OrderHeaders.OrderDateTime<"16:00:00","1","2") AS LunchDinner

FROM OrderHeaders

GROUP BY IIf(Format(OrderHeaders.OrderDateTime,"hh:nn:ss")>"00:00:00" And
Format(OrderHeaders.OrderDateTime,"hh:nnm:ss")<"04:30:00",DateAdd("d","-1",[OrderDateTime]),[OrderDateTime]),
IIf(OrderHeaders.OrderDateTime<"16:00:00","1","2"),
IIf(Format(OrderHeaders.OrderDateTime,"hh:nn:ss")>"00:00:00" And
Format(OrderHeaders.OrderDateTime,"hh:nnm:ss")<"04:30:00",DateAdd("d","-1",[OrderDateTime]),[OrderDateTime]);
 
IIf(OrderHeaders.OrderDateTime<"16:00:00","1","2") AS LunchDinner
Click to expand...

A Time value is not a String so this is what's giving you the error
message. If you're doing chronological calculations, you can just use
date/time values directly - there's no need to convert them to Strings
(as the Format function does) and compare strings. Just use the
TimeValue() function to strip off the date portion, and use # to
delimit date/time constants. It's not necessary to check for times
greater than midnight - they ALL are greater than or equal to
midnight!

Note also that the second argument to DateAdd is a number, not a
string. You don't need all those quotemarks! I'll assume that
LunchDinner is intended to be a Text field; if it's numeric, leave off
the quotes around "1" and "2" as well. Since you also have only one
table it's also not necessary to qualify the fields with the tablename
either:

SELECT DateValue(DateAdd("n", -270, [OrderDateTime]) AS CorrectedDate,
Sum(SubTotal) AS [Sales Total],
Sum(GuestNumber) AS SumOfGuestNumber,
IIf(TimeValue(OrderDateTime) > #04:30:00# AND
TimeValue(OrderDateTime)< #16:00:00#, "1", "2") AS LunchDinner

FROM OrderHeaders

GROUP BY OrderDateTime;


John W. Vinson[MVP]
 
Back
Top