If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using IIF on Exclusive Fields
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. |
#2
|
|||
|
|||
Using IIF on Exclusive Fields
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 "Roger Denison" wrote: 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. |
#3
|
|||
|
|||
Using IIF on Exclusive Fields
Ahhh, yes! The ol' Forms-in-square-brackets trick! Thanks.
"KARL DEWEY" wrote: 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 "Roger Denison" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|