Solved Multiple condition in access query builder

[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