Changing Query Selection criteria

[Andy]

Hello,
I have a Query that has selection criteria (Example: Day=5)
Now I want to write a Macro that opens the query in design
view and changes the criteria to a value specified by the
Macro (Example: Change Day=5 to Day=17 or 19 or 31), then
resaves the query.

Any ideas? I and do the OpenQuery command and go into
Deign view but have no idea how to get to the Criteria
area.

Thanks in Advance!

 

[Allen Browne]

Using VBA code, you can build the query statement dynamically and assign it
to the SQL property of the QueryDef.

However, there may a simpler solution.

Create a form named (say) "Form1".
Add two text boxes named "FromDay" and "ToDay".
In query design view, set the Critieria to:
Between [Forms]![Form1]![FromDay] And [Forms]![Form1]![ToDay]
The query now reads the values from the form.

To prevent the user entering non-numeric values into the text boxes, set the
Format property to General Number. To prevent the query misunderstanding the
data types of the text boxes, choose Parameters from the Query menu (in
query design), and enter two rows into the dialog like this:
[Forms]![Form1]![FromDay] Long Integer
[Forms]![Form1]![ToDay] Long Integer

 

[Andy]

Hello Allen!

Thanks you for your response. I will use VBA to change the
querydef as "day" was used only as an example.

I appreciate your help!

Andy

-----Original Message-----
Using VBA code, you can build the query statement dynamically and assign it
to the SQL property of the QueryDef.

However, there may a simpler solution.

Create a form named (say) "Form1".
Add two text boxes named "FromDay" and "ToDay".
In query design view, set the Critieria to:
Between [Forms]![Form1]![FromDay] And [Forms]![Form1]! [ToDay]
The query now reads the values from the form.

To prevent the user entering non-numeric values into the text boxes, set the
Format property to General Number. To prevent the query misunderstanding the
data types of the text boxes, choose Parameters from the Query menu (in
query design), and enter two rows into the dialog like this:
[Forms]![Form1]![FromDay] Long Integer
[Forms]![Form1]![ToDay] Long Integer

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

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

Hello,
I have a Query that has selection criteria (Example: Day=5)
Now I want to write a Macro that opens the query in design
view and changes the criteria to a value specified by the
Macro (Example: Change Day=5 to Day=17 or 19 or 31), then
resaves the query.

Any ideas? I and do the OpenQuery command and go into
Deign view but have no idea how to get to the Criteria
area.

Thanks in Advance!

.

 

[Ceci]

Andy, I wonder if you have figured out how to change query selectio
criteria using vb. I cannot use the form idea that Allen B. hav
suggested because I import the data into excel. If the query is has
"form" variable, I am not able to see the query in excel. Sorry I hop
this makes sense to you.

Thanks for your help in advance.

Ceci


-----------------------------------------

*Hello,
I have a Query that has selection criteria (Example: Day=5)
Now I want to write a Macro that opens the query in design
view and changes the criteria to a value specified by the
Macro (Example: Change Day=5 to Day=17 or 19 or 31), then
resaves the query.

Any ideas? I and do the OpenQuery command and go into
Deign view but have no idea how to get to the Criteria
area.

Thanks in Advance!

-
Cec