Issue with multiple parameter driven form- based Query

[scarlo]

Ok, here's the dilemma. 9 variables in drop downs on my form that
am using as values for a parameter query.

In my criteria for one of the fields in the query there is:
Criteria: [Forms]![Sort]![Contact]
Or: [Forms]![Sort]![Contact] Is Nul

This brings back information only if I put in a value on the form
when I don't put in a value the return is totally blank.

When I recode to:
Criteria: Where Contact Like Forms![Sort]!Contac
Or: Forms![Sort]![Contact] like "*

I get both the ones I need and all the rest (because of wildcard).
How do I get the search to return ONLY the value I put on the form
OR everything if no value is specified. Basically I want somethin
like:

Criteria: [Forms]![Sort]![Criteria
Or: Where Contact Like Forms![Sort]![Criteria] = null sub "*

When I put this all in the criteria line:
[Forms]![Sort]![Contact] OR [Forms]![Sort]![Contact] Is Nul
I get the correct response when I enter a dropdown selection on th
form (contact), but when I don't enter a contact I get nada...

Any advice

 

[Wolf]

Ok, here's the dilemma. 9 variables in drop downs on my form that I
am using as values for a parameter query.

In my criteria for one of the fields in the query there is:
Criteria: [Forms]![Sort]![Contact]
Or: [Forms]![Sort]![Contact] Is Null

This brings back information only if I put in a value on the form,
when I don't put in a value the return is totally blank.

When I recode to:
Criteria: Where Contact Like Forms![Sort]!Contact
Or: Forms![Sort]![Contact] like "*"

I get both the ones I need and all the rest (because of wildcard).
How do I get the search to return ONLY the value I put on the form,
OR everything if no value is specified. Basically I want something
like:

Criteria: [Forms]![Sort]![Criteria]
Or: Where Contact Like Forms![Sort]![Criteria] = null sub "*"

When I put this all in the criteria line:
[Forms]![Sort]![Contact] OR [Forms]![Sort]![Contact] Is Null
I get the correct response when I enter a dropdown selection on the
form (contact), but when I don't enter a contact I get nada...

Any advice?

Try this;

Criteria: Like "*" & [Forms]![Sort]![Criteria] & "*"

 

[John Spencer (MVP)]

A bit of confusion on your post
What is the name of the control? Is it Forms!Sort!Contact or is it
Forms!Sort!Criteria? You refer to it in both ways in your post. Also, do you
have the COLON in your criteria?

In an SQL Clause you would have something like the following
WHERE (Contact = [Forms]![Sort]![Contact] or [Forms]![Sort]![Contact] is Null)

In the grid:

Field: Contact
Criteria: ([Forms]![Sort]![Contact] or [Forms]![Sort]![Contact] is Null)

THis should work, but Access will rearrange this with the other criteria in your query.

IF your Contact field ALWAYS has data in it and is text, then you could use:

Field: Contact
Criteria: Like IIF([Forms]![Sort]![Contact] is Null,"*",[Forms]![Sort]![Contact])

Note the absence of the word WHERE in the criteria section of the grid.

 

[scarlo]

That way returns all. I have 4 parameters (soon to add 5, to make 9
and I want Access to sort based upon a form.

If there is a value in a slot on the form I want it to run the quer
with that value as parameter, but if there is no value I want Acces
to run that parameter as "*". So basically I need it to be somethin
like

[Forms]![Sort]![Contact
Or if Is Null, Like "*

Any suggestions

 

[scarlo]

Thanks for the help
Sorry, Contact is the actual reference control that I was using as a
example.

Ok, here's what I put in my contact and state sections of quer
table
Lik
IIf(IsNull([Forms]![Sort].[Contact]),"*",[Forms]![Sort].[Contact]
Like IIf(IsNull([Forms]![Sort].[State]),"*",[Forms]![Sort].[State]

The query produces a table when entries are chosen for both contac
and state, but if either are missing the table is empty.

Now I don't have an SQL or anything else running- I thought I coul
get away without it. Is this above all I'm supposed to need

Addendum: Looks like something like this works..
= [Forms]![Sort]![Contact] Or Trim([Forms]![Sort]![Contact] & ""
= "