Open Report with Filter/WHERE clause

[Muttley]

I'm trying to use WHERE condition of the DoCmd.OpenReport method without much luck. The users can enter 'from' and 'to' dates onto a form, which I am trying to use with the WHERE condition. The syntax I am using is as follows:

DoCmd.OpenReport &quot;repName&quot;, acViewPreview, , &quot;WriteOffDate >&quot; & Me.txtFrom & &quot;And WriteOffDate < &quot; & Me.txtTo

I have tried variations on the above, but can either get the report to return a) no records at all or b) all of the records in the underlying table

Please can anyone help!

 

[JoeMiller]

Rather than basing your report directly on the table, base it on a query that selects the data. The controls that hold the &quot;From&quot; and &quot;To&quot; WriteOffDates can be used to tell the query the proper records to select. To reference those controls in the query, put the following in the criteria for the WriteOffDate field:

Between [Forms]![MyFormName]![WriteOffDateFrom] and [Forms]![MyFormName]![WriteOffDateTo]

When the form is loaded and the fields are filled in the query will only return the records between the dates entered. Now when you run repName (which should be based on this query) it will only show the applicable records. Then you can create a command button using the wizard that will call the report and you won't have to modify the code that the wizard produces.

Hope that helps!

Joe Miller
joe.miller@flotech.net

 

[Muttley]

Joe

Thanks for the info - this would be the normal method that I would use, however was trying a different approach as I want my report to be based either on the date parameters entered into the txt boxes OR to select the entire dataset from the table if the parameters were left blank by the users. In this way I would not have to create 2 queries (one that references the date params and one that doesn't) as I am lead to believe that Access partially compiles all of its' stored queries.

I was also getting frustrated that I couldn't use the above method as I couldn't work out a reason why it was not working.

I suppose that the other approach I could use is to create a querydef and modify that based upon whether or not the users had completed the data text boxes.

What do you think?

Cheers...Muttley

 

[JoeMiller]

The querydef is definitely the way you should go for what you are proposing.

Joe Joe Miller
joe.miller@flotech.net

 

[Muttley]

Joe

FYI, I got the original approach to work by changing the dates to US format - apparently it will only accept them like this i.e.:

&quot;[WriteOffDate] >=&quot; & &quot;#&quot; & Format(datFrom, &quot;mm/dd/yy&quot & &quot;# AND [WriteOffDate] <=&quot; & &quot;#&quot; & Format(datTo, &quot;mm/dd/yy&quot & &quot;#&quot;

where datFrom and datTo are the me.txtFrom/me.txtTo variables.

Thanks for your help...Muttley

 

[Quehay]

Another option is to have a parameter such as:

[Enter start date (or leave blank for all)]

and then in the next line of the QBE grid place:

[Enter start date (or leave blank for all)] Is Null

This allows using or ignoring the parameter input if it's NULL (no input).

I haven't tried this with the Between Datestart & Datestop
yet. I don't think you'd even have to enter the &quot;Is Null&quot; version of both dates since the next line of QBE introduces a big OR, that is, if any condition placed in the second line can be satisfied the first line is ignored, i.e.,


WHERE Date (between [Enter Start] and [Enter Stop]) OR (IsNull([Enter Start]))