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

Kathy Webster wrote:
> 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, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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

"Rick Brandt" <(E-Mail Removed)> wrote in message
news:Rk9Zg.18707$(E-Mail Removed)...
> Kathy Webster wrote:
>> 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, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>

Kathy Webster wrote:
> Many thanks!! How do you figure this stuff out??

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

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Is that all it takes? A decade?

"Rick Brandt" <(E-Mail Removed)> wrote in message
news:JgdZg.17368$(E-Mail Removed)...
> Kathy Webster wrote:
>> Many thanks!! How do you figure this stuff out??
>
> Hang around these groups for over a decade and you remember a couple
> things :-)
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>

Kathy Webster wrote:
> Is that all it takes? A decade?

Give or take.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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!



"Rick Brandt" <(E-Mail Removed)> wrote in message
news:T%dZg.17397$(E-Mail Removed)...
> Kathy Webster wrote:
>> Is that all it takes? A decade?
>
> Give or take.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>

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...

"Kathy Webster" <(E-Mail Removed)> wrote in message
news:PruZg.8812$(E-Mail Removed)...
> 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!
>
>
>
> "Rick Brandt" <(E-Mail Removed)> wrote in message
> news:T%dZg.17397$(E-Mail Removed)...
>> Kathy Webster wrote:
>>> Is that all it takes? A decade?
>>
>> Give or take.
>>
>> --
>> Rick Brandt, Microsoft Access MVP
>> Email (as appropriate) to...
>> RBrandt at Hunter dot com
>>
>
>

Kathy Webster wrote:
> 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...

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.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

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

"Rick Brandt" <(E-Mail Removed)> wrote in message
news:RoAZg.12337$(E-Mail Removed)...
> Kathy Webster wrote:
>> 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...
>
> 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.
>
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>