 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / September 2006IIF statement function criteria
The Sex field contains "M" and "F" and "X". The IIf statement will not show any records when true. I would expect everything except "X". Does the IIF statment not handle functions as one of the outcomes? Below is the sql from the query: SELECT Table1.Sex, * FROM Table1 WHERE (((Table1.Sex)=IIf(1=1,Not ([Table1].[Sex])="X",[Sex]))); Thank you Dave >>I would expect everything except "X". Try this -- SELECT Table1.Sex, * FROM Table1 WHERE [Table1].[Sex] <>"X"; > The Sex field contains "M" and "F" and "X". The IIf statement will not show > any records when true. I would expect everything except "X". Does the IIF [quoted text clipped - 7 lines] > Thank you > Dave The criteria needs to be prefixed with the IIF condition and the <>"X" is not behaving like I'd expect it to. > >>I would expect everything except "X". > Try this -- [quoted text clipped - 13 lines] > > Thank you > > Dave Why do you think it has to have the IIf statement? It appears you want to return the entire record, but you have one field at the beginning. I'm not sure you need that. I think SELECT * FROM Table1 WHERE [Sex] <> "X"; Is all you really need. > The criteria needs to be prefixed with the IIF condition and the <>"X" is not > behaving like I'd expect it to. [quoted text clipped - 17 lines] > > > Thank you > > > Dave I was trying to simplify the problem. The record return depends on: if month(now())= 1. If its Janurary retreive all records exept "X", if not January retreive all records. > Why do you think it has to have the IIf statement? > It appears you want to return the entire record, but you have one field at [quoted text clipped - 25 lines] > > > > Thank you > > > > Dave Try this -- SELECT * FROM Table1 WHERE (((Table1.Sex) Not Like IIf(Month(Date())=1,"C"))) OR (((Table1.Sex) Like IIf(Month(Date())<>1,"*"))); > I was trying to simplify the problem. The record return depends on: if > month(now())= 1. If its Janurary retreive all records exept "X", if not [quoted text clipped - 29 lines] > > > > > Thank you > > > > > Dave IIf(You Provide All Criteria, You Get Good Answer, You Get Incomplete Answer) I think you will need to add a calculated field to your query to get the filtering like you want it: Include: Iif(Month(Now()) = 1 And [Sex] IN("M", "F"), True, IIf Month(Now()) <> 1, True, False)) Then put True in the Criteria > I was trying to simplify the problem. The record return depends on: if > month(now())= 1. If its Janurary retreive all records exept "X", if not [quoted text clipped - 29 lines] > > > > > Thank you > > > > > Dave Can you describe what you want to have happen as if you were telling your non-computer-literate grandmother? Perhaps what you want is SELECT Table1.Sex, * FROM Table1 WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])) If some statement is true then return M and F records, if the statement is false then return all records (except those with no value - Null) Where Table1.Sex <> IIF(TheTest,"X","Z") > The Sex field contains "M" and "F" and "X". The IIf statement will not > show [quoted text clipped - 9 lines] > Thank you > Dave The problem is that the statement: WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])) does not return any records. The "Not ([Table1].[Sex])="X"" is not being treated in the IIF as it does when it is used soley as the criteria. The statement below is not elegant, but it works (only for populated records, nulls are another matter): SELECT Table1.Sex, * FROM Table1 WHERE (((Table1.Sex)=IIf(1=1,[sex],IIf([sex]="X","",[Sex])))); Thank you for the help. Dave > Can you describe what you want to have happen as if you were telling your > non-computer-literate grandmother? [quoted text clipped - 22 lines] > > Thank you > > Dave Maybe everyone is missing something. You have IIf(1=1,[sex],xxxx Do you have a field named 1? Why 1=1 ? > The problem is that the statement: > WHERE Table1.Sex=IIf(1=1,Not ([Table1].[Sex])="X",[Sex])) [quoted text clipped - 38 lines] > > > Thank you > > > Dave He is giving us only a partial description of what he needs (again). The original post said nothing about what is now 1=1. When I asked why he needed an IIf (see OP), turns out he is looking for January Month(Now()) = 1 (his code, not mine) I posted back the code that would work, which he chose to ignore. Some people are just beyond help. > Maybe everyone is missing something. You have IIf(1=1,[sex],xxxx > [quoted text clipped - 42 lines] > > > > Thank you > > > > Dave |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.