IIF statement

H

Hank

I have the following Expression statement in a query:

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"Currency"),"$0.00")
The statement run fine and I get what I am asking for.

Now I have tried to change it to read:
PastDue:
IIf([DueDate]<Date(),Format(25,0)-Nz([AmtPaid],0),"Currency"),"$0.00")
This just removes adding a Fee (FeeDue) to any past due amount.

What I am trying to say is -- If today's date is after the due date
(DueDate) then subtract the amount paid (AmtPaid) form $25.00 and format as
currency and show $0.00 if there is no past due amount.

I keep getting syntax errors. What am I doing wrong?

Question #2: Rather than show $0.00, how can I just leave a blank space or
say "None" if there is not a past due amount? I have tried changing the
"$0.00" to "None" but it does not work.

Thanks for all your help
Hank
 
J

June7

Is this expression in a ControlSource for a textbox on a report? I don't see
anything wrong with the original syntax. 'None' or "" or Null in place of
'$0.00' should work.

Your revised expression has improperly placed ,0 and should be:
IIf([DueDate] < Date(), Format(25 - Nz([AmtPaid], 0), "Currency"), "")
 
K

KARL DEWEY

Or this --
PastDue: IIf([DueDate]<Date(),Format(25-Nz([AmtPaid],0),"Currency"),"$0.00")

--
Build a little, test a little.


June7 said:
Is this expression in a ControlSource for a textbox on a report? I don't see
anything wrong with the original syntax. 'None' or "" or Null in place of
'$0.00' should work.

Your revised expression has improperly placed ,0 and should be:
IIf([DueDate] < Date(), Format(25 - Nz([AmtPaid], 0), "Currency"), "")

Hank said:
I have the following Expression statement in a query:

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"Currency"),"$0.00")
The statement run fine and I get what I am asking for.

Now I have tried to change it to read:
PastDue:
IIf([DueDate]<Date(),Format(25,0)-Nz([AmtPaid],0),"Currency"),"$0.00")
This just removes adding a Fee (FeeDue) to any past due amount.

What I am trying to say is -- If today's date is after the due date
(DueDate) then subtract the amount paid (AmtPaid) form $25.00 and format as
currency and show $0.00 if there is no past due amount.

I keep getting syntax errors. What am I doing wrong?

Question #2: Rather than show $0.00, how can I just leave a blank space or
say "None" if there is not a past due amount? I have tried changing the
"$0.00" to "None" but it does not work.

Thanks for all your help
Hank
 
H

Hank

Thanks to both of you. It is working normally now.

Still not able to sove the "None" or Null problem. Actually the #Error
occurs in the sum text box. =Sum([PastDue]) is what I use to get a total of
the past due payemnts. If I have "None" or " " when there is a zero amount,
it seems to have a problem adding. In other words if I have a $5.00 past
due from one installment and $0.00 in the next installement it sums to
$5.00. But if I use "None" or " " I get #Erron in the =Sum([PastDue]) text
box.

Hank
 
D

Duane Hookom

I don't think you should be doing this calculation in an expression in a
query. What happens when you want to change the 25 to 30?

I would create a small user-defined function in a standard module named
"modBusinessCalcs". This provides greater functionality an the ability to add
comments.

--
Duane Hookom
Microsoft Access MVP


Hank said:
Thanks to both of you. It is working normally now.

Still not able to sove the "None" or Null problem. Actually the #Error
occurs in the sum text box. =Sum([PastDue]) is what I use to get a total of
the past due payemnts. If I have "None" or " " when there is a zero amount,
it seems to have a problem adding. In other words if I have a $5.00 past
due from one installment and $0.00 in the next installement it sums to
$5.00. But if I use "None" or " " I get #Erron in the =Sum([PastDue]) text
box.

Hank

Hank said:
I have the following Expression statement in a query:

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"Currency"),"$0.00")
The statement run fine and I get what I am asking for.

Now I have tried to change it to read:
PastDue:
IIf([DueDate]<Date(),Format(25,0)-Nz([AmtPaid],0),"Currency"),"$0.00")
This just removes adding a Fee (FeeDue) to any past due amount.

What I am trying to say is -- If today's date is after the due date
(DueDate) then subtract the amount paid (AmtPaid) form $25.00 and format
as currency and show $0.00 if there is no past due amount.

I keep getting syntax errors. What am I doing wrong?

Question #2: Rather than show $0.00, how can I just leave a blank space
or say "None" if there is not a past due amount? I have tried changing
the "$0.00" to "None" but it does not work.

Thanks for all your help
Hank
 
H

Hank

Thanks Duane,

I am not very experienced in ms access but would like to know more about
this.
I did a google search on "modBusinesCalcs" and guess what -- your name
appears on every hit that I read - so you must be the expert.

Where can I learn more and possibly see some examples of user defined
functions?

To answer your queston, if I have to change from 25 to 30 I would have to
change the code. All for learning a better way.

Thanks again
Hank

Duane Hookom said:
I don't think you should be doing this calculation in an expression in a
query. What happens when you want to change the 25 to 30?

I would create a small user-defined function in a standard module named
"modBusinessCalcs". This provides greater functionality an the ability to
add
comments.

--
Duane Hookom
Microsoft Access MVP


Hank said:
Thanks to both of you. It is working normally now.

Still not able to sove the "None" or Null problem. Actually the #Error
occurs in the sum text box. =Sum([PastDue]) is what I use to get a total
of
the past due payemnts. If I have "None" or " " when there is a zero
amount,
it seems to have a problem adding. In other words if I have a $5.00
past
due from one installment and $0.00 in the next installement it sums to
$5.00. But if I use "None" or " " I get #Erron in the =Sum([PastDue])
text
box.

Hank

Hank said:
I have the following Expression statement in a query:

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"Currency"),"$0.00")
The statement run fine and I get what I am asking for.

Now I have tried to change it to read:
PastDue:
IIf([DueDate]<Date(),Format(25,0)-Nz([AmtPaid],0),"Currency"),"$0.00")
This just removes adding a Fee (FeeDue) to any past due amount.

What I am trying to say is -- If today's date is after the due date
(DueDate) then subtract the amount paid (AmtPaid) form $25.00 and
format
as currency and show $0.00 if there is no past due amount.

I keep getting syntax errors. What am I doing wrong?

Question #2: Rather than show $0.00, how can I just leave a blank
space
or say "None" if there is not a past due amount? I have tried
changing
the "$0.00" to "None" but it does not work.

Thanks for all your help
Hank
 
D

Duane Hookom

I'm not sure what your calculation would be. One issue I think I see is
returning a string/text value when you should only be returning a number or
null.

However, assuming you want to give a discount of 5% on orders over $1,000.
You might add a column in your query like:
Discount: IIf([SaleAmt] > 1000, [SaleAmt] * 0.05, 0)
I would expect either the threshold amount or the discount percent to change
over time. I would hate to have to remember every where I used this
calculation. So, create a module of business calcs with a function like:

Public Function GetDiscount(curAmount as Currency) as Currency
Dim dblDiscount as Double
Dim dblNoDiscount as Double
Dim curThreshold as Currency
dblDiscount = 0.05
dblNoDiscount = 0.0
curThreshold = 1000
'====== no hard-coded numbers below this line =========
If curAmount > curThreshold Then
GetDiscount = curAmount * dblDiscount
Else
GetDiscount = dblNoDiscount
End If
End Function

You could further refine this by sending in the discount percent as an
optional argument. I would go even further and create a table of amounts
with a related percent. The function could then look up the amount in the
table and grab the appropriate percent. Using a table would actually allow
for elimination of the function altogether but that is another thread ;-)

--
Duane Hookom
Microsoft Access MVP


Hank said:
Thanks Duane,

I am not very experienced in ms access but would like to know more about
this.
I did a google search on "modBusinesCalcs" and guess what -- your name
appears on every hit that I read - so you must be the expert.

Where can I learn more and possibly see some examples of user defined
functions?

To answer your queston, if I have to change from 25 to 30 I would have to
change the code. All for learning a better way.

Thanks again
Hank

Duane Hookom said:
I don't think you should be doing this calculation in an expression in a
query. What happens when you want to change the 25 to 30?

I would create a small user-defined function in a standard module named
"modBusinessCalcs". This provides greater functionality an the ability to
add
comments.

--
Duane Hookom
Microsoft Access MVP


Hank said:
Thanks to both of you. It is working normally now.

Still not able to sove the "None" or Null problem. Actually the #Error
occurs in the sum text box. =Sum([PastDue]) is what I use to get a total
of
the past due payemnts. If I have "None" or " " when there is a zero
amount,
it seems to have a problem adding. In other words if I have a $5.00
past
due from one installment and $0.00 in the next installement it sums to
$5.00. But if I use "None" or " " I get #Erron in the =Sum([PastDue])
text
box.

Hank

I have the following Expression statement in a query:

PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"Currency"),"$0.00")
The statement run fine and I get what I am asking for.

Now I have tried to change it to read:
PastDue:
IIf([DueDate]<Date(),Format(25,0)-Nz([AmtPaid],0),"Currency"),"$0.00")
This just removes adding a Fee (FeeDue) to any past due amount.

What I am trying to say is -- If today's date is after the due date
(DueDate) then subtract the amount paid (AmtPaid) form $25.00 and
format
as currency and show $0.00 if there is no past due amount.

I keep getting syntax errors. What am I doing wrong?

Question #2: Rather than show $0.00, how can I just leave a blank
space
or say "None" if there is not a past due amount? I have tried
changing
the "$0.00" to "None" but it does not work.

Thanks for all your help
Hank
 

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