Access Report Filters

[Guest]

Hi,

(MS Access 2002)

I'm having trouble with report filters.

When applying the filter, the report dosen't show the correct data, this can
vary from the correctly filtered data, no data and partial data using the
same data and filters.

strFilter = "(([VENDOR_CODE]='" & rstVendor!VENDOR_CODE & "') And
([TRANS_DATE]=#" & CurrentDate & "#))"

Reports!RPT_VENDOR_KANBAN_USAGE.Filter = strFilter
Reports!RPT_VENDOR_KANBAN_USAGE.FilterOn = True

The above code is how I set the filter, but I've also tried setting the
filter as follows:

DoCmd.OpenReport "RPT_VENDOR_KANBAN_USAGE", acViewPreview, , strFilter

Both seem to have the same output, sometimes correct, no data and variable
data. Tried formatting the dates to Us and UK but still get the same...

Any advice?

regards

Marcus

 

[Guest]

Try it without the parentheses.

strFilter = "[VENDOR_CODE] = '" & rstVendor!VENDOR_CODE & "' And
[TRANS_DATE] = #" & CurrentDate & "#"

Also, what is the context of Vendor_Code & CurrentDate? Are these controls
on the form used to open the report? Are you sure that you are getting a
valid filter string each time. Try this:

strFilter = "[VENDOR_CODE] = '" & rstVendor!VENDOR_CODE & "' And
[TRANS_DATE] = #" & CurrentDate & "#"
MsgBox "strFilter = " & strFilter

Make sure that strFilter actually has the expected values when the msgbox
pops up.

 

[Guest]

Yes, tried without the parenthesis first then added it to try.

The context of the data:

VENDOR_CODE String = 'MF01'
CurrentDate Date = #13/11/2007#

These are genarated through code by opening a recordset to the Vendor table
and setting the current date by the Date() function.

The filter is correct to how it should look, done this be stepping through
the code and placing a watch on the values.

This is the problem, everything looks OK but the report is failing to
display data. Tested again and when the report opened it had no data. So I
put the report into design mode then back to preview mode and the data was
correct, quite baffilling...

cheers

Marcus

Try it without the parentheses.

strFilter = "[VENDOR_CODE] = '" & rstVendor!VENDOR_CODE & "' And
[TRANS_DATE] = #" & CurrentDate & "#"

Also, what is the context of Vendor_Code & CurrentDate? Are these controls
on the form used to open the report? Are you sure that you are getting a
valid filter string each time. Try this:

strFilter = "[VENDOR_CODE] = '" & rstVendor!VENDOR_CODE & "' And
[TRANS_DATE] = #" & CurrentDate & "#"
MsgBox "strFilter = " & strFilter

Make sure that strFilter actually has the expected values when the msgbox
pops up.

Hi,

(MS Access 2002)

I'm having trouble with report filters.

When applying the filter, the report dosen't show the correct data, this can
vary from the correctly filtered data, no data and partial data using the
same data and filters.

strFilter = "(([VENDOR_CODE]='" & rstVendor!VENDOR_CODE & "') And
([TRANS_DATE]=#" & CurrentDate & "#))"

Reports!RPT_VENDOR_KANBAN_USAGE.Filter = strFilter
Reports!RPT_VENDOR_KANBAN_USAGE.FilterOn = True

The above code is how I set the filter, but I've also tried setting the
filter as follows:

DoCmd.OpenReport "RPT_VENDOR_KANBAN_USAGE", acViewPreview, , strFilter

Both seem to have the same output, sometimes correct, no data and variable
data. Tried formatting the dates to Us and UK but still get the same...

Any advice?

regards

Marcus

 

[Guest]

I always recommend
DoCmd.OpenReport "RPT_VENDOR_KANBAN_USAGE", acViewPreview, , strFilter

The dates must be of the format mm/dd/yyyy or similar. You might want to
check out Allen Browne's page on date formatting
http://www.allenbrowne.com/ser-36.html.

Also, if it is possible for the date field to also contain a time element,
you need to strip it like:
strFilter = "[VENDOR_CODE]='" & rstVendor!VENDOR_CODE & _
"' And DateValue([TRANS_DATE])=#" & Format(CurrentDate, "mm\/dd\/yyyy")
& "#"


--
Duane Hookom
Microsoft Access MVP

Yes, tried without the parenthesis first then added it to try.

The context of the data:

VENDOR_CODE String = 'MF01'
CurrentDate Date = #13/11/2007#

These are genarated through code by opening a recordset to the Vendor table
and setting the current date by the Date() function.

The filter is correct to how it should look, done this be stepping through
the code and placing a watch on the values.

This is the problem, everything looks OK but the report is failing to
display data. Tested again and when the report opened it had no data. So I
put the report into design mode then back to preview mode and the data was
correct, quite baffilling...

cheers

Marcus

Try it without the parentheses.

strFilter = "[VENDOR_CODE] = '" & rstVendor!VENDOR_CODE & "' And
[TRANS_DATE] = #" & CurrentDate & "#"

Also, what is the context of Vendor_Code & CurrentDate? Are these controls
on the form used to open the report? Are you sure that you are getting a
valid filter string each time. Try this:

strFilter = "[VENDOR_CODE] = '" & rstVendor!VENDOR_CODE & "' And
[TRANS_DATE] = #" & CurrentDate & "#"
MsgBox "strFilter = " & strFilter

Make sure that strFilter actually has the expected values when the msgbox
pops up.

Hi,

(MS Access 2002)

I'm having trouble with report filters.

When applying the filter, the report dosen't show the correct data, this can
vary from the correctly filtered data, no data and partial data using the
same data and filters.

strFilter = "(([VENDOR_CODE]='" & rstVendor!VENDOR_CODE & "') And
([TRANS_DATE]=#" & CurrentDate & "#))"

Reports!RPT_VENDOR_KANBAN_USAGE.Filter = strFilter
Reports!RPT_VENDOR_KANBAN_USAGE.FilterOn = True

The above code is how I set the filter, but I've also tried setting the
filter as follows:

DoCmd.OpenReport "RPT_VENDOR_KANBAN_USAGE", acViewPreview, , strFilter

Both seem to have the same output, sometimes correct, no data and variable
data. Tried formatting the dates to Us and UK but still get the same...

Any advice?

regards

Marcus