Report must be saved for filter to work?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My report filter only works after the report is saved. The filter is put in
with the following coding. That appears to be correct, but when I type in the
same idential criteria with the report open it gives me all records or no
records. After I save the report with the filter criteria it seems to work...
most of the time anyway.

strFilter = "[Dateofentry] " & strbeginningdate & " AND [Dateofentry] " &
strEndingDate & " AND [Technician Name] " & strfindtech & " AND [Scribe Name]
" & strfindscribe & " AND [Examdate] " & strfindexamdate & " AND [Patient
Name] " & strfindpatname & " AND [Auditmonth] " & strfindauditmonth & " AND
[Enteredby] " & strfindenteredby

Reports![rptFindings].FilterOn = True
Reports![rptFindings].Filter = strFilter
 
I suspect it's all just a matter of reversing the order of the last two
lines in your code, i.e. first set the filter, then force it.

HTH,
Nikos
 
Reversed the last two lines and it doesn't return any records even after
saving the report. The filter is there AND it is on.
 
I must have been blind! Your strFilter is all wrong, which is why you
get no records after it is saved You got all records prior to saving
originally, simply because it wasn't applied at all, due to the wrong
sequence of the last two lines). What is wrong with it is you are not
using any operands at all! Also, you need hashes around the Date/Time
parametes, and quotes around string variables, so Jet treats each as the
right type. Try something like:

strFilter = "[Dateofentry] Between #" & strbeginningdate _
& "# AND #" & strEndingDate & "# AND [Technician Name] ='" _
& strfindtech & "' AND [Scribe Name] ='" & strfindscribe _
& "' AND [Examdate] '#" & strfindexamdate _
& "# AND [Patient Name] ='" & strfindpatname _
& "' AND [Auditmonth] =" & strfindauditmonth _
& " AND [Enteredby] ='" & strfindenteredby & "'"
Debug.Print strFilter

Note: I have assumed Auditmonth to be numeric, thus no (single) quotes
around it; if text, then add the single quotes like around the other
tesxt strings. I have also assumed the date fields to actually be
date/time type.

The Debug.Print will print the actual sting in the immediate window
(Ctrl+G to open), so you can check the string that is actually generated.

If you still have problems, post back your modified code and the
strFilter string from the immediate window.

HTH,
Nikos
 
Sorry I didn't post the entire SQl for your answer. I have the

' Build criteria string for Enteredby field
If IsNull(Me.findenteredby.Value) Then
strfindenteredby = "Like '*'"
Else
strfindenteredby = "='" & Me.findenteredby.Value & "'"
End If

for each field. So my syntax isn't all wrong..... I just don't understand
why is doesn't work!
 
You are still likely getting a wrong string. It happens all the time
when constructing filter strings in code! Add the Debug.Print, and check
the strFilter you actually get. Post back with the string and the full
code if you need.

For what it's worth, what I do in similar situations is exclude a field
from the filter altogether if there is no input criterion, so I don't
get redundant filtering with Like '*'. I haven't actually measured this,
but suspect it might make a difference in performance on big tables.

Nikos
 
I have dropped the patname field on purpose. The following is was is posted
to the filter and returns no records at all. I was getting syntax errors when
I tried the between coding for the Dateofentry field

[Dateofentry] >=#01/01/2005# AND [Dateofentry] <=#12/31/2005# AND
[TechnicianName] ='Cathy' AND [ScribeName] ='Bessent' AND [Examdate]
=#7/25/2005# AND [Auditmonth] =#7/1/2005# AND [Enteredby] ='Darleen'
 
This looks generally OK, except for the:

[Auditmonth] =#7/1/2005#

part, which looks somewhat fishy(?) From the field name, I would have
expected it holds entries like Jan, Feb etc or 1,2 etc, whereas you are
setting the criterion to a Date. What type is the field, and what are
your entries in there?

Nikos
 
Sorry.. it is accepting date criteria only.. will be changing the name to
Audit Date on the form when I get this working
 
In that case the filter string is (technically) correct... unless your
date format in your regional settings is non-US, in which case you are
trying to match #7/1/2005# (VBA only understands US format) with
#1/7/2005# (or whatever) in your table! Any chance?

To verify the filter string would return records, start making a query
and add the table (or query) that is the report's recordsource, without
any criteria in design view; then revert to SQL view, and add the word
WHERE, followeb by a space and the filter string, inserting it right
before the last line (WITH OWNER ACCESS OPTION;). Run it; does it return
any records? If not, then the problem is in the criteria themselves.

Nikos
 
Regional settings were fine. I tried the second part of your post and it did
not return any records, so it is a criteria problem. Now where do I go from
here?

P
 
Sorry about the quick posting and talking to myself on here. I may have
found a clue. When I run the form it generates the filter, opens and posts
the filter to the report. Result = no records. BUT when I remove all the
parts of the filter that contain no user data (otherwords all records for
that field, including Like '*' and "" and NOTHING) then SAVE the report, it
works when I open the saved report! The results are perfect but only that
way... So far atleast! ~L~

P
 
It is a problem with the criteria!!!!!!

Out of context:
strfindexamdate = "' AND ([Examdate] Like " * " Or [Examdate] Is
Null)'"

It will not take the extra set of quotes around the * How do you enter a
double set of quotation marks? I think this might fix it!

P
 
Use single quotes, and make sure you don't include spaces around the *
within them! Also, in VBA the syntax is: IsNull(argument).

strfindexamdate = "' AND ([Examdate] Like '*' Or IsNull([Examdate])"
 
Back
Top