Criteria on calcualted field creates parameter prompt

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

Guest

Hi,

I have a calculated field using a nested IIf that basically creates either
the word "cataract" or an empty cell. It works fine but I want to add a
Criteria that returns ONLY the rows with Cataract. When I enetre the word
"Cataract" or Like "Cataract" it runs like a parameter prompt.

There's other threads here with similar problems that indicate I have to add
a "WHERE" clause to the critaria area, but I don't quite follow. The field is
generated using the followinf IIf:

Cataract: IIf([ICD_1] Between "C71" And "C76","Cataract",IIf([ICD_2] Between
"C71" And "C76","Cataract",IIf([ICD_3] Between "C71" And
"C76","Cataract",IIf([ICD_4] Between "C71" And "C76","Cataract",IIf([ICD_5]
Between "C71" And "C76","Cataract",IIf([ICD_6] Between "C71" And
"C76","Cataract",IIf([ICD_7] Between "C71" And "C76","Cataract","")

Any suggestions?
 
easy - it's a typo! copy & pasted but missed last brackets, I have seven
closing parenthesis.

Rick B said:
How do you have 7 opening parenthesis and only one closing?


--
Rick B



Matt D Francis said:
Hi,

I have a calculated field using a nested IIf that basically creates either
the word "cataract" or an empty cell. It works fine but I want to add a
Criteria that returns ONLY the rows with Cataract. When I enetre the word
"Cataract" or Like "Cataract" it runs like a parameter prompt.

There's other threads here with similar problems that indicate I have to add
a "WHERE" clause to the critaria area, but I don't quite follow. The field is
generated using the followinf IIf:

Cataract: IIf([ICD_1] Between "C71" And "C76","Cataract",IIf([ICD_2] Between
"C71" And "C76","Cataract",IIf([ICD_3] Between "C71" And
"C76","Cataract",IIf([ICD_4] Between "C71" And "C76","Cataract",IIf([ICD_5]
Between "C71" And "C76","Cataract",IIf([ICD_6] Between "C71" And
"C76","Cataract",IIf([ICD_7] Between "C71" And "C76","Cataract","")

Any suggestions?
Click to expand...
Click to expand...
 
I would use something like the statement below to get the value. Since you only
want records with the calculated value of cataract and that only happens when
the DX is in the range C71 to C76, you really don't need to calculate the value
at all.

SAMPLE QUERY

SELECT "Cataract" as Cataract, <List of other fields>
FROM YourTable
WHERE ICD_1 Like "C7[1-6]"
OR ICD_2 Like "C7[1-6]"
OR ICD_3 Like "C7[1-6]"
OR ICD_4 Like "C7[1-6]"
OR ICD_5 Like "C7[1-6]"
OR ICD_6 Like "C7[1-6]"

By the way, you really should be storing the ICD codes in a separate table along
with a id code to tie the DX to the individual. That way the whole problem
would be a lot simpler.

PatientDxTable
PatientID
ICD_Code
OrdinalPosition (If you had to track the DX)

Assuming that table plus a patient table

SELECT Distinct Patient.*, "Cataract"
FROM Patient INNER JOIN PatientDxTable
 
Hi John

Thanks for the tips. I'm working with databases and tables that I
unfortunately have no design control over, so can't make the changes you
suggest.

I was interested in your syntax though where you had the square brackets i.e
"C7[1-6]"

My query now isn't only returning "Cataracts", but does have to identify
them in the result set. I had used:

Cataract: IIf([OPCS_1] Between "C71" And "C759","Cataract",

but your syntax looked neater so I tried

Cataract: IIf([OPCS_1] Like "C7[1-6]","Cataract",

but it didn't seem to work (got no records identified as "Cataract") do you
know why?

Cheers,

Matt


John Spencer (MVP) said:
I would use something like the statement below to get the value. Since you only
want records with the calculated value of cataract and that only happens when
the DX is in the range C71 to C76, you really don't need to calculate the value
at all.

SAMPLE QUERY

SELECT "Cataract" as Cataract, <List of other fields>
FROM YourTable
WHERE ICD_1 Like "C7[1-6]"
OR ICD_2 Like "C7[1-6]"
OR ICD_3 Like "C7[1-6]"
OR ICD_4 Like "C7[1-6]"
OR ICD_5 Like "C7[1-6]"
OR ICD_6 Like "C7[1-6]"

By the way, you really should be storing the ICD codes in a separate table along
with a id code to tie the DX to the individual. That way the whole problem
would be a lot simpler.

PatientDxTable
PatientID
ICD_Code
OrdinalPosition (If you had to track the DX)

Assuming that table plus a patient table

SELECT Distinct Patient.*, "Cataract"
FROM Patient INNER JOIN PatientDxTable
Hi,

I have a calculated field using a nested IIf that basically creates either
the word "cataract" or an empty cell. It works fine but I want to add a
Criteria that returns ONLY the rows with Cataract. When I enetre the word
"Cataract" or Like "Cataract" it runs like a parameter prompt.

There's other threads here with similar problems that indicate I have to add
a "WHERE" clause to the critaria area, but I don't quite follow. The field is
generated using the followinf IIf:

Cataract: IIf([ICD_1] Between "C71" And "C76","Cataract",IIf([ICD_2] Between
"C71" And "C76","Cataract",IIf([ICD_3] Between "C71" And
"C76","Cataract",IIf([ICD_4] Between "C71" And "C76","Cataract",IIf([ICD_5]
Between "C71" And "C76","Cataract",IIf([ICD_6] Between "C71" And
"C76","Cataract",IIf([ICD_7] Between "C71" And "C76","Cataract","")

Any suggestions?
Click to expand...
Click to expand...
 
Probably because there are more than three characters in your ICD Codes. The
snippet I gave you would get values that were exactly C71, C72, C73, C74, C75,
and C76,

Try
LIKE "C7[1-5]*"

Adding the asterisk at the end will get values that start with C71, C72, C73,
C74, and C75
Look up Wildcards in the help file and with luck you will get some information
Hi John

Thanks for the tips. I'm working with databases and tables that I
unfortunately have no design control over, so can't make the changes you
suggest.

I was interested in your syntax though where you had the square brackets i.e
"C7[1-6]"

My query now isn't only returning "Cataracts", but does have to identify
them in the result set. I had used:

Cataract: IIf([OPCS_1] Between "C71" And "C759","Cataract",

but your syntax looked neater so I tried

Cataract: IIf([OPCS_1] Like "C7[1-6]","Cataract",

but it didn't seem to work (got no records identified as "Cataract") do you
know why?

Cheers,

Matt

John Spencer (MVP) said:
I would use something like the statement below to get the value. Since you only
want records with the calculated value of cataract and that only happens when
the DX is in the range C71 to C76, you really don't need to calculate the value
at all.

SAMPLE QUERY

SELECT "Cataract" as Cataract, <List of other fields>
FROM YourTable
WHERE ICD_1 Like "C7[1-6]"
OR ICD_2 Like "C7[1-6]"
OR ICD_3 Like "C7[1-6]"
OR ICD_4 Like "C7[1-6]"
OR ICD_5 Like "C7[1-6]"
OR ICD_6 Like "C7[1-6]"

By the way, you really should be storing the ICD codes in a separate table along
with a id code to tie the DX to the individual. That way the whole problem
would be a lot simpler.

PatientDxTable
PatientID
ICD_Code
OrdinalPosition (If you had to track the DX)

Assuming that table plus a patient table

SELECT Distinct Patient.*, "Cataract"
FROM Patient INNER JOIN PatientDxTable
Hi,

I have a calculated field using a nested IIf that basically creates either
the word "cataract" or an empty cell. It works fine but I want to add a
Criteria that returns ONLY the rows with Cataract. When I enetre the word
"Cataract" or Like "Cataract" it runs like a parameter prompt.

There's other threads here with similar problems that indicate I have to add
a "WHERE" clause to the critaria area, but I don't quite follow. The field is
generated using the followinf IIf:

Cataract: IIf([ICD_1] Between "C71" And "C76","Cataract",IIf([ICD_2] Between
"C71" And "C76","Cataract",IIf([ICD_3] Between "C71" And
"C76","Cataract",IIf([ICD_4] Between "C71" And "C76","Cataract",IIf([ICD_5]
Between "C71" And "C76","Cataract",IIf([ICD_6] Between "C71" And
"C76","Cataract",IIf([ICD_7] Between "C71" And "C76","Cataract","")

Any suggestions?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top