Filters within Queries

[Guest]

I'm having difficulty achieving a result I'm wanting in a query.... I'm not
use to having problems with queries but this has been my week for them!

I have a table that has Month Booked, 1st Pmt Date Long, and Last Pmt Date
Long. I have multiple months (Month Booked) inside this table. My current
filter is this:

Jan 2007: IIf(IsNull([Month Booked]) Between #1/1/2007# And #1/31/2007# &
[Month Booked]<[Last Pmt Date Long] & [Month Booked]>[1st Pmt Date
Long],[Total Payment Forward],0)

I have 11 more Columns labeled Feb 2007, Mar 2007, Apr 2007, and so forth,
with almost the exact same formula as the Jan 2007 one. Basically, I want to
see the [Total Payment Forward] amount for 1/1/2007 if the Month Booked ='s
1/1/2007, and to show 0's for the rest of the months as it applies. Did this
make sense? How may I achieve this result?

 

[John Spencer]

First you must use the conjunction AND and not the ampersand.

Jan 2007: IIf([Month Booked] Between #1/1/2007# And #1/31/2007#
AND [Month Booked]<[Last Pmt Date Long]
AND [Month Booked]>[1st Pmt Date Long],[Total Payment Forward],0)

Second, I don't know what you are trying to accomplish with ISNull(Month
Booked) as that returns True or False and then Access will probably try
to see if 0 or -1 (true) is between the two dates you have specified.




'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================