Report in VBA - FilterName or WhereCondition

[Guest]

Hi,
I built a report based on a query. Now, I need to add two parameters to the
query that the user will provide when he uses the application.
In VBA, I can build the complete Where clause and use it in the
DoCmd.OpenReport command.
I was wandering if it would be better to use a FilterName parameter rather
than a WhereCondition in the DoCmd.OpenReport command.
The WhereCondition I build is the following:
strWhere = "tblFollowUp.DateSpecialComitee>=#" & datStart & _
"# And tblFollowUp.DateSpecialComitee<#" & datEnd & "#"
Then, I thought of using this code:
DoCmd.OpenReport "rptStatReport", _
acViewPreview, , strWhere, acWindowNormal
My question is: is it better (or more effective) to use the FilterName or
the WhereCondition (2 parameters of the DoCmd.OpenReport command) in my case?
For some reason, it does not work and Access is closed when I use this code.
I use Access 2000 in Windows XP Pro.
Thanks.

 

[fredg]

Hi,
I built a report based on a query. Now, I need to add two parameters to the
query that the user will provide when he uses the application.
In VBA, I can build the complete Where clause and use it in the
DoCmd.OpenReport command.
I was wandering if it would be better to use a FilterName parameter rather
than a WhereCondition in the DoCmd.OpenReport command.
The WhereCondition I build is the following:
strWhere = "tblFollowUp.DateSpecialComitee>=#" & datStart & _
"# And tblFollowUp.DateSpecialComitee<#" & datEnd & "#"
Then, I thought of using this code:
DoCmd.OpenReport "rptStatReport", _
acViewPreview, , strWhere, acWindowNormal
My question is: is it better (or more effective) to use the FilterName or
the WhereCondition (2 parameters of the DoCmd.OpenReport command) in my case?
For some reason, it does not work and Access is closed when I use this code.
I use Access 2000 in Windows XP Pro.
Thanks.

The Where clause should be just fine.
Remove the criteria from the query.

Use 2 unbound controls on your form.
Name one datStart and the other datEnd


strWhere = "[DateSpecialComitee] >= #" & Me![datStart] & "# And
[DateSpecialComitee] < #" & Me![datEnd] & "#"

DoCmd.OpenReport "rptStatReport", acViewPreview, , strWhere

By the way, is the spelling of 'comitee' the exact way you spelled it
in your field name?

Enter the Starting Date and the Ending Date in the controls, then run
the event to open the filtered report.
Note acWindowNormal is the default so no need to write it.

 

[Guest]

Hi Fred,
I just got home where I use Access 2003 and the problem does not exist with
that version; it only bugs with Access 2000. The application runs fine here
and I was able to display 3 different reports with the command
DoCmd.OpenReport...
About the "Comitee" spelling, your keen eye detected a possible problem. I
know now that it should be spelled "Committee". Actually, at the office, I
translated my code from French but overlooked that mistake (I had no
dictionary at hand). The actual code is the following:
strWhere = "tblSuiviMpp.DateComitéSpécial>=#" & datDébut & _
"# And tblSuiviMpp.DateComitéSpécial<#" & datFin & "#"
There are no mistakes in the French version.
You say "Remove the criteria from the query". I can't really do that because
it contains some other criteria that are needed to extract the right data.
Maybe I could add those other criteria in the strWhere clause I build in VBA.
That would be an option.
You say "Use 2 unbound controls...". Actually, the form I use is a simple
form with different buttons to print different reports ( and one button to
send the user back to the main menu which is also a form with different
buttons). There are no fields on it and for some particular reports, the user
is required to enter (in an Inputbox) the year he wants the report for. From
that year, I build the start and end dates in variables.
Finally, I think that the problem is an Access 2000 issue only and that
someone somewhere has already encountered it and circumvent in some way that
I would like to know. My users will upgrade to the 2003 version late this
summer. So I need to find an answer soon.
Thanks for your comment, it really helps even though it does not solve my
problem completely.
--
Jac Tremblay

Hi,
I built a report based on a query. Now, I need to add two parameters to the
query that the user will provide when he uses the application.
In VBA, I can build the complete Where clause and use it in the
DoCmd.OpenReport command.
I was wandering if it would be better to use a FilterName parameter rather
than a WhereCondition in the DoCmd.OpenReport command.
The WhereCondition I build is the following:
strWhere = "tblFollowUp.DateSpecialComitee>=#" & datStart & _
"# And tblFollowUp.DateSpecialComitee<#" & datEnd & "#"
Then, I thought of using this code:
DoCmd.OpenReport "rptStatReport", _
acViewPreview, , strWhere, acWindowNormal
My question is: is it better (or more effective) to use the FilterName or
the WhereCondition (2 parameters of the DoCmd.OpenReport command) in my case?
For some reason, it does not work and Access is closed when I use this code.
I use Access 2000 in Windows XP Pro.
Thanks.

The Where clause should be just fine.
Remove the criteria from the query.

Use 2 unbound controls on your form.
Name one datStart and the other datEnd


strWhere = "[DateSpecialComitee] >= #" & Me![datStart] & "# And
[DateSpecialComitee] < #" & Me![datEnd] & "#"

DoCmd.OpenReport "rptStatReport", acViewPreview, , strWhere

By the way, is the spelling of 'comitee' the exact way you spelled it
in your field name?

Enter the Starting Date and the Ending Date in the controls, then run
the event to open the filtered report.
Note acWindowNormal is the default so no need to write it.

 

[Albert D.Kallal]

You say "Use 2 unbound controls...". Actually, the form I use is a simple

form with different buttons to print different reports ( and one button to
send the user back to the main menu which is also a form with different
buttons).

You are better to stick with the idea you now have of building the where in
code.

If you use un-bound controls in your sql query, then some drawbacks arise

* you can't use the nice sql query unless the form is open
* if you need to use the sql query for a different report, then again
you are duck soup, since
now the query is tied to that form that MUST be open at all times for
the query to work
* the sql in the query looks VERY difficult to read with weird
forms!myforms stuff in sql. This
is not standard sql, and will make upgrading to sql server in the
future a nightmare
* if the form is accidentally closed, or you change the name of the
form, the sql will now break.

I could likely write on for more issues that arise here when forms
references in your sql. There are times when you need to...but, if you can
use a nice clean elegance solution as yours...then stick with it. Your
solution keeps the form ref OUT OF the sql. The resulting sql query is
clean, easier to read, and can be used in MANY different places in your
application, and is NOT tied to having one form open for it to function.

Finally, I think that the problem is an Access 2000 issue only and that
someone somewhere has already encountered it and circumvent in some way
that
I would like to know. My users will upgrade to the 2003 version late this
summer. So I need to find an answer soon.

It is either you have a references issue, or the date format in the other
computer is different. You should as a rule cast the sql into usa format...

So, use

strWhere = "tblFollowUp.DateSpecialComitee>=#" &
format(datStart,"mm/dd/yyyy") & _
"# And tblFollowUp.DateSpecialComitee<#" &
format(datEnd,"mm/dd/yyyy") & "#"

If the date setting is different on that other machine, the above will
fix/prevent this problem...

Also, try compiling your code on the offending machine (references problem).

My question is: is it better (or more effective) to use the FilterName or

the WhereCondition

Much better to use the WhereCondition. While you can actually place your
strWhere in the filter parameter of the report, the defense is that you can
*change* the filter AFTER the report has loaded. You can't do that with the
strWhere. In addition, the strFilter operates on the existing data set,
where as the strWhere restricts the report to the given data. It should be
well noted that after the report loads...if you check the filter
condition..it will be the same as your where condition..and can be changed.

So, really, the strWhere tells me, or any other developer looking at this
that you wanted to restrict the records for the report. Using strFilter says
I might want to change the records displayed *after* the report is loaded,
and the user views it. This filter change for a loaded report is not very
common. Further, some performance issues can arise if you change the filter
anyway. So, which one you use is much a matter of conveying your
intentions..and some performance issues can arise if you use the filter
anyway. So, in your case, strWhere is the best choice...

 

[Guest]

Hi Albert,
Thank you for your reply. It is very interesting. I will stick to my
strWhere clause as you advise.
I still would like to find out if there really is a problem with Access 2000
that was solved in Access 2003 concerning the DoCmd.OpenReport command.
I will check out your comments later as I am home now for the weekend and
cannot test the Access 2000 version.
On Monday, after some tests, I wil keep the community informed on the results.
Thank you again for your pertinent and informative answer.