Iff statement as query criteria

[Lobster1071]

If someone would be so kind, I would like to find out why this bit of code doesn't work. I'm probably just missing something simple. I've done some poking around, and can't find an answer to what I'm trying to accomplish.

I simply have a field called Position, and would like to grab records in a query based on the first 3 characters in that field. The characters are "Rdr". If a selection of a particular radio button on a form is selected, all records meeting that requirement is displayed. Otherwise all other records NOT meeting that requirement are displayed.

Here is my criteria for the Position field:
IIf([Forms]![MasterForm]![FrameSchedule]=1,Not Like "Rdr*",Like "Rdr*")

Why exactly doesn't this work? Putting in the individual statements in the criteria (without the IIf statement) works fine.

 

[plog]

You can't dynamically create criteria. You can use variables, but you can't create a string that you want evaluated as criteria-- which is what you are trying to do.

I would make a new calculated field with that Iff statement. I don't fully understand your logic so I'm probably not fully using your logic, but the general idea remains the same:

ShowRecord: Iif([Forms]![MasterForm]![FrameSchedule]=1 AND [SomeField] Like "Rdr*", 1, 0) + Iif([Forms]![MasterForm]![FrameSchedule]<>1 AND [SomeField] Not Like "Rdr*", 1, 0)

I would then run that query and make sure that all the ones I want to show do in fact have a 1 in the ShowRecord field. Once I verify that I would go back to design view and put a 1 under the ShowRecord field in the criteria section.

 

[arnelgp]

create two query's one with Like "Rdr*" and the other Not Like "Rdr*".
the idea is to change recordsource of your form/subform.
on the load event of your form/subform:

private sub form_load()
if me.frameschedule = 1 then
me.recordsource = "query name with Like here"
else
me.recordsource = "query name with Not Like here"
end if
end sub

on your FrameSchedule control's click event:

private sub frameschedule_click()
' just call the form load event to switch recordsource
form_load
end sub