25th of month to 25th of month

[Kathy Webster]

Our reporting period is from the 25th of the month to the 25th of the next
month.

I have a [MonthReporting] field and a [YearReporting] control on a form
where the user selects the month and year they wish to report on. So if they
select, for example, October in the [MonthReporting] field, and 2006 in the
[YearReporting] field, I have to show them all records where the
[PaymentDate] is between September 25, 2006 and October 25, 2006.

Can anyone help?
TIA,
Kathy

 

[Rick Brandt]

Our reporting period is from the 25th of the month to the 25th of the
next month.

I have a [MonthReporting] field and a [YearReporting] control on a
form where the user selects the month and year they wish to report
on. So if they select, for example, October in the [MonthReporting]
field, and 2006 in the [YearReporting] field, I have to show them all
records where the [PaymentDate] is between September 25, 2006 and
October 25, 2006.
Can anyone help?
TIA,
Kathy

SELECT *
FROM TabelName
WHERE DateField >= DateSerial(YearReporting, MonthReporting-1, 25)
AND DateField < DateSerial(YearReporting, MonthReporting, 26)

 

[Kathy Webster]

Many thanks!! How do you figure this stuff out??

Our reporting period is from the 25th of the month to the 25th of the
next month.

I have a [MonthReporting] field and a [YearReporting] control on a
form where the user selects the month and year they wish to report
on. So if they select, for example, October in the [MonthReporting]
field, and 2006 in the [YearReporting] field, I have to show them all
records where the [PaymentDate] is between September 25, 2006 and
October 25, 2006.
Can anyone help?
TIA,
Kathy

SELECT *
FROM TabelName
WHERE DateField >= DateSerial(YearReporting, MonthReporting-1, 25)
AND DateField < DateSerial(YearReporting, MonthReporting, 26)

 

[Rick Brandt]

Many thanks!! How do you figure this stuff out??

Hang around these groups for over a decade and you remember a couple things

 

[Kathy Webster]

Is that all it takes? A decade?

Hang around these groups for over a decade and you remember a couple
things

 

[Rick Brandt]

Is that all it takes? A decade?

Give or take.

 

[Kathy Webster]

Aargh! Just when I thought all was right in the world....

I want to catch all payments made from 9/25/06 through 10/24/06.
My query criteria for [PaymentDate] is set to:

DateSerial([forms]![ExcelExport]![YearReporting],[forms]![ExcelExport]![MonthReporting]-1,25)
And
<DateSerial([forms]![excelExport]![YearReporting],[forms]![excelExport]![MonthReporting],24)

The [MonthReporting] control on my ExcelExport form is 10.
The [YearReporting] control on my ExcelExport form is 2006.

It is only selecting one record: Payment date 10/20/06.
It is omitting 6 other records whose payment date falls within the desired
range.

Helllp!

 

[Kathy Webster]

Ohmygod, I actually figured it out myself! I forgot to change it from > and
< to >= and <=.
The payments it was missing were the ones on the cutoff days, which is what
I was testing for...
Boy is my face red...

Aargh! Just when I thought all was right in the world....

I want to catch all payments made from 9/25/06 through 10/24/06.
My query criteria for [PaymentDate] is set to:

DateSerial([forms]![ExcelExport]![YearReporting],[forms]![ExcelExport]![MonthReporting]-1,25)
And
<DateSerial([forms]![excelExport]![YearReporting],[forms]![excelExport]![MonthReporting],24)

The [MonthReporting] control on my ExcelExport form is 10.
The [YearReporting] control on my ExcelExport form is 2006.

It is only selecting one record: Payment date 10/20/06.
It is omitting 6 other records whose payment date falls within the
desired range.

Helllp!

Give or take.

 

[Rick Brandt]

Ohmygod, I actually figured it out myself! I forgot to change it from
The payments it was missing were the ones on the cutoff days, which
is what I was testing for...
Boy is my face red...

Actually you normally want >= on the low date and < on the high date while using
one day past the value you actually want. That is IF your date values also have
time components other than midnight (zero).

If your dates contain non-midnight times then <= on the high date will only
match records that are exactly at midnight on that day. Therefore you really
need < midnight of the following day.

If all of your dates have midnight as the time then what you are using now is
fine. Note that none of this has anything ot do with how you are formatting
your dates for display.

 

[Kathy Webster]

Thank you. Point very well taken. Sometimes I forget about that little time
component that has bit me in the butt on several occasions...