IIf Problem

[Ciggys]

Hi all

Just been for a surf around the net looking for answer to an issue i am
currently having with a formula in a query in access
I am trying to get the gst factor of a product using a YES/NO combo box has
the control sauce.
The sum i have in the query looks like this
IIf([Forms]![Invoice]![GSTComboBox
YES/NO]="Yes"[Forms]![Invoice]![InvoiceCost]/(1.1),"")
But i keep getting a error.

Anyone here have any idea????

Thanks in advance [ Edit Your Post ]

 

[John Spencer]

As posted, you are missing a comma after
[Forms]![Invoice]![GSTComboBox YES/NO]="Yes"

Also I would return Null instead of a zero-length string (ZLS). If you return
a ZLS, then the IIf will typecast the result of the calculation as a string.

IIf([Forms]![Invoice]![GSTComboBox YES/NO]="Yes",
[Forms]![Invoice]![InvoiceCost]/(1.1),Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

What error are you getting? I see potential problems, but without knowing the
error number or message, I can only guess.

1. If the combo box is based on a Yes/No data type, the "Yes" should not
have quotation marks around it. In fact -1 might work better.

2. There isn't a comma between "Yes" and [Forms].

3. A Null would probably be better that an empty string.

4. The Invoice form must remain open.

5. You might need to use the .Text or .Value property of the Yes/No field.

 

[Ciggys]

Hi Jerry

Thanks for you help and sorry i haven't gotten back sooner. I have change
the way i have done the sum
IIf([GST]="YES",[InvoiceCost]/(1.1)) Or IIf([GST]="No",[GSTTotal],Null)

But this is still not working. Any help would be appreciated

What error are you getting? I see potential problems, but without knowing the
error number or message, I can only guess.

1. If the combo box is based on a Yes/No data type, the "Yes" should not
have quotation marks around it. In fact -1 might work better.

2. There isn't a comma between "Yes" and [Forms].

3. A Null would probably be better that an empty string.

4. The Invoice form must remain open.

5. You might need to use the .Text or .Value property of the Yes/No field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi all

Just been for a surf around the net looking for answer to an issue i am
currently having with a formula in a query in access
I am trying to get the gst factor of a product using a YES/NO combo box has
the control sauce.
The sum i have in the query looks like this
IIf([Forms]![Invoice]![GSTComboBox
YES/NO]="Yes"[Forms]![Invoice]![InvoiceCost]/(1.1),"")
But i keep getting a error.

Anyone here have any idea????

Thanks in advance [ Edit Your Post ]

 

[John Spencer]

IIf([GST]="YES",[InvoiceCost]/1.1, IIf([GST]="No",[GSTTotal],Null))



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi Jerry

Thanks for you help and sorry i haven't gotten back sooner. I have change
the way i have done the sum
IIf([GST]="YES",[InvoiceCost]/(1.1)) Or IIf([GST]="No",[GSTTotal],Null)

But this is still not working. Any help would be appreciated

What error are you getting? I see potential problems, but without knowing the
error number or message, I can only guess.

1. If the combo box is based on a Yes/No data type, the "Yes" should not
have quotation marks around it. In fact -1 might work better.

2. There isn't a comma between "Yes" and [Forms].

3. A Null would probably be better that an empty string.

4. The Invoice form must remain open.

5. You might need to use the .Text or .Value property of the Yes/No field.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi all

Just been for a surf around the net looking for answer to an issue i am
currently having with a formula in a query in access
I am trying to get the gst factor of a product using a YES/NO combo box has
the control sauce.
The sum i have in the query looks like this
IIf([Forms]![Invoice]![GSTComboBox
YES/NO]="Yes"[Forms]![Invoice]![InvoiceCost]/(1.1),"")
But i keep getting a error.

Anyone here have any idea????

Thanks in advance [ Edit Your Post ]

 

[shganduri]

Hi Ciggys

I have struggled myself with "Yes" and "No" with inverted commas or without.
It never worked. But then i switched on to True and False and it started
working. Just try this. You may succeed just as I did.

Hi Jerry

Thanks for you help and sorry i haven't gotten back sooner. I have change
the way i have done the sum
IIf([GST]="YES",[InvoiceCost]/(1.1)) Or IIf([GST]="No",[GSTTotal],Null)

But this is still not working. Any help would be appreciated

What error are you getting? I see potential problems, but without knowing the
error number or message, I can only guess.

[quoted text clipped - 24 lines]

Thanks in advance [ Edit Your Post ]

 

[Ciggys]

Thanks heaps for everyones help but for the life of me i cant get it to work
and i have tried everything i can think of.
I am sure its just me and just wonted to to thank everyone for there time
and effort, will build the rest of the data base and come back to this
problem later.

Cheers guys

Hi Ciggys

I have struggled myself with "Yes" and "No" with inverted commas or without.
It never worked. But then i switched on to True and False and it started
working. Just try this. You may succeed just as I did.

Hi Jerry

Thanks for you help and sorry i haven't gotten back sooner. I have change
the way i have done the sum
IIf([GST]="YES",[InvoiceCost]/(1.1)) Or IIf([GST]="No",[GSTTotal],Null)

But this is still not working. Any help would be appreciated

What error are you getting? I see potential problems, but without knowing the
error number or message, I can only guess.

[quoted text clipped - 24 lines]

Thanks in advance [ Edit Your Post ]