Dynamic Queries - Passing Data to a Query

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hi All

I have another question and thanks again in advance for your great advice
and help.

I am good with passing data to a query - what I really want to know is how
you can pass a value to a query that will make is as if you hadn't entered
any criteria at - all for example:

Text Box Name: BillTo
FormName: Campaigns
I can pass the value of BillTo to a query with setting the criteria of the
BillTo field in a query to
Forms!Campaigns.BillTo.Value

and everything works fine but if I leave the BillTo Text Box blank my query
returns no rows instead of ALL the rows that I would assume. I am trying
to write the query so that if there is nothing in the text box(or any other
way I want to pass similar information to a query) that it will act as if I
hadn't entered any criteria and return ALL rows.

Can this be done or do I have to write/desing separate queries based on the
user's selctions?

I usually use the Query Design view but straight SQL advice will work too.

If you need any more information please let me know.

Thanks a lot everyone,

Jeff
 
Select * from table where
(field = forms.campaigns.billto.value)
or
(forms.campaigns.billto.value = "")
or
(forms.campaigns.billto.value is null)


Open the query in design view. Add a new field

v:forms.campaigns.billto.value

set the criteria for this field to ""
below that, set the criteria to Is Null

all criteria on one line have to match.
each criteria line is evaluated separately.

(david)
 
Thanks David

That code definately works if the Textbox on the form is in fact blank but
it doesnt seem to be working if there is data in the text box to pass off
to:

I tested this in another database with the following SQL statement

SELECT DrProductivity.DrProdID
FROM DrProductivity
WHERE (((DrProductivity.DrProdID)=[Forms]![ZZTemp].[Text1].[Value]) AND
(([Forms].[ZZTemp].[Text1].[Value])="")) OR
((([Forms].[ZZTemp].[Text1].[Value]) Is Null));

Table: DrProductivity
Field: DrPRodID
Form: ZZTEmp
Field: TextBox1

I am trying to get it so that if I enter a number in the text box the query
returns just the row of the number in the text box and if the text box is
empty it will return All of the rows in the table- is my SQL missing
something?

And if I am trying to do this with multiple different fields within a query
(getting data from a few different textbox on a form and return everything
for that query field if each text box is empty) or should I just bight the
bullet and write MANY different SQL statements for each of the possible
variable selections the user could make to return the query based on the
selection?

Thanks again,

Jeff
 
SELECT DrProductivity.DrProdID
FROM DrProductivity
WHERE
Click to expand...

(
((DrProductivity.DrProdID)=[Forms]![ZZTemp].[Text1].[Value])
AND
(([Forms].[ZZTemp].[Text1].[Value])="")
)

OR
((
([Forms].[ZZTemp].[Text1].[Value]) Is Null
));


What you want is

(
((DrProductivity.DrProdID)=[Forms]![ZZTemp].[Text1].[Value])
OR
(([Forms].[ZZTemp].[Text1].[Value])="")
OR
(([Forms].[ZZTemp].[Text1].[Value]) Is Null)
);

In design view,

((DrProductivity.DrProdID)=[Forms]![ZZTemp].[Text1].[Value])

needs to be on a different line from

(([Forms].[ZZTemp].[Text1].[Value])="")

(david)


Jeff said:
Thanks David

That code definately works if the Textbox on the form is in fact blank but
it doesnt seem to be working if there is data in the text box to pass off
to:

I tested this in another database with the following SQL statement

SELECT DrProductivity.DrProdID
FROM DrProductivity
WHERE (((DrProductivity.DrProdID)=[Forms]![ZZTemp].[Text1].[Value]) AND
(([Forms].[ZZTemp].[Text1].[Value])="")) OR
((([Forms].[ZZTemp].[Text1].[Value]) Is Null));

Table: DrProductivity
Field: DrPRodID
Form: ZZTEmp
Field: TextBox1

I am trying to get it so that if I enter a number in the text box the
query
returns just the row of the number in the text box and if the text box is
empty it will return All of the rows in the table- is my SQL missing
something?

And if I am trying to do this with multiple different fields within a
query
(getting data from a few different textbox on a form and return everything
for that query field if each text box is empty) or should I just bight the
bullet and write MANY different SQL statements for each of the possible
variable selections the user could make to return the query based on the
selection?

Thanks again,

Jeff

david epsom dot com dot au said:
Select * from table where
(field = forms.campaigns.billto.value)
or
(forms.campaigns.billto.value = "")
or
(forms.campaigns.billto.value is null)


Open the query in design view. Add a new field

v:forms.campaigns.billto.value

set the criteria for this field to ""
below that, set the criteria to Is Null

all criteria on one line have to match.
each criteria line is evaluated separately.

(david)
Click to expand...
Click to expand...
 
use this as the criteria

Like Forms!Campaigns.BillTo.Value & "*"

so if the text box is Emply/Null, query will show everything.

(e-mail address removed)
 
from Hong Kong Rainbow01

first i want to know what is data type of your table field for your text box
---> BillTo

if date type , please tell me the date range in your table, i.e. the early
date and the lastest date
if number type, also tell moe the number range , i.e. the same in above
if string (text type), no need


"Jeff" 來函：
 
"Like" runs much slower than "="

If you only have a small database, "Like" is easier to write.

It is also good to learn how to use "AND" and "OR" with
multiple criteria for when "Like" is too slow.

(david)
 
Back
Top