filter report

[ayh1]

I have a report filter that filters the reports by month and year:

        DoCmd.OpenReport "AttWholeCity", acPreview, , " Month = '" & txtCourseDateMonth & "' AND Year = '" & txtCourseDateYear & "'"

I want the code to also show these two columns where there are null/blank values aswell, for example if I filter by apr 2015 i want the report to show these columns as well as blank columns is this possible

 

[plog]

First, a few notes about your Month and Year fields:

1. Month and Year are both reserved words (https://support.microsoft.com/en-us/kb/286335) and shouldn't be used as names. It makes writing code and queries just that much more difficult (Looking at your code, you will soon find out why).

2. According to your sample criteiria ('apr 2015'), Year should be numeric, but you are treating it like text in your report filter. You really should compare correctly for your datatypes. Is the field Year a numeric or text field in the underlying datasource of AttWholeCity?


Now, for your issue you need to include the possibility of Null data in your criteria. You would use an OR statement to connect them to your existing criteria and parenthesis around them as well.

Here's what your month criteria should be:

"([Month]='" & txtCourseDateMonth & "' OR IsNull([Month])=False)"


Follow that pattern to make it work for your Year as well.

 

[ayh1]

First, a few notes about your Month and Year fields:

1. Month and Year are both reserved words (https://support.microsoft.com/en-us/kb/286335) and shouldn't be used as names. It makes writing code and queries just that much more difficult (Looking at your code, you will soon find out why).

2. According to your sample criteiria ('apr 2015'), Year should be numeric, but you are treating it like text in your report filter. You really should compare correctly for your datatypes. Is the field Year a numeric or text field in the underlying datasource of AttWholeCity?


Now, for your issue you need to include the possibility of Null data in your criteria. You would use an OR statement to connect them to your existing criteria and parenthesis around them as well.

Here's what your month criteria should be:

"([Month]='" & txtCourseDateMonth & "' OR IsNull([Month])=False)"


Follow that pattern to make it work for your Year as well.

I tried it with month only first it says error, expecting two arguments

DoCmd.OpenReport "AttWholeCity", acPreview, , "([Month]='" & txtCourseDateMonth & "' OR IsNull([Month])=False)"

 

[plog]

That code looks correct. Can you post your database?

 

[ayh1]

That code looks correct. Can you post your database?

It would not work as it is linked to sql tables

 

[plog]

Are you able to open the report directly by double clicking on it?

 

[ayh1]

yes i have a show all button and filter button, the filter takes the show all report and limits it down

 

[plog]

Not talking about forms and buttons. Can you directly open the report by double clicking on it? Does it work that way or does it prompt you in the same manner?

 

[ayh1]

Not talking about forms and buttons. Can you directly open the report by double clicking on it? Does it work that way or does it prompt you in the same manner?

yep if i double click the report from the toolbar on the side it currently shows all

 

[plog]

What is it prompting you for? [Month] values?