Logic within a query

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

Guest

I have tried to include a conditional statement within a query but when I
save it, the query is split with the conditions being moved to a seperate
query field. What I want to do (within the query) is calculate a value based
on one field of the query and a calclulation.

Can this be done in a query?
 
I have tried the iif statement below but get syntax error (operand without
operator). Can s/one help?

Account_Due:IIf(IsNull([type]),Null,IIf([type]=AINL,14*8.689,IIf([type]=ENL,14*14,IIf([type]=ENPL,14*12,IIf([type]=RNL,14*15.2,IIf([type]=RNPL,14*13.529,
iif([type], “EN ASS†or “RN ASSâ€, 14*2.1155))))))))
 
Code is actually:
Account_Due:IIf(IsNull([type]),Null,IIf([type]=AINL,14*8.689,IIf([type]=ENL,14*14,IIf([type]=ENPL,14*12,IIf([type]=RNL,14*15.2,IIf([type]=RNPL,14*13.529,
IIf([type]= “EN ASS†or “RN ASSâ€, 14*2.1155))))))))

Michael Trotter said:
I have tried the iif statement below but get syntax error (operand without
operator). Can s/one help?

Account_Due:IIf(IsNull([type]),Null,IIf([type]=AINL,14*8.689,IIf([type]=ENL,14*14,IIf([type]=ENPL,14*12,IIf([type]=RNL,14*15.2,IIf([type]=RNPL,14*13.529,
iif([type], “EN ASS†or “RN ASSâ€, 14*2.1155))))))))

Michael Trotter said:
I have tried to include a conditional statement within a query but when I
save it, the query is split with the conditions being moved to a seperate
query field. What I want to do (within the query) is calculate a value based
on one field of the query and a calclulation.

Can this be done in a query?
Click to expand...
Click to expand...
 
Michael Trotter said:
I have tried the iif statement below but get syntax error (operand
without operator). Can s/one help?

Account_Due:IIf(IsNull([type]),Null,IIf([type]=AINL,14*8.689,IIf([type]=
ENL,14*14,IIf([type]=ENPL,14*12,IIf([type]=RNL,14*15.2,IIf([type]=RNPL,1
4*13.529,
iif([type], "EN ASS" or "RN ASS", 14*2.1155))))))))
Click to expand...

It looks as thought the strings AINL, ENL, ENPL, RNL, and RNPL should be
enclosed in quotes, plus you have no omparison operator in the last
IIf() expression. It might be simpler to use the Switch function to
clarify the logic:

Account_Due: Switch(
[type] Is Null, Null,
[type]="AINL", 14*8.689,
[type]="ENL", 14*14,
[type]="ENPL", 14*12,
[type]="RNL", 14*15.2,
[type]="RNPL", 14*13.529,
[type]="EN ASS" Or [type]="RN ASS", 14*2.1155
)

I've broken that onto multiple lines to make it easier to read, but
you'd have it all on one line in your query definition.
 
This works:

Account_Due: IIf(IsNull([TYPE]),Null,IIf([type]="AINL",(Now()-[paid
to])/14*8.689,IIf([type]="ENL",(Now()-[paid
to])/14*14,IIf([type]="ENPL",(Now()-[paid
to])/14*12,IIf([type]="RNL",(Now()-[paid
to])/14*15.2,IIf([type]="RNPL",(Now()-[paid to])/14*13.529,IIf([type]="EN
ASS",(Now()-[paid to])/14*12,IIf([type]="RN ASS",(Now()-[paid
to])/14*12))))))))

Dirk Goldgar said:
Michael Trotter said:
I have tried the iif statement below but get syntax error (operand
without operator). Can s/one help?

Account_Due:IIf(IsNull([type]),Null,IIf([type]=AINL,14*8.689,IIf([type]=
ENL,14*14,IIf([type]=ENPL,14*12,IIf([type]=RNL,14*15.2,IIf([type]=RNPL,1
4*13.529,
iif([type], "EN ASS" or "RN ASS", 14*2.1155))))))))
Click to expand...

It looks as thought the strings AINL, ENL, ENPL, RNL, and RNPL should be
enclosed in quotes, plus you have no omparison operator in the last
IIf() expression. It might be simpler to use the Switch function to
clarify the logic:

Account_Due: Switch(
[type] Is Null, Null,
[type]="AINL", 14*8.689,
[type]="ENL", 14*14,
[type]="ENPL", 14*12,
[type]="RNL", 14*15.2,
[type]="RNPL", 14*13.529,
[type]="EN ASS" Or [type]="RN ASS", 14*2.1155
)

I've broken that onto multiple lines to make it easier to read, but
you'd have it all on one line in your query definition.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
The problem is in the inner-most IIf:

Iif([type], "EN ASS" or "RN ASS", 14*2.1155)

1. The comma after [type] looks wrong. In addition, you need to repeat the
Field name if you use criteria with logical Or like:

([Type] = "EN ASS") Or ([Type] = "RN ASS")

2. Note that you need exactly 3 arguments in Iif function. Since the first
comma in the inner-most Iif looks wrong, I am not sure whether you have all
3 arguments there.

3. Personally, I would use Switch function per Dirk's advise or a UDF where
you can use a LookUp Table for the constants used in your expression. The
advantage of the LookUp Table is that if you need to change the constants'
values, you only need to change the Table Field values and not the
expression or the UDF.

--
HTH
Van T. Dinh
MVP (Access)



Michael Trotter said:
I have tried the iif statement below but get syntax error (operand without
operator). Can s/one help?

Account_Due:IIf(IsNull([type]),Null,IIf([type]=AINL,14*8.689,IIf([type]=ENL,14*14,IIf([type]=ENPL,14*12,IIf([type]=RNL,14*15.2,IIf([type]=RNPL,14*13.529,
iif([type], "EN ASS" or "RN ASS", 14*2.1155))))))))

Michael Trotter said:
I have tried to include a conditional statement within a query but when I
save it, the query is split with the conditions being moved to a seperate
query field. What I want to do (within the query) is calculate a value
based
on one field of the query and a calclulation.

Can this be done in a query?
Click to expand...
Click to expand...
 
I split the 'or' section and it all worked OK. I have in fact changed it to
the switch function in any case.

Thanks to one and all

Van T. Dinh said:
The problem is in the inner-most IIf:

Iif([type], "EN ASS" or "RN ASS", 14*2.1155)

1. The comma after [type] looks wrong. In addition, you need to repeat the
Field name if you use criteria with logical Or like:

([Type] = "EN ASS") Or ([Type] = "RN ASS")

2. Note that you need exactly 3 arguments in Iif function. Since the first
comma in the inner-most Iif looks wrong, I am not sure whether you have all
3 arguments there.

3. Personally, I would use Switch function per Dirk's advise or a UDF where
you can use a LookUp Table for the constants used in your expression. The
advantage of the LookUp Table is that if you need to change the constants'
values, you only need to change the Table Field values and not the
expression or the UDF.

--
HTH
Van T. Dinh
MVP (Access)



Michael Trotter said:
I have tried the iif statement below but get syntax error (operand without
operator). Can s/one help?

Account_Due:IIf(IsNull([type]),Null,IIf([type]=AINL,14*8.689,IIf([type]=ENL,14*14,IIf([type]=ENPL,14*12,IIf([type]=RNL,14*15.2,IIf([type]=RNPL,14*13.529,
iif([type], "EN ASS" or "RN ASS", 14*2.1155))))))))

Michael Trotter said:
I have tried to include a conditional statement within a query but when I
save it, the query is split with the conditions being moved to a seperate
query field. What I want to do (within the query) is calculate a value
based
on one field of the query and a calclulation.

Can this be done in a query?
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

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

Similar Threads

Parameter Value error in a Subform 0
Form question: Simplest way to bring in a query result that is notthe record source into a form? 3
my query does not update table 2
Multiple iif challenge 2
Using "like" query from and external link to Access 1
Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
"Read Only" Query 0
multiple counts, same calculated field, different parameters 6

Back
Top