Multiple Query Parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sheenalis - 13 Jul 2006 16:22 GMT

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!

Signature

whitney

Reply to this Message

John Spencer - 13 Jul 2006 17:24 GMT

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
[quoted text clipped - 18 lines]
>
> Thanks!

Reply to this Message

Sheenalis - 13 Jul 2006 18:42 GMT

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!

Signature

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.
[quoted text clipped - 30 lines]
> >
> > Thanks!

Reply to this Message

Allen Browne - 13 Jul 2006 18:51 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 18 lines]
>
> Thanks!

Reply to this Message

Sheenalis - 17 Jul 2006 19:55 GMT

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

Signature

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
[quoted text clipped - 27 lines]
> >
> > Thanks!

Reply to this Message