Solved Multiple condition in access query builder (1 Viewer)

[Tor_Fey]

Afternoon all;

Is it possible to use the query builder to set the criteria of a field in an access query?

An example of what i want to do is as follows:

Field Name:

 Selected: (IIf([Rtrd_EdrDate]=[Tret_DateFrom],"First Return") Or IIf([Rtrd_Status]="21","Possible Final Return") Or IIf([Box5]<-900000,"Above Upper Limit"))

I have used the above code but it doesn't work for multiple field criteria, any help would greatly be appreciated.

Kind Regards
Tor Fey

 

[theDBguy]

Yes, you can use the query builder to add your multiple criteria. However, it has a limited number of rows to construct OR conjunctions. As a workaround, you could try entering multiple criteria in the same cell.

 

[arnelgp]

you can also use Switch function:

Selected: Switch([Rtrd_EdrDate]=[Tret_DateFrom],"First Return", [Rtrd_Status]="21","Possible Final Return", [Box5]<-900000,"Above Upper Limit", True, Null)

note that Box5 there should be a field? or prefix it with the form name if it is control on the form, ei:

[Forms]![formName]![Box5] < -900000, ...

 

[SHANEMAC51]

Is it possible to use the query builder to set the criteria of a field in an access query?

this is unlikely to work in

Selected:
 (IIf([Rtrd_EdrDate]=[Tret_DateFrom],"First Return")
 Or IIf([Rtrd_Status]="21","Possible Final Return")
 Or IIf([Box5]<-900000,"Above Upper Limit"))

try another way (1-14 attachment levels)

Selected:
 IIf([Rtrd_EdrDate]=[Tret_DateFrom],"First Return",
 IIf([Rtrd_Status]="21","Possible Final Return",
 IIf([Box5]<-900000,"Above Upper Limit","")))

and maybe you need to use a semicolon instead of commas

 

[plog]

You shouldn't try and cram that much logic into one line of a query. Build a custom function in a Module.

The query becomes this:

Selected: getSelected([Rtrd_EdrDate], [Tret_DateFrom], [Rtrd_Status],[Box5])

And then your function looks like this:

Public Function getSelected(EdrDate As Date, DateFrom As Date, Rtrd_Status As String, Box5 As Long) AS String

Dim ret as String
ret="Error"
' return value, will be set below

' put all your logic in however many lines it takes here

getSelected=ret

End Function

 

[Pat Hartman]

You have three different, independent conditions all of which might be true at the same time. The IIf() conditions you coded will return the LAST true value from the OR's but the Switch() suggestion will return the FIRST. Which do you want? What do you want if all are false? If you can't define what you want, no one can help you to code it.