Query Question

[Jennie]

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]

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

 

[KARL DEWEY]

I do not understand your use of Max here.

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

and sort Descending?

 

[ghetto_banjo]

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

 

[Jennie]

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.

I do not understand your use of Max here.

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

and sort Descending?

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]

Dorian,

I tried that and I am still having the same problem. I have no idea what I
am 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".

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 DEWEY]

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.

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.

I do not understand your use of Max here.

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

and sort Descending?

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]

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.

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.

I do not understand your use of Max here.

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

and sort Descending?

:

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?

 

[ghetto_banjo]

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], " " , ">")

 

[KARL DEWEY]

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.

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.

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.

:

I do not understand your use of Max here.

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

and sort Descending?

:

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?