Logic within a query

M

Michael Trotter

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?
 
M

Michael Trotter

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))))))))
 
M

Michael Trotter

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...
 
D

Dirk Goldgar

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.
 
M

Michael Trotter

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...
 
V

Van T. Dinh

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...
 
M

Michael Trotter

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...
 
Top