Query Question

If Previous Date is intended to be the month/year prior to the current month
whenever the query is opened then you don't need a field for it at all as you
can compute it, so the query would be along these lines:

SELECT Account,
IIf(Max(Current) = Format(DateAdd("m",-1,Date()),"mm/yyyy"),"","")
AS NotBilled
FROM BillsReceived
GROUP BY Account;

where Account and Current are columns in a table BillsReceived.

This assumes that Current is a text field which stores the month and year
when the bill is received in the format mm/yyyy. This would present a
problem with bills received in different years however, e.g. a bill received
in December 2009 would have Current Value of '12/2009' whereas one received
in January 2010 would have a current value of '01/2010', but of these two
that for December would be returned as the 'later' bill by the Max operator
as the string '12/2009' sorts after the string '01/2010'.

You could get round this by returning a true date/time value of the first of
the month from the string

SELECT Account,
IIf(DateSerial(Right(Current,4),Left(Current,2),1)
= DateSerial(Year(Date()),Month(Date())-1,1) ,"","")
AS NotBilled
FROM BillsReceived
GROUP BY Account;

If on the other hand you were to store the date when each bill is recived in
a column date/time data type, DateReceived say, then you could use that to
return the first of the month:

SELECT Account,
IIf(DateSerial(Year(DateReceived),Month(DateReceiv ed),1)
= DateSerial(Year(Date()),Month(Date())-1,1) ,"","")
AS NotBilled
FROM BillsReceived
GROUP BY Account;

Ken Sheridan
Stafford, England

Jennie wrote:
Karl,

I tried to run the report with only this in the query:

Recent: IIf([Current][PreviousDate],"*","")

And I got an error message saying that the expression is not part of an
aggregate function. Did I enter it correctly? Thanks for your help.

The Max as you are using it will not give you the latest date as it is not
perfoming a function on a date. It is giving you the Max of the IIF function.
[quoted text clipped - 31 lines]
bill was received in 05/2009, I want the "" to appear. If they do match,
then I don't want anything to appear. What am I doing wrong?

--
Message posted via http://www.accessmonster.com