Multiple Query Parameters

[Guest]

I have a query that pulls project numbers, task numbers, and cost types. I
want the parameters to ask for two project numbers, and task number and a
cost type but I want the fields to be optional. I know this should be simple
but for some reason I can't get it to work.

I've tried a couple things but right now I have( I took the null code out
because it wasn't working):

WHERE (((VISIB_G_LDETAIL.PROJECTNO) Like [Enter 1st Project Number] & "*")
OR (((VISIB_G_LDETAIL.PROJECTNO) Like [Enter 2nd Project Number (or leave
blank and press enter)] & "*")) OR (((VISIB_G_LDETAIL.COST_TYPE) Like [Enter
Cost Type] & "*")) OR (((VISIB_G_LDETAIL.TASKNO) Like [Enter Task Number] &
"*"))

Can anyone let me know how to correctly add the null coding and if something
else is missing/wrong.

Thanks!

 

[John Spencer]

Are the fields ProjectNo, Cost_Type, and TaskNo ever NULL? If the fields
always have a value, then the following might work for you.

WHERE PROJECTNO Like [Enter 1st Project Number] & "*"
OR PROJECTNO Like NZ([Enter 2nd Project Number (or leave (blank and press
enter)],"---") & "*"
OR COST_TYPE Like NZ([Enter Cost Type],"---") & "*"
OR TASKNO Like NZ([Enter Task Number],"---") & "*"

If the fields are not required to have a value, post back for alternative
solutions.

 

[Guest]

Yes, the fields are null sometimes. I do not want the query return the nulls.
If a project number is typed in, I want those projects to returned. Otherwise
I want all data which does not include a null project # etc.

Thanks!
--
whitney

Are the fields ProjectNo, Cost_Type, and TaskNo ever NULL? If the fields
always have a value, then the following might work for you.

WHERE PROJECTNO Like [Enter 1st Project Number] & "*"
OR PROJECTNO Like NZ([Enter 2nd Project Number (or leave (blank and press
enter)],"---") & "*"
OR COST_TYPE Like NZ([Enter Cost Type],"---") & "*"
OR TASKNO Like NZ([Enter Task Number],"---") & "*"

If the fields are not required to have a value, post back for alternative
solutions.

I have a query that pulls project numbers, task numbers, and cost types. I
want the parameters to ask for two project numbers, and task number and a
cost type but I want the fields to be optional. I know this should be
simple
but for some reason I can't get it to work.

I've tried a couple things but right now I have( I took the null code out
because it wasn't working):

WHERE (((VISIB_G_LDETAIL.PROJECTNO) Like [Enter 1st Project Number] & "*")
OR (((VISIB_G_LDETAIL.PROJECTNO) Like [Enter 2nd Project Number (or leave
blank and press enter)] & "*")) OR (((VISIB_G_LDETAIL.COST_TYPE) Like
[Enter
Cost Type] & "*")) OR (((VISIB_G_LDETAIL.TASKNO) Like [Enter Task Number]
&
"*"))

Can anyone let me know how to correctly add the null coding and if
something
else is missing/wrong.

Thanks!

 

[Allen Browne]

The most efficient solution is to just build the WHERE string (or Filter for
a form, or WhereCondition for OpenReport) from the controls that actually
have a value.

For an explanation and downloadable example, see:
Search criteria
at:
http://allenbrowne.com/ser-62.html

 

[Guest]

Thanks for your help...I'll try this out.
--
whitney

The most efficient solution is to just build the WHERE string (or Filter for
a form, or WhereCondition for OpenReport) from the controls that actually
have a value.

For an explanation and downloadable example, see:
Search criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that pulls project numbers, task numbers, and cost types. I
want the parameters to ask for two project numbers, and task number and a
cost type but I want the fields to be optional. I know this should be
simple
but for some reason I can't get it to work.

I've tried a couple things but right now I have( I took the null code out
because it wasn't working):

WHERE (((VISIB_G_LDETAIL.PROJECTNO) Like [Enter 1st Project Number] & "*")
OR (((VISIB_G_LDETAIL.PROJECTNO) Like [Enter 2nd Project Number (or leave
blank and press enter)] & "*")) OR (((VISIB_G_LDETAIL.COST_TYPE) Like
[Enter
Cost Type] & "*")) OR (((VISIB_G_LDETAIL.TASKNO) Like [Enter Task Number]
&
"*"))

Can anyone let me know how to correctly add the null coding and if
something
else is missing/wrong.

Thanks!