Criteria on calcualted field creates parameter prompt

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt D Francis - 17 May 2005 14:31 GMT

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?

Reply to this Message

Rick B - 17 May 2005 14:47 GMT

How do you have 7 opening parenthesis and only one closing?

Signature

Rick B

> Hi,
>
[quoted text clipped - 14 lines]
>
> Any suggestions?

Reply to this Message

Matt D Francis - 17 May 2005 14:56 GMT

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

> How do you have 7 opening parenthesis and only one closing?
>
[quoted text clipped - 20 lines]
> >
> > Any suggestions?

Reply to this Message

John Spencer (MVP) - 17 May 2005 21:50 GMT

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
On Patient.PatientID = PatientDxTable.PatientID
WHERE PatientDXTable.ICD_Code Between "C71" and "C76"

> Hi,
>
[quoted text clipped - 14 lines]
>
> Any suggestions?

Reply to this Message

Matt D Francis - 18 May 2005 09:41 GMT

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

> 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
[quoted text clipped - 46 lines]
> >
> > Any suggestions?

Reply to this Message

John Spencer (MVP) - 18 May 2005 23:41 GMT

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
on using them with SQL and Access.

> Hi John
>
[quoted text clipped - 71 lines]
> > >
> > > Any suggestions?

Reply to this Message