IIF question getting #ERROR Message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2002
I have an IIF statement in my query
"DGPSN: IIf([QuickBook]=6,55,50)
When I run this in my forms it works fine, but in the query I get "#Error"
Don't know why it won't work.
Any help would be appreciated.
Thanks Paul
 
I'm using Access 2002
I have an IIF statement in my query
"DGPSN: IIf([QuickBook]=6,55,50)
When I run this in my forms it works fine, but in the query I get "#Error"
Don't know why it won't work.
Any help would be appreciated.
Thanks Paul
Click to expand...

Please open the Query in SQL view and post it here. Is there in fact a
field named QuickBook in the table upon which the query is based? It
might work on the Form because there's a Control of that name.

John W. Vinson[MVP]
 
Here's the SQL view: SELECT JobCode.Code, [Basic Estimator].QuickBook,
[Basic Estimator].REP, IIf([QuickBook]=6,55,50) AS REPL,
(([REP]*[PercentIncrease]+[REP])*[REPL]) AS REPIH
FROM (JobCode INNER JOIN [Basic Estimator] ON JobCode.ID = [Basic
Estimator].BID) INNER JOIN [Client Info] ON JobCode.ID = [Client Info].CIID
WHERE (((JobCode.Code)="waip d"))
ORDER BY JobCode.Code;


Thanks again Paul

John Vinson said:
I'm using Access 2002
I have an IIF statement in my query
"DGPSN: IIf([QuickBook]=6,55,50)
When I run this in my forms it works fine, but in the query I get "#Error"
Don't know why it won't work.
Any help would be appreciated.
Thanks Paul
Click to expand...

Please open the Query in SQL view and post it here. Is there in fact a
field named QuickBook in the table upon which the query is based? It
might work on the Form because there's a Control of that name.

John W. Vinson[MVP]
Click to expand...
 
Here's the SQL view:
Click to expand...

Hmm. Not sure why that wouldn't work! What field shows up as #Error?
Does it make any difference if you use

Here's the SQL view: SELECT JobCode.Code, [Basic
Estimator].QuickBook,
[Basic Estimator].REP, IIf([QuickBook]=6,55,50) AS REPL,
(([REP]*[PercentIncrease]+[REP])*IIf([QuickBook]=6,55,50)) AS REPIH
FROM (JobCode INNER JOIN [Basic Estimator] ON JobCode.ID = [Basic
Estimator].BID) INNER JOIN [Client Info] ON JobCode.ID = [Client
Info].CIID
WHERE (((JobCode.Code)="waip d"))
ORDER BY JobCode.Code;

Sometimes you can't use a calculated field in another calculation!


John W. Vinson[MVP]
 
Well I tried the new code you wrote but got the same #Error on the REPL and
REPIH fields. Thanks again for the help

John Vinson said:
Here's the SQL view:
Click to expand...

Hmm. Not sure why that wouldn't work! What field shows up as #Error?
Does it make any difference if you use

Here's the SQL view: SELECT JobCode.Code, [Basic
Estimator].QuickBook,
[Basic Estimator].REP, IIf([QuickBook]=6,55,50) AS REPL,
(([REP]*[PercentIncrease]+[REP])*IIf([QuickBook]=6,55,50)) AS REPIH
FROM (JobCode INNER JOIN [Basic Estimator] ON JobCode.ID = [Basic
Estimator].BID) INNER JOIN [Client Info] ON JobCode.ID = [Client
Info].CIID
WHERE (((JobCode.Code)="waip d"))
ORDER BY JobCode.Code;

Sometimes you can't use a calculated field in another calculation!


John W. Vinson[MVP]
Click to expand...
 
Well I tried the new code you wrote but got the same #Error on the REPL and
REPIH fields. Thanks again for the help
Click to expand...

What are the datatypes of the fields [QUickbook], [Rep] and
[PercentIncrease]?
John Vinson said:
Here's the SQL view:
Click to expand...

Hmm. Not sure why that wouldn't work! What field shows up as #Error?
Does it make any difference if you use

Here's the SQL view: SELECT JobCode.Code, [Basic
Estimator].QuickBook,
[Basic Estimator].REP, IIf([QuickBook]=6,55,50) AS REPL,
(([REP]*[PercentIncrease]+[REP])*IIf([QuickBook]=6,55,50)) AS REPIH
FROM (JobCode INNER JOIN [Basic Estimator] ON JobCode.ID = [Basic
Estimator].BID) INNER JOIN [Client Info] ON JobCode.ID = [Client
Info].CIID
WHERE (((JobCode.Code)="waip d"))
ORDER BY JobCode.Code;

Sometimes you can't use a calculated field in another calculation!


John W. Vinson[MVP]
Click to expand...
Click to expand...


John W. Vinson[MVP]
 
I checked the type of field for Quickbook and PercentIncrease and they were
text fields, once I changed them to number fields the calculation worked
great. Thanks for all the help!

John Vinson said:
Well I tried the new code you wrote but got the same #Error on the REPL and
REPIH fields. Thanks again for the help
Click to expand...

What are the datatypes of the fields [QUickbook], [Rep] and
[PercentIncrease]?
John Vinson said:
On Tue, 31 Jan 2006 15:31:27 -0800, pas926

Here's the SQL view:

Hmm. Not sure why that wouldn't work! What field shows up as #Error?
Does it make any difference if you use

Here's the SQL view: SELECT JobCode.Code, [Basic
Estimator].QuickBook,
[Basic Estimator].REP, IIf([QuickBook]=6,55,50) AS REPL,
(([REP]*[PercentIncrease]+[REP])*IIf([QuickBook]=6,55,50)) AS REPIH
FROM (JobCode INNER JOIN [Basic Estimator] ON JobCode.ID = [Basic
Estimator].BID) INNER JOIN [Client Info] ON JobCode.ID = [Client
Info].CIID
WHERE (((JobCode.Code)="waip d"))
ORDER BY JobCode.Code;

Sometimes you can't use a calculated field in another calculation!


John W. Vinson[MVP]
Click to expand...
Click to expand...


John W. Vinson[MVP]
Click to expand...
 
Back
Top