using data in a field in a form to become the Where clause of a qu

[Guest]

I have a field on a form that I would like to use in the "where clause" of a
query.
How can I automate this so that I do not have to open the query in design
view fill in the criteria field then run the query. I would like to just
click a command button and run the query with the "on current" criteria or
generate a report.

 

[Guest]

You can reference the control on the form as a parameter in the query, e.g.:

SELECT *
FROM Contacts
WHERE City = "Forms!YourFormName!txtCity;

Note that with some data types, dates in particular due to the different
formats used internationally, it is advisable to declare the parameter in the
query.

Alternatively you can base a report on an unrestricted query and open it
from your button, referencing the text box to build an expression for the
WhereCondition of the OpenReport method. Assuming City is a text field, and
therefore needs wrapping in quotes:

DoCmd.OpenReport "YourReport", WhereCondition:="City = """ & Me.txtCity & """"

 

[Guest]

I've got two methods on this one. The first solution uses a query designed
in Access, and the second uses SQL in VBA code.

First, we'll stick with what you seem to be using - an query built through
Access. The easiest way to jury rig this is to create a table with a single
row to hold database settings. Make the columns whatever you need to match
the query you are trying to build.

So for instance, let's say the WHERE section of your SQL statement has two
criteria - a string last name and an integer age. In our settings table we
will create three columns - an autonumber 'index' for easy tracking, a string
'last', and an integer 'age'.

Now, go back to your query and add this new table to the mix. If you want
the WHERE criteria to include people older than the number in the age field,
set your query to read:

WHERE age = DLookup("[age]","settings","[index] = 1")

You can see where this is going. Just make sure to add controls to the form
to save the data to the settings table before running the query


The second solution is to just use VBA. Probably the easier solution,
though some people argue that it is not the fastest to execute. I argue
otherwise.

Open the query in SQL view and copy the text you see. Then, take it to the
form's code and assign it to a string. Add the WHERE components you want,
and then run it! Easy as pie. ... though I should probably show you an
example.

dim strSQL as string
strSQL = "SELECT leadnum FROM clients WHERE age > " & me!age & " AND last =
" & me!last & ";"
docmd.runsql strSQL

Of course, you could always just let the user type in their own SQL to a
text box and add that: strSQL = "SELECT leadnum FROM clients WHERE " &
me!where

I don't think they'd like that though.

Hope that helps!

Nick

 

[Guest]

Just noticed that a stray quotes character crept into my reply. It should
have read:

SELECT *
FROM Contacts
WHERE City = Forms!YourFormName!txtCity;

Apologies for any confusion.

 

[Guest]

I tried the open report option and that works, however the first part
referencing the control on the form with perameter. I am not sure where or
how to do this. Do I create an unbound text box and use the sql statement
from the query as the data under properties then use an on click event (make
the txtbox act like a button) to open the query?

 

[Guest]

Thank you, I will have to study this solution for awhile.

I've got two methods on this one. The first solution uses a query designed
in Access, and the second uses SQL in VBA code.

First, we'll stick with what you seem to be using - an query built through
Access. The easiest way to jury rig this is to create a table with a single
row to hold database settings. Make the columns whatever you need to match
the query you are trying to build.

So for instance, let's say the WHERE section of your SQL statement has two
criteria - a string last name and an integer age. In our settings table we
will create three columns - an autonumber 'index' for easy tracking, a string
'last', and an integer 'age'.

Now, go back to your query and add this new table to the mix. If you want
the WHERE criteria to include people older than the number in the age field,
set your query to read:

WHERE age = DLookup("[age]","settings","[index] = 1")

You can see where this is going. Just make sure to add controls to the form
to save the data to the settings table before running the query


The second solution is to just use VBA. Probably the easier solution,
though some people argue that it is not the fastest to execute. I argue
otherwise.

Open the query in SQL view and copy the text you see. Then, take it to the
form's code and assign it to a string. Add the WHERE components you want,
and then run it! Easy as pie. ... though I should probably show you an
example.

dim strSQL as string
strSQL = "SELECT leadnum FROM clients WHERE age > " & me!age & " AND last =
" & me!last & ";"
docmd.runsql strSQL

Of course, you could always just let the user type in their own SQL to a
text box and add that: strSQL = "SELECT leadnum FROM clients WHERE " &
me!where

I don't think they'd like that though.

Hope that helps!

Nick

I have a field on a form that I would like to use in the "where clause" of a
query.
How can I automate this so that I do not have to open the query in design
view fill in the criteria field then run the query. I would like to just
click a command button and run the query with the "on current" criteria or
generate a report.

 

[Guest]

Presumably as you have the OpenReport method working you already have a
control on a form from where its getting the value by which you want to
filter the report. To use the alternative approach of a parameter in a query
you just reference that control in the query's WHERE clause. In query design
view this is done by entering the reference to the control in the criteria
row of the appropriate column, so using my example, if the query has a City
column, your form is called MyForm and the unbound text box on the form is
called txtCity you'd enter the following parameter in the criteria row of the
City column of the query in design view:

Forms!MyForm!txtCity

The first part 'Forms' is the database's Forms collection, which is a
collection of all open forms, so this tells the query to look for a form
called 'MyForm' and then for a control on that form called 'txtCity'. If you
base a report on this query you can simply open the report from a button on
MyForm without specifying any WhereCondition:

DoCmd.OpenReport "MyReport"

You could just open the query in the same way, but opening a report based on
the query gives a much more professional appearance. Rather than printing
the report you can open it in Print Preview with:

DoCmd.OpenReport "MyReport",View:=acPreview

so you might like two buttons on the form, one for printing the report, one
for previewing it.

Note that if you use ca query with a parameter the form must be open for the
query to work. If you open the query or report when the form is closed
you'll be prompted for the parameter value. If you use the OpenReport method
with a WhereCondtion you can open the report or its query independently of
the form, in which case all records will be returned.