Strange complicated Query

[tjtross]

I am a newbie with only a few hours of training. I am trying to build a parameter query, where it will take a start month number from the Startup form. The start month (CD_StartM) is a ComboBox based on another query (qryForm_DateM). The end month (CD_EndM) is a ComboBox based on another query (qryForm_DateM). I wanted to have the ability to enter only the Start month and it give me all data points after and including that month, and I wanted to have the ability to enter only the End month and it give me all the data points before and including that month and I wanted to have the ability to enter a Start and End month and it give me all the data points between and including those months and the ability to enter no Start and End months and it give me all the data points.

I entered the following in the criteria box and it fails:
Between ([Forms]![frmStartup]![CD_StartM] Or [Forms]![frmStartup]![CD_StartM] Is Null) and ([Forms]![frmStartup]![CD_EndM] Or [Forms]![frmStartup]![CD_EndM] Is Null)

I also tried adding the # sign and it fails:
Between #([Forms]![frmStartup]![CD_StartM] Or [Forms]![frmStartup]![CD_StartM] Is Null)# and #([Forms]![frmStartup]![CD_EndM] Or [Forms]![frmStartup]![CD_EndM] Is Null)#

I am completely stumped. :banghead:

 

[pbaldy]

You realize if the field contains months 1-12, this wouldn't handle year? In any case, this type of thing:

Between Nz([Forms]![frmStartup]![CD_StartM], 0) And Nz([Forms]![frmStartup]![CD_EndM], 12)

 

[tjtross]

Thanks Pbaldy that worked like a charm. One more question if you don't mind, I have the same thing for years. I tried this:
Between Nz([Forms]![frmStartup]![CD_StartY],0) And Nz([Forms]![frmStartup]![CD_EndY],2400) and it didn't work.
Would you point me in the right direction?

 

[pbaldy]

I would expect it to work the same way. Is it clashing with the month criteria (does it work alone)?

 

[tjtross]

Paul,
The two must be clashing. When I put in 4 (month) and 2014 (year) for the ending parameters. I get 1-4 of 2014 and nothing for 2013.

I have a drop down box for start month, start year, end month and end year.

I am beginning to think I may have to concatenate the month and year to get what I want.

 

[pbaldy]

Would there be data for 1-4 in 2013? It certainly muddies the water when you try to work with date components separately instead of as dates. If you concatenate, make sure to use yyyymm rather than mmyyyy.

 

[tjtross]

I see how it has muddied everything up with the year and month being separate. I have taken you advise and used the yyyy/mm concatenation. It seems to be working. Thank you so much for you help.

In your statement:
Between Nz([Forms]![frmStartup]![CD_StartM], 0) And Nz([Forms]![frmStartup]![CD_EndM], 12)
What does the 12 mean? Once I have everything concatenated, do I need to change the 12 to a different number?

 

[pbaldy]

Yes, it's intended to be larger than any existing value, so something like 202912.