question

GlennMurphy-8002 avatar image
0 Votes"
GlennMurphy-8002 asked gustav answered

MS Access report filter dates base on combo box selections for date range

Greetings and thanks in advance for any assistance you can give me. I'm having issues with a MS Access query I use to filter a report to a specific set of months. To do this I present the user a form that says "pick a range of months" and gives them two combo boxes. Those combo boxes are unbound value lists, two column (1 - Jan 2 - Feb etc), and the first column is bound. Then the query that is embedded in the report has a custom field 'Birth Month' that uses Month([DOB]). The criteria on that custom field is >=combo box 1 value and <=combo box 2 value. This works fine if the months selected are between Jan and Sep, but if a month with an associated two digit number is selected I get either no results ( start month Feb-Sep and end month Oct-Dec) or I get Jan included with the last three months (start month Jan and end month Oct-Dec). With the second failure mode it doesn't matter which month I end with, Oct thru Dec and included in the report. So, after all that, is there a better way to define the combo box values so that the query will behave properly? Or should I do something special in the report data query that I'm not aware of to make it accept values for the custom filed Month([]date])? What I have in there now doesn't create an error, but it doesn't produce the correct results either. Any suggestions welcome. Thanks.

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GlennMurphy-8002 avatar image
0 Votes"
GlennMurphy-8002 answered thedbguy commented

I've never had time to build a demo version, as as the real database contains PII I can't share it...but for anyone else who might encounter this issue I have at least one solution. I created a single column query using SELECT DISTINCT MONTH ([Employee]![DOB]) from the Employee table. That gives me numbers 1 through 12. Then I used that query as the combo box's row source instead of the value list. That works. The only minor down side is the options presented by the combo box drop down are numbers rather than the three character month designations that were there before. That is not my preference, but the users are bright enough that it will work. I'm still not sure why the other approach was giving strange results under differing conditions, but I have way too much real work to ponder the meaning of the universe. I don't know how to mark this closed, but I consider it so.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi. Glad to hear you found a working solution to your problem. Good luck with your project.

0 Votes 0 ·
thedbguy avatar image
0 Votes"
thedbguy answered

Hi. There are better ways, but you could try something like:

Month(DOB) Between Val(Combo1) And Val(Combo2)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GlennMurphy-8002 avatar image
0 Votes"
GlennMurphy-8002 answered

Thanks for the reply. I have tried that approach previously, but I tried it again and this time I noticed something odd. If I run the query on Apr - Sep, I get correct results, but when I query on Apr - Oct I get results that include Feb - Apr and Oct - Dec. So if there are better ways to accomplish this, I'm very interested in that.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. It might help if you could share a demo version of your db, so we can see if there are other things at play that's messing up with the outcome we're trying to achieve.

Just a thought...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

The behaviour which you are experiencing is consistent with the restriction being applied being on the basis of string expressions rather than numbers. I assume that you are referencing the combo boxes as parameters. If so then try declaring the parameters as SHORT in a PARAMETERS clause in the query. They will then be evaluated as integer numbers.

The method described in your first post of using a value list such as:

1;January;2;February;3;March;4;April;5;May;6;June;7;July;8;August;9;September;10;October;11;November;12;December

as the combo box's RowSource will enable you to reference the control as a parameter provided that its ColumnWidths property is 0, its BoundColumn property is 1, and its ColumnCount property is 2.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GlennMurphy-8002 avatar image
0 Votes"
GlennMurphy-8002 answered

Thanks Ken, I may try that implementation in a future version update. For now what I have works.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

gustav avatar image
0 Votes"
gustav answered

You can use a query like this where M1 and M2 are your two month values:

 PARAMETERS
     M1 Short,
     M2 Short;
 SELECT 
     *
 FROM 
     YourTable
 WHERE 
     ((Month([DateField])>=[M1] And Month([DateField])<=[M2]) AND [M1]<=[M2]) 
     OR 
     ((Month([DateField])>=[M1] Or Month([DateField])<=[M2]) AND [M1]>[M2])
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.