Parameter Query

[Scott]

I'm making a query-form interface where the query fields
base their information from what the user puts into the
form - basic stuff. Anyway, I have all the VBA in place
to make it work, however, I'm stuck on what kind of SQL to
use. I wish io be able for the user to not select a value
in a field and the query return any value for that field.
I was suggested to use an "All" row in my combo boxes, and
that seems logical, but I don't know how to write the SQL
in to allow this. Any suggestions very welcome.

TIA,
-Scott M.

 

[Scott]

My idea is to use an if then to coordinate this.

iif(IsNull(Forms!myform!myfield]),?,[Forms!myform!myfield])

I think that this would work...however...in the Then
portion, how do I say, "Well Mr. Query, if that other
field is null, then I don't want you to have any parameter
at all here." (Sorry, that's the best I could come up
with.)

LOL, TIA
-Scott

 

[Gary Walter]

My idea is to use an if then to coordinate this.

iif(IsNull(Forms!myform!myfield]),?,[Forms!myform!myfield])

I think that this would work...however...in the Then
portion, how do I say, "Well Mr. Query, if that other
field is null, then I don't want you to have any parameter
at all here." (Sorry, that's the best I could come up
with.)

Hi Scott,

A typical method is to OR your parameter condition
with the condition that will happen if "you don't
want to have any parameter here at all."

For example, if you want to "ignore the parameter"
when the form field is null:

WHERE
([somefield]=Forms!myform!myfield
OR
Forms!myform!myfield IS NULL)

or, if you want to "ignore the parameter" when your
form field= "ALL":

WHERE
([somefield]=Forms!myform!myfield
OR
Forms!myform!myfield ="ALL")

Please respond back if I have misunderstood.

Good luck,

Gary Walter

 

[Scott M.]

Finally, I have soluted my problem, thank you so very very
much.

-Scott M.

 

[Gary Walter]

Finally, I have soluted my problem, thank you so very very
much.

Hi Scott,

I "solute" you on your success.

Gary Walter