Return all records in FORM fed parameter query

[Guest]

From the 8/28 post below I know how to return all records in a parameter
query if the parameter is left blank. But I don't know how to do this
through a parameter query that is based on form input. I am using the string
below in my query criteria to summon the parameter from the form.

[FORMS]![FRM_Option_Frequency_Criteria]![Type]

1. How do I also tell it to return all records if the "Type" parameter is
left blank?

2. How would I handle this if my form sends multiple parameters and I don't
want any blank parameters to limit my results?

Thanks, \Jim

Subject: Re: Return all records in a parameter query 8/28/2005 6:06 AM PST

By: Douglas J. Steele In: microsoft.public.access.queries


WHERE Bldg = [What Building?] Or [What Building?] Is Null

Note that it's critical that the two references to the parameter be typed
identically.

You can also extend the above so that they only have to type in part of the
Building Name:

WHERE Bldg Like "*" & [What Building?] & "*" Or [What Building?] Is Null

 

[Amy Blankenship]

Try

SELECT * FROM theTable WHERE Bldg LIKE
IIF(Nz([FORMS]![FRM_Option_Frequency_Criteria]![Type]) > 0,
[FORMS]![FRM_Option_Frequency_Criteria]![Type], "*") AND Thingie LIKE
IIF(Nz([FORMS]![FRM_Option_Frequency_Criteria]![Thingie]) ,
[FORMS]![FRM_Option_Frequency_Criteria]![Thingie], "*"...etc.

HTH;

Amy

From the 8/28 post below I know how to return all records in a parameter
query if the parameter is left blank. But I don't know how to do this
through a parameter query that is based on form input. I am using the
string
below in my query criteria to summon the parameter from the form.

[FORMS]![FRM_Option_Frequency_Criteria]![Type]

1. How do I also tell it to return all records if the "Type" parameter is
left blank?

2. How would I handle this if my form sends multiple parameters and I
don't
want any blank parameters to limit my results?

Thanks, \Jim

Subject: Re: Return all records in a parameter query 8/28/2005 6:06 AM
PST

By: Douglas J. Steele In: microsoft.public.access.queries


WHERE Bldg = [What Building?] Or [What Building?] Is Null

Note that it's critical that the two references to the parameter be typed
identically.

You can also extend the above so that they only have to type in part of
the
Building Name:

WHERE Bldg Like "*" & [What Building?] & "*" Or [What Building?] Is Null

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm going crazy. I know there's a way to add criteria in a parameter
query
that will return all records if the parameter box is left blank. I've
done
it before but I can't find it in the help system or in here (surely
because
I'm typing my question incorrectly).

I have a field called Bldg. I'd like people to type a bldg. or to leave
it
blank and return all records. I'm not using a form, just a plain old
parameter.

Thanks in advance for your help.