Date criteria on multiple queries

[PolarStar]

Hi All,

I have 8 queries all with a date field. I have one report that shows the fields from all of these queries. I'm opening the report from a print dialog box that has a date range - so I'm opening the report with a docmd.openreport line. It's easy enough to do with just one query, sending the date range - but how do I do it with multiple queries? I mean, how do I send the 1 date range to all 8 queries in one docmd?


Regards,

Mark

 

[MichaelRed]

in general, you should refine all of your queries to use a "parameter" for the date field filter (where clause). Set all of them to the same "Global Common" variable amd set the results of the dialog box to the variable common.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[PolarStar]

Michael,

Thanks for the quick reply. I think I follow you. Here's what I've done so far.

In my frmPrintDialog I created a variable called ReportFilter As String in the declarations section. In the module I have a routine that sets ReportFilter to basically "Between #1/1/93# and #2/15/93#" (These are actually the dates I been using to search on - I know data exists.)

In my queries I changed the date fields to Where and in the criteria i have forms!frmPrintDialog!ReportFilter.

I've tinkered with the docmd.openreport both passing and not passing the ReportFilter variable. I can get the report to run but there's no data.

I must've missed a vital step.

Mark

 

[PolarStar]

Okay, I think I got it.

I created some phony unbound text boxes on my print dialog form and created the date variables in that and then I was able to reference these in all the queries. I never had to reference them as global.

I guess the problem I had, when you get down to it - is it's possible to reference form variables in a query no problem. But pulling out a Public or Global variable is a right royal pain.

Mark

 

[MichaelRed]

Mark,

I think you mis-understand a little. If it's working, you are "O.K." for the time being. However the real problem with your first response is that you put the entire string in the single variable. The SQL engine sees this as attempting to match the entire string [red]"Between #1/1/93# and #2/15/93#"[/red], not the field being between the two dates included in the string. Using public or form variables is not the issue. Either works equally well. BUT to use the "Between" construct, you need two variables to hold the seperate dates, Not a single string variables which happens to include two date items. Basically, the clause needs to be part of the query, which references the variables.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[PolarStar]

I considered that actually and tried it both ways. I created public variable for the start and end dates and tinkered with that method but I still couldn't come up with a working solution.

After I've finished with this project and I'll go back and fine tune it and see if I can get it working 'properly'. Thanks for your help. You kept me from losing my mind.

Mark