Using IIF on Exclusive Fields

[Roger Denison]

I'm trying to develop a query on a table that rather than have one column for
the day ("Mon", "Tues", etc or "2", "3", etc), it has 7 columns of type
tinyInt, one for each day. A Monday item would have a 1 in the M column. A
Saturday item would have a 1 in the S column. All other columns would have 0.

I would like to have a list on a form that if the user selects a day, the
query will filter for just that day. But I keep getting empty rows.

For simplicity, here is the SQL for a "Monday" example: (if I can get this
then I can add multiple criteria for the other days)
SELECT dbo_Garment.BC, dbo_Garment.M, dbo_Garment.T, dbo_Garment.W,
dbo_Garment.R, dbo_Garment.F, dbo_Garment.S, dbo_Garment.U
FROM dbo_Garment
WHERE (((dbo_Garment.M)=IIf("Forms![Shipping
Report]![lstDay]"="Monday",1,0)) AND ((dbo_Garment.T)=0) AND
((dbo_Garment.W)=0) AND ((dbo_Garment.R)=0) AND ((dbo_Garment.F)=0) AND
((dbo_Garment.S)=0) AND ((dbo_Garment.U)=0));

where M, T, W, R, F, S, and U are the days of the week.

If I run the query without the IIF statement (force M's criteria to 1), then
I get the desireed results, but if I use the IIF statement then I get nothing.

 

[KARL DEWEY]

I do not follow but try changing your syntax --
SELECT dbo_Garment.BC, dbo_Garment.M, dbo_Garment.T, dbo_Garment.W,
dbo_Garment.R, dbo_Garment.F, dbo_Garment.S, dbo_Garment.U
FROM dbo_Garment
WHERE (((dbo_Garment.M)=IIf([Forms]![Shipping
Report]![lstDay]="Monday",1,0)) AND ((dbo_Garment.T)=0) AND
((dbo_Garment.W)=0) AND ((dbo_Garment.R)=0) AND ((dbo_Garment.F)=0) AND
((dbo_Garment.S)=0) AND ((dbo_Garment.U)=0));

 

[Roger Denison]

Ahhh, yes! The ol' Forms-in-square-brackets trick! Thanks.

I do not follow but try changing your syntax --
SELECT dbo_Garment.BC, dbo_Garment.M, dbo_Garment.T, dbo_Garment.W,
dbo_Garment.R, dbo_Garment.F, dbo_Garment.S, dbo_Garment.U
FROM dbo_Garment
WHERE (((dbo_Garment.M)=IIf([Forms]![Shipping
Report]![lstDay]="Monday",1,0)) AND ((dbo_Garment.T)=0) AND
((dbo_Garment.W)=0) AND ((dbo_Garment.R)=0) AND ((dbo_Garment.F)=0) AND
((dbo_Garment.S)=0) AND ((dbo_Garment.U)=0));

--
KARL DEWEY
Build a little - Test a little

I'm trying to develop a query on a table that rather than have one column for
the day ("Mon", "Tues", etc or "2", "3", etc), it has 7 columns of type
tinyInt, one for each day. A Monday item would have a 1 in the M column. A
Saturday item would have a 1 in the S column. All other columns would have 0.

I would like to have a list on a form that if the user selects a day, the
query will filter for just that day. But I keep getting empty rows.

For simplicity, here is the SQL for a "Monday" example: (if I can get this
then I can add multiple criteria for the other days)
SELECT dbo_Garment.BC, dbo_Garment.M, dbo_Garment.T, dbo_Garment.W,
dbo_Garment.R, dbo_Garment.F, dbo_Garment.S, dbo_Garment.U
FROM dbo_Garment
WHERE (((dbo_Garment.M)=IIf("Forms![Shipping
Report]![lstDay]"="Monday",1,0)) AND ((dbo_Garment.T)=0) AND
((dbo_Garment.W)=0) AND ((dbo_Garment.R)=0) AND ((dbo_Garment.F)=0) AND
((dbo_Garment.S)=0) AND ((dbo_Garment.U)=0));

where M, T, W, R, F, S, and U are the days of the week.

If I run the query without the IIF statement (force M's criteria to 1), then
I get the desireed results, but if I use the IIF statement then I get nothing.