Query Criteria IIF Statement Multiple True parts (1 Viewer)

[Exodus]

Forgive me if this has been covered but have been searching and haven't come up with what I'm looking for.

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

 

[Brianwarnock]

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1 Or Forms!FrmAttendanceLogsRpt!BlkFilter = 86,[TempVars]![EID],anyfalseaction)

 

[Exodus]

I don't need to evaluate two conditions I need to the the 86 and [TempVars]![EID] as the true return. Lets simplify this, I might be making it confusing.

If [Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1 Then I need 133 or 86
If [Forms]![FrmAttendanceLogsRpt]![BlkFilter]= 0 Then I need 133

 

[plog]

Then I need 133 or 86

You have to be definitive in your return values. The system needs logic so it knows which one to return. AI isn't here yet (and won't be coming to Access when/if it does), you have to set up rules so that it explicitly knows which one to return.

If you want it to be random, you can use the Rnd function (https://msdn.microsoft.com/en-us/library/f7s023d2(v=vs.90).aspx), but you still have to explicitly tell the system when and how to use it.

 

[Exodus]

So why does it work if I just put 133 Or 86 as the criteria?

 

[plog]

You are talking about 2 different things: return value and criteria. A computer can evaulate as many criteria as you want to give it, but it can only return 1 thing and you need to help it determine exactly what that 1 thing is.

You're brother comes home from college infrequently and you are in charge of picking him up when he does. The bus station and train station share a building. The airport is 30 miles away.

1. If your brother is taking the bus or taking the train go downtown, else go to the airport.

No problem.


2. If your brother is coming to town go to the airport or downtown.

Wait? Those are 30 miles apart, where do I go? More instructions are needed.


#1 is why the conditional criteria one works. #2 is why the conditional return makes sense to you, but isn't specific enough for a computer.

 

[Exodus]

Thank you for your help. I think I understand now why it doesn't work with the IIf statement.

I am expecting the the IIf statement to return 133 or 86 as the criteria and in actuality it is to give the 133 or the 86 but doesn't know which one.

I guess I can always go down the route of changing the query def on the fly.

 

[jdraw]

I am expecting the the IIf statement to return 133 or 86 as the criteria

The issue may start right here,

IIF (condition, truepart, falsepart) ====read condition as criteria in your thinking.

eg
------------------------condition------------------------, truepart, false part)
IIF( [Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,

 

[Exodus]

Nope I'm expecting if the condition is met then criteria is
133 or 88

If condition is not met I expect criteria to be
133

 

[plog]

condition/criteria are synonyms.


If criteria/condtion then result

 

[Exodus]

Yes this is correct with in the IIf statement.
But in this case the IIf statement is in the criteria of the query, or the where statement of the query.

 

[plog]

Then you need to move the logic to a calculated field:

Include: Iif({ConditionA}=True OR {ConditionB}=True, 1,0)

Then underneath put 1 in the criteria.

 

[Exodus]

I only need to test one condition. Is the field on the form set to yes. If it is then the query criteria needs to be
133 or 86
to return records with 133 or 86 in the ID Field

If the field on the form is anything other than yes then the query criteria needs to be
133
to return records with 133 in the ID Field

 

[Brianwarnock]

Try
Where (yourfield=133) or (yourfield=86 And [Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1)

Yourfield is the field you are applying the criteria to

Brian

 

[namliam]

The best solution for these kind of "complex" where clauses is to not try and fix it in one query does it all, make customized SQL from your form.

I.e. in your form

If something = 1 then
    currentdb.querydefs("AnyQuery").sql = "Select.... where ... = 133" 
else
    currentdb.querydefs("AnyQuery").sql = "Select.... where ... = 86" 
endif

Then use the AnyQuery as source to whatever you need it to be source for.

 

[Brianwarnock]

Complex?

This particular query was not complex.

He always wants to select 113 and if another value is -1 then he also wants to select 86.

Brian

 

[namliam]

This particular one isnt (yet), but once users start going down this route usually more and more options come into play... and eventually they want or's and what nots and if then maybeees, etc...

 

[Brianwarnock]

Point taken

Brian

 

[jdraw]

Guys,

I'm still reading this thinking that Exodus has missed the point of the IIF statement and its syntax.

I liken it to

Pick a number between 1 and 10 ---fully expecting that a number means 1 number
To me the OP is happy with , and really wants any 2 numbers--- say 3 or 7.

The iif just doesn't mean that -- to me anyway.

 

[Exodus]

Complex?

This particular query was not complex.

He always wants to select 113 and if another value is -1 then he also wants to select 86.

Brian

Brian got it exactly. I was trying to use the IIF Statement to accomplish this.
It's not the syntax I wasn't understanding it was the out put I wasn't getting. But it does make sense now. I got it working by applying another IIF Statement to another field in the query.

But eventually this module is going to grow just as namliam predicts and I just need to code the it. Won't be too hard I'm already changing query defs in several other places. I just don't like writing SQL in VBA. I prefer to store the base of it in a table and just change the where statement as needed.