Before and Including month selected (1 Viewer)

[Angello Pimental]

Greetings everyone,

I have a query that is used to pull records for a certain month from my table. I use the following criteria, as the user selects the Month and Year from comboboxes on a form.
The form is based on the query.

Field: Year([Renewal])
Criteria: [forms]![renewalform]![year]

Field: Month([Renewal])
Criteria: [forms]![renewalform]![month]

My question is, how do I alter the code so that if the user selects August 2001, it will bring back all records from the month of August 2001, and all records before that date??

Thnx

 

[Abby N]

Hello Angello. This should do it. Include [Renewal] as a field in the query with this criterion. (It assumes an American date format. If you use the European format you'll need to adjust it.)

<DateAdd("m",1,CDate([forms]![renewalform]![month] & "/1/" & [forms]![renewalform]![year]))

Good Luck.

~Abby

 

[Angello Pimental]

Abby,

HOw would I adjust this for the European date format??

Thnx

 

[Abby N]

<DateAdd("m",1,CDate("1/" & [forms]![renewalform]![month] & "/" & [forms]![renewalform]![year]))

~Abby

 

[Angello Pimental]

THanks for the reply Abby,

But it seems that Access thinks that it is too complex... Whatever that means..

Oh well, I will have to try something else...

Thnx

 

[Pat Hartman]

I think you may need to use a US formatted date in this situation. Despite your Windows date settings (which only affect how a date is displayed), Access stores dates internally as serial numbers so how they are formatted is irrelevant. But, SQL needs externally supplied dates in US format.