Re: Report in VBA - FilterName or WhereCondition

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.
--
Jac Tremblay


"Albert D.Kallal" wrote:

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...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx
http://www.members.shaw.ca/AlbertKallal



.

Relevant Pages

  • Re: Problems adding "All" to combo box
    ... SQL for each combo box is in a previous post). ... You have a report defined that is named AI Thru Hole Work Log_Operator ... of the report or form you want to filter ...
    (microsoft.public.access.formscoding)
  • Re: Problems adding "All" to combo box
    ... You code to open the report is, I think, a little off. ... SQL for each combo box below. ... Dim strWhere As String ... of the report or form you want to filter ...
    (microsoft.public.access.formscoding)
  • Re: Report in VBA - FilterName or WhereCondition
    ... If you use un-bound controls in your sql query, ... strWhere in the filter parameter of the report, the defense is that you can ... *change* the filter AFTER the report has loaded. ...
    (microsoft.public.access.modulesdaovba)
  • Change Database at Runtime for Crystal Report
    ... When creating each report, crystal asks where the database to use is, so I ... specify our SQL server using the ADO option. ... The problem is that the report has an SQL Query Command embedded in ...
    (microsoft.public.dotnet.general)
  • Change Database at Runtime for Crystal Report
    ... When creating each report, crystal asks where the database to use is, so I ... specify our SQL server using the ADO option. ... The problem is that the report has an SQL Query Command embedded in ...
    (microsoft.public.dotnet.languages.csharp)