modify criteria on the fly

  • Thread starter Thread starter John Milbury-Steen
  • Start date Start date
J

John Milbury-Steen

Hi Access gurus,

I want to modify the criteria in a query based on a value in a form. The
user in the form may choose to see all records or one record. (He sets
[Choice] to "See One Record" or "See All Records"). If the user wants to
see one record, he chooses it from a menu and sets [RecordChosen]. So Why
doesn't this work for the criteria in the query:

IIf([Forms]![ChoiceForm].[Choice] = "SeeOneRecord",
([Forms]![ChoiceForm].[RecordChosen], *)

It's the last part of the IIf that doesn't work. [RecordChosen] refers to a
number. What can I use besides * to refer to all records, as if there were
no criteria?
 
Try this:

IIf([Forms]![ChoiceForm].[Choice] = "SeeOneRecord",
([Forms]![ChoiceForm].[RecordChosen], "*")
 
IIf([Forms]![ChoiceForm].[Choice] = "SeeOneRecord",
[Forms]![ChoiceForm].[RecordChosen], "*")

This gives me a "type mismatch." To simplify the thing, if we just use
numbers, why can't I say

IIf(2=3, 7, *)
or, alternatively,
IIf(2=3, 7, <>0)
(Neither of these works.)

That is, if the condition is true, limit the search to record 7, but if it's
false (as it is here), get all the records. I am just searching for a
wildcard to use for the third term.
 
OK -

If the user sets Choice as SeeOneRecord, will the RecordChosen control
always be "blank" (Null)? If yes, then use this criteria expression:

[Forms]![ChoiceForm]![RecordChosen] Or [Forms]![ChoiceForm]![RecordChosen]
Is Null
 
Back
Top