It was working perfect

[Guest]

Okay, I have a query that works perfect. I have two tables, one for changes
and one for markups. The expressions added will multiply the value of the
change times the value of the markup Bond:
(([Amount]+[General]+[INS])*[Jobsites]![Bond-Cont]). I've even made
arrangements for changes when they change the heading in the report.

Now, they throw this at me. I need help to write an IIf statement that
basically states that if the value of Cont-Bond = No then
(([Amount]+[General]+[INS])*[Jobsites]![Bond-Cont]), if Yes then
([Amount*[Jobsites]![Bond-Cont]) but my IIf statement isn't working right.
This changes the formula for this field as well as another. The second field
states JSCG: (([Amount]+[General]+[INS]+[BOND])*Jobsites!Fee) and this
changes; if the value of Cont-Bond = No then
(([Amount]+[General]+[INS]+[BOND])*Jobsites!Fee), if Yes then
(([Amount]+[General]+[INS]*Jobsites!Fee).

I can't get the expressions written to where they will do what I need. HELP!

 

[Guest]

This is what I need to get done with the first expression:
Bond:
IIf([Jobsites]![Cont-Bond]="No",(([Amount]+[General]+[INS])*[Jobsites]![Bond-Cont]),([Amount]*[Jobsites]![Bond-Cont]))
And the second expression:
JSCG:
IIf([Jobsites]![Cont-Bond]="No",(([Amount]+[General]+[INS]+[BOND])*[Jobsites]![Fee]),(([Amount]+[General]+[INS])*[Jobsites]![Fee]))
both of these come back with errors, which naturally creates an error on my
TOTAL field.

 

[John Vinson]

both of these come back with errors, which naturally creates an error on my
TOTAL field.

Would you care to give us a bit of help here? What errors do you get?
"Doctor, I don't feel good - what should I take?" doesn't get you a
cure!

John W. Vinson[MVP]

 

[Guest]

It doesn't give me an error message stating that something is wrong, the
result in the field comes up as error. The original calculation of Bond:
(([Amount]+[General]+[INS])*[Jobsites]![Bond-Cont]) works perfectly, when I
add the condition of IIf([Jobsites]![Cont-Bond]="No" the result is #Error.

 

[John Vinson]

This is what I need to get done with the first expression:
Bond:
IIf([Jobsites]![Cont-Bond]="No",(([Amount]+[General]+[INS])*[Jobsites]![Bond-Cont]),([Amount]*[Jobsites]![Bond-Cont]))
And the second expression:
JSCG:
IIf([Jobsites]![Cont-Bond]="No",(([Amount]+[General]+[INS]+[BOND])*[Jobsites]![Fee]),(([Amount]+[General]+[INS])*[Jobsites]![Fee]))
both of these come back with errors, which naturally creates an error on my
TOTAL field.
--

What's the context? Is this on a Form, in a Query, or what? Is
Jobsites the name of a Table? Do you have fields named [Cont-Bond]
(text datatype) and [Bond-Cont] (numeric or currency)? Might any of
these fields be NULL?

And could you describe IN WORDS - rather than in a non-functional
expression - what data you have and what result you want?


John W. Vinson[MVP]

 

[Guest]

Okay, I have two tables. One with the jobsites, this contains the Project
Names, and Numbers, Addresses etc. along with the allowable markup on any
contract changes.
the second table is the scope changes, this provides a change number,
description and value along with the project number that is associated to the
specific group of letters. I created a query that provides information
pulled from both table. I then inserted fields to get the numbers I needed.
The Allowable Markup (in four separate areas) is multiplied times the value
of the change to come up with the proper value (example, markup for General
Conditions is 1%, the value of the change order is 100,000, therefore the
GENERAL field shows a value of 1000.) All basically simple. Since the markup
varies from project to project placing those fields on the Jobsite table
seemed the logical way to handle it. I used the query so that I could
incorporate these specific calculations and then a report is generated from
these results. The fields I'm having problems with are all in currency format
and while none of the fields should be null, I do have a few exceptions that
I'm not sure how to handle (I need a way to let the query do its thing but
maybe give me the option of changing this value).
--
Lori A. Pong

This is what I need to get done with the first expression:
Bond:
IIf([Jobsites]![Cont-Bond]="No",(([Amount]+[General]+[INS])*[Jobsites]![Bond-Cont]),([Amount]*[Jobsites]![Bond-Cont]))
And the second expression:
JSCG:
IIf([Jobsites]![Cont-Bond]="No",(([Amount]+[General]+[INS]+[BOND])*[Jobsites]![Fee]),(([Amount]+[General]+[INS])*[Jobsites]![Fee]))
both of these come back with errors, which naturally creates an error on my
TOTAL field.
--

What's the context? Is this on a Form, in a Query, or what? Is
Jobsites the name of a Table? Do you have fields named [Cont-Bond]
(text datatype) and [Bond-Cont] (numeric or currency)? Might any of
these fields be NULL?

And could you describe IN WORDS - rather than in a non-functional
expression - what data you have and what result you want?


John W. Vinson[MVP]

 

[Guest]

I forgot, the Cont-Bond field is a yes/no field. If this field is checked,
then I need the value of the change times the Bond/contingency field. If it
is not checked then I need the value of the chane, plus the value of the
General Conditions, plus the value of the Insurance, times the value of the
Bond/Contingency field.
--
Lori A. Pong

This is what I need to get done with the first expression:
Bond:
IIf([Jobsites]![Cont-Bond]="No",(([Amount]+[General]+[INS])*[Jobsites]![Bond-Cont]),([Amount]*[Jobsites]![Bond-Cont]))
And the second expression:
JSCG:
IIf([Jobsites]![Cont-Bond]="No",(([Amount]+[General]+[INS]+[BOND])*[Jobsites]![Fee]),(([Amount]+[General]+[INS])*[Jobsites]![Fee]))
both of these come back with errors, which naturally creates an error on my
TOTAL field.
--

What's the context? Is this on a Form, in a Query, or what? Is
Jobsites the name of a Table? Do you have fields named [Cont-Bond]
(text datatype) and [Bond-Cont] (numeric or currency)? Might any of
these fields be NULL?

And could you describe IN WORDS - rather than in a non-functional
expression - what data you have and what result you want?


John W. Vinson[MVP]

 

[John Vinson]

I forgot, the Cont-Bond field is a yes/no field.

That's the problem, then. A Yes/No field is NOT a text field; it's a
small integer, containing -1 for Yes and 0 for No. It will never be
equal to the text string "No" and comparing it to a text string will
give you an error.

Since the first argument of IIF is just looking for true or false
expressions anyway, you can just use the field directly:

IIf([Jobsites].[Cont-Bond],([Amount]*[Jobsites].[Bond-Cont]),(([Amount]+[General]+[INS])*[Jobsites].[Bond-Cont]))

IIf([Jobsites].[Cont-Bond],(([Amount]+[General]+[INS])*[Jobsites].[Fee]),(([Amount]+[General]+[INS]+[BOND])*[Jobsites].[Fee]))

Note that I reversed the order of the arguments so the YES response is
first.

I'd also replace the ! with . in this context - ! is used for
referring to (for example) a Control on a Form, not a field within a
table.

John W. Vinson[MVP]

 

[Guest]

Okay, with what you gave me I was able to make it work with a slight
modification. I tried pasting yours into the field and it gave me a syntax
error but Since you told me about the -1 (I looked at the field and it gave
me yes and no) but I modified my existing to eliminate the =Yes and changed
it to =-1 and it works perfectly.

Now, the second half of my question, is it possible to set this field up so
that I can (if necessary) modify the value? I need the default to be the
formula we worked on but there are ocassions when a value might be corrected
for the one occurence.
--
Lori A. Pong

I forgot, the Cont-Bond field is a yes/no field.

That's the problem, then. A Yes/No field is NOT a text field; it's a
small integer, containing -1 for Yes and 0 for No. It will never be
equal to the text string "No" and comparing it to a text string will
give you an error.

Since the first argument of IIF is just looking for true or false
expressions anyway, you can just use the field directly:

IIf([Jobsites].[Cont-Bond],([Amount]*[Jobsites].[Bond-Cont]),(([Amount]+[General]+[INS])*[Jobsites].[Bond-Cont]))

IIf([Jobsites].[Cont-Bond],(([Amount]+[General]+[INS])*[Jobsites].[Fee]),(([Amount]+[General]+[INS]+[BOND])*[Jobsites].[Fee]))

Note that I reversed the order of the arguments so the YES response is
first.

I'd also replace the ! with . in this context - ! is used for
referring to (for example) a Control on a Form, not a field within a
table.

John W. Vinson[MVP]

 

[John Vinson]

Now, the second half of my question, is it possible to set this field up so
that I can (if necessary) modify the value? I need the default to be the
formula we worked on but there are ocassions when a value might be corrected
for the one occurence.

You'll have to do it differently. I have no idea how your form is set
up, but you will need to have a field in your Table to contain this
value, and you'll need to set that field's value in VBA code -
probably in the AfterUpdate event of some other control.

John W. Vinson[MVP]