Query Question

I have the following expression in my query:

Recent: Max(IIf([Current]=[PreviousDate]," ",""))

When I switch the "" and the " " it works perfectly but when I have the
Truepart set as " " all records have the "". I tried substituting " " with
another character and it works. I also tried using "" (no space) and it still
didn't work.

If you need a little background, this database tracks the 91 utility bills
we receive each month. Not all of the accounts are billed monthly, I want the
"" to appear next to the accounts that did not receive a bill for the
current period. Current is formatted as mm/yyyy and is set as month and year
of the most recent bill received for each account. PreviousDate is also
formatted as mm/yyyy and is set as the previous month and year (For example,
we receive May's bills in June, so if I were to run the report today then
PreviousDate would be 05/2009). If the two fields do not match, meaning no
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?

very strange!
Did you try changing the "=" to "" and reversing the conditions?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Jennie" wrote:

I have the following expression in my query:

Recent: Max(IIf([Current]=[PreviousDate]," ",""))

When I switch the "" and the " " it works perfectly but when I have the
Truepart set as " " all records have the "". I tried substituting " " with
another character and it works. I also tried using "" (no space) and it still
didn't work.

If you need a little background, this database tracks the 91 utility bills
we receive each month. Not all of the accounts are billed monthly, I want the
"" to appear next to the accounts that did not receive a bill for the
current period. Current is formatted as mm/yyyy and is set as month and year
of the most recent bill received for each account. PreviousDate is also
formatted as mm/yyyy and is set as the previous month and year (For example,
we receive May's bills in June, so if I were to run the report today then
PreviousDate would be 05/2009). If the two fields do not match, meaning no
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?

I do not understand your use of Max here.

Why not use it this way --
Recent: IIf([Current]=[PreviousDate], "", "")

and sort Descending?

"Jennie" wrote:

I have the following expression in my query:

Recent: Max(IIf([Current]=[PreviousDate]," ",""))

When I switch the "" and the " " it works perfectly but when I have the
Truepart set as " " all records have the "". I tried substituting " " with
another character and it works. I also tried using "" (no space) and it still
didn't work.

If you need a little background, this database tracks the 91 utility bills
we receive each month. Not all of the accounts are billed monthly, I want the
"" to appear next to the accounts that did not receive a bill for the
current period. Current is formatted as mm/yyyy and is set as month and year
of the most recent bill received for each account. PreviousDate is also
formatted as mm/yyyy and is set as the previous month and year (For example,
we receive May's bills in June, so if I were to run the report today then
PreviousDate would be 05/2009). If the two fields do not match, meaning no
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?

I think the issue with the Max() function. Why not just the iif?

Karl,

I use Max because the report shows all bills ever received for every
account. I only want to see if the most current billed date matches the
PreviousDate field, not all of the bills.

"KARL DEWEY" wrote:

I do not understand your use of Max here.

Why not use it this way --
Recent: IIf([Current]=[PreviousDate], "", "")

and sort Descending?

"Jennie" wrote:

I have the following expression in my query:

Recent: Max(IIf([Current]=[PreviousDate]," ",""))

When I switch the "" and the " " it works perfectly but when I have the
Truepart set as " " all records have the "". I tried substituting " " with
another character and it works. I also tried using "" (no space) and it still
didn't work.

If you need a little background, this database tracks the 91 utility bills
we receive each month. Not all of the accounts are billed monthly, I want the
"" to appear next to the accounts that did not receive a bill for the
current period. Current is formatted as mm/yyyy and is set as month and year
of the most recent bill received for each account. PreviousDate is also
formatted as mm/yyyy and is set as the previous month and year (For example,
we receive May's bills in June, so if I were to run the report today then
PreviousDate would be 05/2009). If the two fields do not match, meaning no
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?

Dorian,

I tried that and I am still having the same problem. I have no idea what I
am doing wrong.

"Dorian" wrote:

very strange!
Did you try changing the "=" to "" and reversing the conditions?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Jennie" wrote:

I have the following expression in my query:

Recent: Max(IIf([Current]=[PreviousDate]," ",""))

When I switch the "" and the " " it works perfectly but when I have the
Truepart set as " " all records have the "". I tried substituting " " with
another character and it works. I also tried using "" (no space) and it still
didn't work.

If you need a little background, this database tracks the 91 utility bills
we receive each month. Not all of the accounts are billed monthly, I want the
"" to appear next to the accounts that did not receive a bill for the
current period. Current is formatted as mm/yyyy and is set as month and year
of the most recent bill received for each account. PreviousDate is also
formatted as mm/yyyy and is set as the previous month and year (For example,
we receive May's bills in June, so if I were to run the report today then
PreviousDate would be 05/2009). If the two fields do not match, meaning no
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?

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.

"Jennie" wrote:

Karl,

I use Max because the report shows all bills ever received for every
account. I only want to see if the most current billed date matches the
PreviousDate field, not all of the bills.

"KARL DEWEY" wrote:

I do not understand your use of Max here.

Why not use it this way --
Recent: IIf([Current]=[PreviousDate], "", "")

and sort Descending?

"Jennie" wrote:

I have the following expression in my query:

Recent: Max(IIf([Current]=[PreviousDate]," ",""))

When I switch the "" and the " " it works perfectly but when I have the
Truepart set as " " all records have the "". I tried substituting " " with
another character and it works. I also tried using "" (no space) and it still
didn't work.

If you need a little background, this database tracks the 91 utility bills
we receive each month. Not all of the accounts are billed monthly, I want the
"" to appear next to the accounts that did not receive a bill for the
current period. Current is formatted as mm/yyyy and is set as month and year
of the most recent bill received for each account. PreviousDate is also
formatted as mm/yyyy and is set as the previous month and year (For example,
we receive May's bills in June, so if I were to run the report today then
PreviousDate would be 05/2009). If the two fields do not match, meaning no
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?

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.



"KARL DEWEY" wrote:

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.

"Jennie" wrote:

Karl,

I use Max because the report shows all bills ever received for every
account. I only want to see if the most current billed date matches the
PreviousDate field, not all of the bills.

"KARL DEWEY" wrote:

I do not understand your use of Max here.

Why not use it this way --
Recent: IIf([Current]=[PreviousDate], "", "")

and sort Descending?

"Jennie" wrote:

I have the following expression in my query:

Recent: Max(IIf([Current]=[PreviousDate]," ",""))

When I switch the "" and the " " it works perfectly but when I have the
Truepart set as " " all records have the "". I tried substituting " " with
another character and it works. I also tried using "" (no space) and it still
didn't work.

If you need a little background, this database tracks the 91 utility bills
we receive each month. Not all of the accounts are billed monthly, I want the
"" to appear next to the accounts that did not receive a bill for the
current period. Current is formatted as mm/yyyy and is set as month and year
of the most recent bill received for each account. PreviousDate is also
formatted as mm/yyyy and is set as the previous month and year (For example,
we receive May's bills in June, so if I were to run the report today then
PreviousDate would be 05/2009). If the two fields do not match, meaning no
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?

Jennie,
Do you need to worry about just getting the Max of Current? Or both
Current and PreviousDate?

if only for current, try this:

Recent: IIf(Max([Current])=[PreviousDate]," ","")


You can't have the Max() outside of the iif, it is simply taking the
Maximum of the true/false values and thats why you get "" for
everything.

If the above doesnt work, you could try turning on the Totals for the
query, and setting the total function to Max on the [Current] field.

Then in another query field do:
iif([MaxOfCurrent] = [PreviousDate], " " , "")

What action(s) are you wanting from this operation? Do you want to sort or
eliminate records?

Post your complete SQL of the query. Open in design view, click on VIEW -
SQL View, highlight all, copy, and paste in a post.

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



"KARL DEWEY" wrote:

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.

"Jennie" wrote:

Karl,

I use Max because the report shows all bills ever received for every
account. I only want to see if the most current billed date matches the
PreviousDate field, not all of the bills.

"KARL DEWEY" wrote:

I do not understand your use of Max here.

Why not use it this way --
Recent: IIf([Current]=[PreviousDate], "", "")

and sort Descending?

"Jennie" wrote:

I have the following expression in my query:

Recent: Max(IIf([Current]=[PreviousDate]," ",""))

When I switch the "" and the " " it works perfectly but when I have the
Truepart set as " " all records have the "". I tried substituting " " with
another character and it works. I also tried using "" (no space) and it still
didn't work.

If you need a little background, this database tracks the 91 utility bills
we receive each month. Not all of the accounts are billed monthly, I want the
"" to appear next to the accounts that did not receive a bill for the
current period. Current is formatted as mm/yyyy and is set as month and year
of the most recent bill received for each account. PreviousDate is also
formatted as mm/yyyy and is set as the previous month and year (For example,
we receive May's bills in June, so if I were to run the report today then
PreviousDate would be 05/2009). If the two fields do not match, meaning no
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?