Query based on Month not year

[DFeil]

Hello, can you tell me what expression to use to generate a query based on a month alone, not a year? For example, If I run the query today, it should bring up all the records for the month of November, but it doesn't matter what year the record was entered. Does that make sense? Thank you in advance.

Dave

 

[Alansidman]

You could add a new column to your query. Create an expression

MonthNr: =month(yourDateField)
This will return a value for the months. ie. October would be 10, November 11, etc.
In the criteria for the new field, type 11 for November and you should only get records for all Novembers.

 

[DFeil]

Thanks Alan, that will work, but I am looking for a way for the current date to be used as the base criteria, so that the user does not have to think about which number a month is... Got to keep is very simple if you know what I mean. Thanks.

Dave

 

[Alansidman]

So you want your users to be querying only the current month and all previous years with the same month? How are your users running the query? From a form where they could put the criteria (current date) in an unbound text box? Or some other way. Give me some more information about this.

 

[DFeil]

Yes the users will use a form, and and have to enter the month and the year they would like the queries/reports generated on. Format mm/yyyy . Does this help?

 

[nanscombe]

You could add a new column to your query. Create an expression

MonthNr: =month(yourDateField)
This will return a value for the months. ie. October would be 10, November 11, etc.
In the criteria for the new field, type 11 for November and you should only get records for all Novembers.

Create the column MonthNr: month(yourDateField) just add the criteria of =month(now())

 

[Alansidman]

Dave;
Here is how I did it. Keep the formula expression as shown above. In a unbound text box which you will format as hidden, type this expression in the property data tab control source, =Month(NameOfYourUnboundInputTextBox).

In the criteria for the field you created in your query, type Forms!YourSearchFormName.YourTextBoxNameForTheHiddenControl

In your query make this Helper column hidden. Uncheck the box above the criteria.

Alan

Edit: Or use the suggestion Nanscombe provided while I was figuring this more complicated way.

 

[nanscombe]

The more complicated way? That's usually how my suggestions end up.

 

[DFeil]

Both worked, you guys rock! Thank you.