Set Query criteria with vba

[gg80]

I am using A2007 and A2003.



I have two reports that could use the same source query. However, the query criteria required for each report is different. I would like to change the criteria with vba and use only one query..

Say that the source Query1, as it stands has the criteria Field_X=-1 and field_Y=null. This is ok for report_1. When I open Report_2, I would like to reverse the criteria (x=null and Y=-1). Of course, opposite action when open the first report. If doable, what would the code look like?

I looked up and tried two different solutions on various help sites and neither worked. Possible I am not using them correctly. e.g., one used a term "qdf" as if it were some standard function. My Access didn't recognize it.

Any help much appreciated.

[June7]

Alternative:

Don't put parameters in query.

Instead of query object as report RecordSource, can build the SQL directly in the RecordSource property.

VBA to pass filter criteria to the report. Whether the RecordSource is query object or SQL statement, can apply this approach.

If this condition met Then
DoCmd.OpenReport "report1 name", , , "fieldX=-1 And fieldY Is Null"
Else
DoCmd.OpenReport "report2 name", , , "fieldX Is Null And FieldY=-1"
End If

Note the use of Is Null - cannot =Null, nothing is ever =Null. http://allenbrowne.com/casu-12.html

[gg80]

Thanks for quick reply. I didn't give you whole story in posting, trying to keep it simple to write up. A big problem with this system is that the user has a very slow server link. I am always redesigning for speed. I have read somewhere that keeping queries small helps speed, so hesitate to have query with no criteria..

One of the reports is a sub report. I can handle that with on-load. The other report includes code to change a column to a csv row, opening the report with a control to hold the csv row. I changed query criteria fieldX=-1 OR fieldY=-1, then modified CSV creation code to convert only where fieldY=1.

Works, but for some reason the query slowed way down when I added the OR function. Both fields are indexed. I will try to find out why

[June7]

Did you try the approach of building the SQL directly in the report RecordSourse? You can have the criteria in that statement then don't need the WHERE CONDITION argument in VBA and the query then has filter.

[gg80]

I had not tried it. I still have two queries, but I don't have to look at them and wonder why whenever I go back and modify the system. Speed is not compromised either. Like a lot of these things, I smack my head when I get the answer. Sometimes, just get too focused on a chosen path.

Thanks again.