IIF expresion

[Merge]

I have the following IIF statement, I am getting an err message about my
parenthsis placement before I make a bigger mess can someone reveiw please

Thank in advance

IIf([payment terms]>=91),[91+],(or[payment
terms]>=61),[61-90]+[91+]),(or[payment
terms]>=46),[46-60]+[61-90]+[91+]),(or[payment
terms]>=31),[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=16),[15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=11),[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=6),[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]<5),[0-5]+[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+],0)

 

[John W. Vinson]

I have the following IIF statement, I am getting an err message about my
parenthsis placement before I make a bigger mess can someone reveiw please

Thank in advance

IIf([payment terms]>=91),[91+],(or[payment
terms]>=61),[61-90]+[91+]),(or[payment
terms]>=46),[46-60]+[61-90]+[91+]),(or[payment
terms]>=31),[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=16),[15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=11),[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=6),[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]<5),[0-5]+[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+],0)

Ummm.... yes. That's certainly erroneous.

IIF takes three arguments: an expression which evaluates to TRUE or FALSE; a
value to return if it's TRUE; and a value to return if it's FALSE.

Your or[ syntax has no resemblance to anything in the documentation.

*GUESSING* at what you want, I'd suggest using the Switch() function instead.
It takes arguments in pairs; evaluates them left to right; if the first
element of a pair is true it returns the other member of that pair and quits.
So:

Switch([Payment terms] => 91, [91+], [Payment terms] >= 61, [61-90] + [91+],
<etc. etc.>, False, 0)

I must say that if you have *table fields* named 91+, 61-90 and so on, your
table structure is badly misdesigned; and also that I've had some real
problems with fieldnames containing special characters such as + and -.

 

[Jerry Whittle]

Ouch! My brain just exploded. Please don't tell me that [0-5], [6-10],
[11-14], [15-30], [31-45], [46-60], [61-90], and [91+] are fields in your
table! If so you have a serious problem with your table structure.

Oh well.... Below mat be the correct syntax and might even work.

IIf([payment terms]>=91,[91+],
IIf([payment terms]>=61,[61-90]+[91+],
IIf([payment terms]>=46,[46-60]+[61-90]+[91+],
IIf([payment terms]>=31,[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=16,[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=11,[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=6,[6-10]+[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment
terms]<5,[0-5]+[6-10]+[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],0))))))))

 

[Merge]

Oy, well I am just using the tables/columns from my aging and creating a
calcuation to show all the accounts that have past due amounts based upon
each clients agreed upon payment terms

What is wrong with the table structure?

Ouch! My brain just exploded. Please don't tell me that [0-5], [6-10],
[11-14], [15-30], [31-45], [46-60], [61-90], and [91+] are fields in your
table! If so you have a serious problem with your table structure.

Oh well.... Below mat be the correct syntax and might even work.

IIf([payment terms]>=91,[91+],
IIf([payment terms]>=61,[61-90]+[91+],
IIf([payment terms]>=46,[46-60]+[61-90]+[91+],
IIf([payment terms]>=31,[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=16,[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=11,[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=6,[6-10]+[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment
terms]<5,[0-5]+[6-10]+[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],0))))))))

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following IIF statement, I am getting an err message about my
parenthsis placement before I make a bigger mess can someone reveiw please

Thank in advance

IIf([payment terms]>=91),[91+],(or[payment
terms]>=61),[61-90]+[91+]),(or[payment
terms]>=46),[46-60]+[61-90]+[91+]),(or[payment
terms]>=31),[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=16),[15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=11),[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=6),[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]<5),[0-5]+[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+],0)

 

[Jerry Whittle]

Anytime that you go across with the fields, especially when the field names
have "meaning", you are treating Access like a spreadsheet, which is is not.

Ponder this: Someone decides that 91-120 needs to be a term. What happens to
all your queries, forms and reports? Many will probably need at least a
little revision.

What you need is a PaymentTerms table that may look something like this:

Term Payment
0 1
6 2
11 3

and so on. Then if someone's term is 11, you would pick out the payment for
it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Oy, well I am just using the tables/columns from my aging and creating a
calcuation to show all the accounts that have past due amounts based upon
each clients agreed upon payment terms

What is wrong with the table structure?

Ouch! My brain just exploded. Please don't tell me that [0-5], [6-10],
[11-14], [15-30], [31-45], [46-60], [61-90], and [91+] are fields in your
table! If so you have a serious problem with your table structure.

Oh well.... Below mat be the correct syntax and might even work.

IIf([payment terms]>=91,[91+],
IIf([payment terms]>=61,[61-90]+[91+],
IIf([payment terms]>=46,[46-60]+[61-90]+[91+],
IIf([payment terms]>=31,[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=16,[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=11,[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=6,[6-10]+[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment
terms]<5,[0-5]+[6-10]+[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],0))))))))

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following IIF statement, I am getting an err message about my
parenthsis placement before I make a bigger mess can someone reveiw please

Thank in advance

IIf([payment terms]>=91),[91+],(or[payment
terms]>=61),[61-90]+[91+]),(or[payment
terms]>=46),[46-60]+[61-90]+[91+]),(or[payment
terms]>=31),[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=16),[15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=11),[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=6),[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]<5),[0-5]+[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+],0)

 

[John W. Vinson]

What is wrong with the table structure?

For one thing, the fact that *every single value in each of these fields* will
be wrong within a few days.

If you store something in field [0-5] meaning that it's zero to five days old,
that value will be incorrect and inappropriate five days from now at the very
best.

You're using a relational database, not a spreadsheet!!!! The age should be
dynamically calculated on the fly, using the date of sale, today's date, and
the DateDiff function - not used as a fieldname!

 

[Merge]

Great point Yes I have been looking at it as a spread sheet
Thanks

Anytime that you go across with the fields, especially when the field names
have "meaning", you are treating Access like a spreadsheet, which is is not.

Ponder this: Someone decides that 91-120 needs to be a term. What happens to
all your queries, forms and reports? Many will probably need at least a
little revision.

What you need is a PaymentTerms table that may look something like this:

Term Payment
0 1
6 2
11 3

and so on. Then if someone's term is 11, you would pick out the payment for
it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Oy, well I am just using the tables/columns from my aging and creating a
calcuation to show all the accounts that have past due amounts based upon
each clients agreed upon payment terms

What is wrong with the table structure?

Ouch! My brain just exploded. Please don't tell me that [0-5], [6-10],
[11-14], [15-30], [31-45], [46-60], [61-90], and [91+] are fields in your
table! If so you have a serious problem with your table structure.

Oh well.... Below mat be the correct syntax and might even work.

IIf([payment terms]>=91,[91+],
IIf([payment terms]>=61,[61-90]+[91+],
IIf([payment terms]>=46,[46-60]+[61-90]+[91+],
IIf([payment terms]>=31,[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=16,[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=11,[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment terms]>=6,[6-10]+[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],
IIf([payment
terms]<5,[0-5]+[6-10]+[11-14]+[15-30]+[31-45]+[46-60]+[61-90]+[91+],0))))))))

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have the following IIF statement, I am getting an err message about my
parenthsis placement before I make a bigger mess can someone reveiw please

Thank in advance

IIf([payment terms]>=91),[91+],(or[payment
terms]>=61),[61-90]+[91+]),(or[payment
terms]>=46),[46-60]+[61-90]+[91+]),(or[payment
terms]>=31),[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=16),[15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=11),[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]>=6),[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+]),(or[payment
terms]<5),[0-5]+[6-10]+[11-14]+ [15-30]+[31-45]+[46-60]+[61-90]+[91+],0)