Query Question

J

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?
 
D

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

KARL DEWEY

I do not understand your use of Max here.

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

and sort Descending?
 
J

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.

KARL DEWEY said:
I do not understand your use of Max here.

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

and sort Descending?

Jennie said:
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?
 
J

Jennie

Dorian,

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

Dorian said:
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 said:
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?
 
K

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.

Jennie said:
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 said:
I do not understand your use of Max here.

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

and sort Descending?

Jennie said:
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?
 
J

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.



KARL DEWEY said:
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 said:
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 said:
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?
 
G

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

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.

Jennie said:
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 said:
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 said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top