Year To Date Record Selection

[Kathy Webster]

I am trying to select records in a query where the [InvoiceDate] is between
the first of the year selected and the end of the month of the month the
user selects when he runs the query.

ie.:
User enters 5 in an unbound form's "Month" field.
[forms]![CaptureDate]![Month]
User enters 2006 in an unbound form's "Year" field.
[forms]![CaptureDate]![Year]
Query should select records whose Invoice Date is between 1/1/06 and
5/31/06.

My query has Year([InvoiceDate]) with the criteria
[forms]![ClientMonthlyFileTotals]![year]

Secondly, my query has Month([InvoiceDate]) with the criteria
<=[forms]![ClientMonthlyFileTotals]![month]

If I remove the <= from the Month criteria, I get an accurate selection of
Invoices dated in the month of June 2006. However, when I add the <=, I
still only see invoices dated in the month of June 2006.

TIA,
Kathy

 

[John Vinson]

I am trying to select records in a query where the [InvoiceDate] is between
the first of the year selected and the end of the month of the month the
user selects when he runs the query.

ie.:
User enters 5 in an unbound form's "Month" field.
[forms]![CaptureDate]![Month]
User enters 2006 in an unbound form's "Year" field.
[forms]![CaptureDate]![Year]
Query should select records whose Invoice Date is between 1/1/06 and
5/31/06.

I'd make two suggestions. Don't use the reserved words Month and Year
for these controls - they're very likely to be confusing Access and
returning the current month and current year instead. Let's say name
them txtMonth and txtYear.

And change your criterion to

= DateSerial(Me!txtYear, 1, 1) AND < DateSerial(Me!txtYear, Me!txtMonth + 1, 1)

John W. Vinson[MVP]

 

[Kathy Webster]

Thank you John. DateSerial works wonderfully!
Interesting I can't find anything about it in the Access Help screens. :-(
-kathy

I am trying to select records in a query where the [InvoiceDate] is
between
the first of the year selected and the end of the month of the month the
user selects when he runs the query.

ie.:
User enters 5 in an unbound form's "Month" field.
[forms]![CaptureDate]![Month]
User enters 2006 in an unbound form's "Year" field.
[forms]![CaptureDate]![Year]
Query should select records whose Invoice Date is between 1/1/06 and
5/31/06.

I'd make two suggestions. Don't use the reserved words Month and Year
for these controls - they're very likely to be confusing Access and
returning the current month and current year instead. Let's say name
them txtMonth and txtYear.

And change your criterion to

= DateSerial(Me!txtYear, 1, 1) AND < DateSerial(Me!txtYear, Me!txtMonth +
1, 1)

John W. Vinson[MVP]

 

[John Vinson]

Thank you John. DateSerial works wonderfully!
Interesting I can't find anything about it in the Access Help screens. :-(

Annoyingly, it is there - but you have to pick up the rock that it's
hiding under <g>.

Open the VBA editor (by typing Ctrl-G for example) and look in THAT
help file. It's there along with a lot of other functions which can be
used in queries, but which don't show up in the Access "application"
help.

John W. Vinson[MVP]