What is wrong with this expression

A

Al

I put the following expression in the criteria of a query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE (<> 'CE').
any help?
thanks
Al
 
G

Guest

The IIf structure is an if-then-else structure. You need
to nest it in order to do a second if: see below

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,IIf([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE','CE','CE'))
Click to expand...
-----Original Message-----
I put the following expression in the criteria of a query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE (<> 'CE').
any help?
thanks
Al
.
Click to expand...
 
G

Guest

I think I miswrote the expression the expression is:
IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<>'CE'),'CE') and it is placed under
[ProtocolStudyNumber] field which is from
tblPatientTreatment table. I wrote the expression as you
see it now but access automatically addedd
the "[tblPatientTreatment].[ProtocolStudyNumber]" as you
saw it in my original message. I am simpley trying to
accomplish the following:
If [PatientType] (Option group) = 1 then I want to see all
recoreds that are not equal to "CE" in the in the
[ProtocolStudyNumber] field ( this is the case that is
not working with me). Else, I want to see all records that
are equal to "CE" in the [ProtocolStudyNumber].

-----Original Message-----
The IIf structure is an if-then-else structure. You need
to nest it in order to do a second if: see below

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,IIf([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE','CE','CE'))
Click to expand...
-----Original Message-----
I put the following expression in the criteria of a query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE (<> 'CE').
any help?
thanks
Al
.
Click to expand...
.
Click to expand...
 
G

Guest

ok, I see it now...maybe

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,"<> 'CE'",'CE')
Click to expand...
Click to expand...
Click to expand...

Note quotes around the true condition.

Because you are trying to pass a complex value for the
true condition.
-----Original Message-----
I think I miswrote the expression the expression is:
IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<>'CE'),'CE') and it is placed under
[ProtocolStudyNumber] field which is from
tblPatientTreatment table. I wrote the expression as you
see it now but access automatically addedd
the "[tblPatientTreatment].[ProtocolStudyNumber]" as you
saw it in my original message. I am simpley trying to
accomplish the following:
If [PatientType] (Option group) = 1 then I want to see all
recoreds that are not equal to "CE" in the in the
[ProtocolStudyNumber] field ( this is the case that is
not working with me). Else, I want to see all records that
are equal to "CE" in the [ProtocolStudyNumber].

-----Original Message-----
The IIf structure is an if-then-else structure. You need
to nest it in order to do a second if: see below

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,IIf([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE','CE','CE'))
Click to expand...
-----Original Message-----
I put the following expression in the criteria of a query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE (<> 'CE').
any help?
thanks
Al
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
A

Al

This is a good Idea, However, it did not work. My query
simpley returns nothing. I also tried Not In('CE') and Not
Like 'CE' and Not Like "CE", etc. All these did not return
any results when their condition was true. any other Ideas?
thanks
-----Original Message-----
ok, I see it now...maybe

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,"<> 'CE'",'CE')
Click to expand...
Click to expand...

Note quotes around the true condition.

Because you are trying to pass a complex value for the
true condition.
-----Original Message-----
I think I miswrote the expression the expression is:
IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<>'CE'),'CE') and it is placed under
[ProtocolStudyNumber] field which is from
tblPatientTreatment table. I wrote the expression as you
see it now but access automatically addedd
the "[tblPatientTreatment].[ProtocolStudyNumber]" as you
saw it in my original message. I am simpley trying to
accomplish the following:
If [PatientType] (Option group) = 1 then I want to see all
recoreds that are not equal to "CE" in the in the
[ProtocolStudyNumber] field ( this is the case that is
not working with me). Else, I want to see all records that
are equal to "CE" in the [ProtocolStudyNumber].

-----Original Message-----
The IIf structure is an if-then-else structure. You need
to nest it in order to do a second if: see below

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,IIf([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE','CE','CE'))

-----Original Message-----
I put the following expression in the criteria of a query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE (<> 'CE').
any help?
thanks
Al
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
J

John Spencer (MVP)

SQL something like

SELECT FieldA, FIELDB, ProtocolStudyNumber
FROM YourTableName
WHERE (ProtocolStudyNumber = 'CE' and Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]![PatientType]<>1)
OR (ProtocolStudyNumber <> 'CE' and Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]![PatientType]=1)

Access query grid will probably create an ugly looking mess of this.

Can you copy and post the SQL of your query? Then perhaps someone can post the
exact text you need.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message



I think I miswrote the expression the expression is:
IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<>'CE'),'CE') and it is placed under
[ProtocolStudyNumber] field which is from
tblPatientTreatment table. I wrote the expression as you
see it now but access automatically addedd
the "[tblPatientTreatment].[ProtocolStudyNumber]" as you
saw it in my original message. I am simpley trying to
accomplish the following:
If [PatientType] (Option group) = 1 then I want to see all
recoreds that are not equal to "CE" in the in the
[ProtocolStudyNumber] field ( this is the case that is
not working with me). Else, I want to see all records that
are equal to "CE" in the [ProtocolStudyNumber].
-----Original Message-----
The IIf structure is an if-then-else structure. You need
to nest it in order to do a second if: see below

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,IIf([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE','CE','CE'))
Click to expand...
-----Original Message-----
I put the following expression in the criteria of a query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE (<> 'CE').
any help?
thanks
Al
.
Click to expand...
.
Click to expand...
Click to expand...
 
G

Guest

Sure thanks, here it is:
*************************************
TRANSFORM Count(tblPatientDemographic.Gender) AS
CountOfGender
SELECT tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
FROM tblPatientDemographic INNER JOIN tblPatientTreatment
ON tblPatientDemographic.PatientID =
tblPatientTreatment.PatientID
WHERE (((tblPatientTreatment.ProtocolStudyNumber)=IIf
([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<> 'CE',"CE")) AND
((tblPatientTreatment.ProtocolID) Is Not Null) AND
((tblPatientDemographic.Gender)="F" Or
(tblPatientDemographic.Gender)="M"))
GROUP BY tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
PIVOT tblPatientTreatment.Consultation In
("43,742","22,029","40,576","51,006","PP","CO","NEW");
**************************************
thanks again
Al
-----Original Message-----
SQL something like

SELECT FieldA, FIELDB, ProtocolStudyNumber
FROM YourTableName
WHERE (ProtocolStudyNumber = 'CE' and Forms!
Click to expand...
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
OR (ProtocolStudyNumber <> 'CE' and Forms! [frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1)

Access query grid will probably create an ugly looking mess of this.

Can you copy and post the SQL of your query? Then perhaps someone can post the
exact text you need.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message



[email protected] said:
I think I miswrote the expression the expression is:
IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<>'CE'),'CE') and it is placed under
[ProtocolStudyNumber] field which is from
tblPatientTreatment table. I wrote the expression as you
see it now but access automatically addedd
the "[tblPatientTreatment].[ProtocolStudyNumber]" as you
saw it in my original message. I am simpley trying to
accomplish the following:
If [PatientType] (Option group) = 1 then I want to see all
recoreds that are not equal to "CE" in the in the
[ProtocolStudyNumber] field ( this is the case that is
not working with me). Else, I want to see all records that
are equal to "CE" in the [ProtocolStudyNumber].
-----Original Message-----
The IIf structure is an if-then-else structure. You need
to nest it in order to do a second if: see below

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,IIf([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE','CE','CE'))

-----Original Message-----
I put the following expression in the criteria of a query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE (<> 'CE').
any help?
thanks
Al
.

.
Click to expand...
Click to expand...
.
Click to expand...
 
J

John Spencer (MVP)

First, Declare your parameters - you have to do this in a crosstab query.

Second, change your where statement to match my earlier suggestion.

Also, be consistent in your use of apostrophe and quotes as text delimiters.

WHERE (ProtocolStudyNumber = 'CE' and
Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]![PatientType]<>1)
OR (ProtocolStudyNumber <> 'CE' and
Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]![PatientType]=1)

Putting that all together, you should end up with something like the following
UNTESTED SQL statement.

Parameters
[Forms]![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]![PatientType] Long;
TRANSFORM Count(tblPatientDemographic.Gender) AS CountOfGender
SELECT tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
FROM tblPatientDemographic INNER JOIN tblPatientTreatment
ON tblPatientDemographic.PatientID = tblPatientTreatment.PatientID
WHERE ((ProtocolStudyNumber = "CE" AND
Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]![PatientType]<>1)
OR (ProtocolStudyNumber <> "CE" and
Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]![PatientType]=1)) AND
tblPatientTreatment.ProtocolID Is Not Null AND
tblPatientDemographic.Gender in ("F","M")
GROUP BY tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
PIVOT tblPatientTreatment.Consultation In
("43,742","22,029","40,576","51,006","PP","CO","NEW");

GOOD LUCK!

Sure thanks, here it is:
*************************************
TRANSFORM Count(tblPatientDemographic.Gender) AS
CountOfGender
SELECT tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
FROM tblPatientDemographic INNER JOIN tblPatientTreatment
ON tblPatientDemographic.PatientID =
tblPatientTreatment.PatientID
WHERE (((tblPatientTreatment.ProtocolStudyNumber)=IIf
([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<> 'CE',"CE")) AND
((tblPatientTreatment.ProtocolID) Is Not Null) AND
((tblPatientDemographic.Gender)="F" Or
(tblPatientDemographic.Gender)="M"))
GROUP BY tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
PIVOT tblPatientTreatment.Consultation In
("43,742","22,029","40,576","51,006","PP","CO","NEW");
**************************************
thanks again
Al
-----Original Message-----
SQL something like

SELECT FieldA, FIELDB, ProtocolStudyNumber
FROM YourTableName
WHERE (ProtocolStudyNumber = 'CE' and Forms!
Click to expand...
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
OR (ProtocolStudyNumber <> 'CE' and Forms! [frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1)

Access query grid will probably create an ugly looking mess of this.

Can you copy and post the SQL of your query? Then perhaps someone can post the
exact text you need.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message



[email protected] said:
I think I miswrote the expression the expression is:
IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<>'CE'),'CE') and it is placed under
[ProtocolStudyNumber] field which is from
tblPatientTreatment table. I wrote the expression as you
see it now but access automatically addedd
the "[tblPatientTreatment].[ProtocolStudyNumber]" as you
saw it in my original message. I am simpley trying to
accomplish the following:
If [PatientType] (Option group) = 1 then I want to see all
recoreds that are not equal to "CE" in the in the
[ProtocolStudyNumber] field ( this is the case that is
not working with me). Else, I want to see all records that
are equal to "CE" in the [ProtocolStudyNumber].

-----Original Message-----
The IIf structure is an if-then-else structure. You need
to nest it in order to do a second if: see below

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,IIf([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE','CE','CE'))

-----Original Message-----
I put the following expression in the criteria of a
query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE (<> 'CE').
any help?
thanks
Al
.

.
Click to expand...
.
Click to expand...
Click to expand...
 
A

AL

I do not know if you are going to read this or not,since
it is late reply. However, it works, thank you
Al
-----Original Message-----
First, Declare your parameters - you have to do this in a crosstab query.

Second, change your where statement to match my earlier suggestion.

Also, be consistent in your use of apostrophe and quotes as text delimiters.

WHERE (ProtocolStudyNumber = 'CE' and
Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
Click to expand...
OR (ProtocolStudyNumber <> 'CE' and
Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]! [PatientType]=1)

Putting that all together, you should end up with something like the following
UNTESTED SQL statement.

Parameters
[Forms]!
Click to expand...
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType] Long;
TRANSFORM Count(tblPatientDemographic.Gender) AS CountOfGender
SELECT tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
FROM tblPatientDemographic INNER JOIN tblPatientTreatment
ON tblPatientDemographic.PatientID = tblPatientTreatment.PatientID
WHERE ((ProtocolStudyNumber = "CE" AND
Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
Click to expand...
OR (ProtocolStudyNumber <> "CE" and
Forms![frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]! [PatientType]=1)) AND
tblPatientTreatment.ProtocolID Is Not Null AND
tblPatientDemographic.Gender in ("F","M")
GROUP BY tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
PIVOT tblPatientTreatment.Consultation In
("43,742","22,029","40,576","51,006","PP","CO","NEW");

GOOD LUCK!

[email protected] said:
Sure thanks, here it is:
*************************************
TRANSFORM Count(tblPatientDemographic.Gender) AS
CountOfGender
SELECT tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
FROM tblPatientDemographic INNER JOIN tblPatientTreatment
ON tblPatientDemographic.PatientID =
tblPatientTreatment.PatientID
WHERE (((tblPatientTreatment.ProtocolStudyNumber)=IIf
([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<> 'CE',"CE")) AND
((tblPatientTreatment.ProtocolID) Is Not Null) AND
((tblPatientDemographic.Gender)="F" Or
(tblPatientDemographic.Gender)="M"))
GROUP BY tblPatientTreatment.ProtocolID,
tblPatientTreatment.SubProtocolID,
tblPatientTreatment.ProtocolStudyNumber,
tblPatientDemographic.Gender,
tblPatientTreatment.Consultation
PIVOT tblPatientTreatment.Consultation In
("43,742","22,029","40,576","51,006","PP","CO","NEW");
**************************************
thanks again
Al
-----Original Message-----
SQL something like

SELECT FieldA, FIELDB, ProtocolStudyNumber
FROM YourTableName
WHERE (ProtocolStudyNumber = 'CE' and Forms!
Click to expand...
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
OR (ProtocolStudyNumber <> 'CE' and Forms! [frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1)

Access query grid will probably create an ugly looking mess of this.

Can you copy and post the SQL of your query? Then perhaps someone can post the
exact text you need.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message



(e-mail address removed) wrote:

I think I miswrote the expression the expression is:
IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,<>'CE'),'CE') and it is placed under
[ProtocolStudyNumber] field which is from
tblPatientTreatment table. I wrote the expression as you
see it now but access automatically addedd
the "[tblPatientTreatment].[ProtocolStudyNumber]" as you
saw it in my original message. I am simpley trying to
accomplish the following:
If [PatientType] (Option group) = 1 then I want to
Click to expand...
see
all
recoreds that are not equal to "CE" in the in the
[ProtocolStudyNumber] field ( this is the case that is
not working with me). Else, I want to see all records that
are equal to "CE" in the [ProtocolStudyNumber].

-----Original Message-----
The IIf structure is an if-then-else structure. You need
to nest it in order to do a second if: see below

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,IIf([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE','CE','CE'))

-----Original Message-----
I put the following expression in the criteria of a
query:

IIf([Forms]!
[frmNoOfPatientsInEachProtocolByProtocol/Ptnumber]!
[PatientType]=1,([tblPatientTreatment].
[ProtocolStudyNumber]<>'CE'),'CE')

it only works in the case of 'CE' only i.e. (PatientType
=1) but it does not work when it is not CE
Click to expand...
( said:
any help?
thanks
Al
.

.

.
Click to expand...
Click to expand...
.
Click to expand...
 

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

IIF/OR function togerher-not work 4
Isolating string combinations 3
Date in DSum from a Control on a Form 0
Access Crash on Query Save. What do I do? 1
Checkbox Problem 2
Code not working, copy in Select Case section not copying over. 10
Some tasks won't sync to my PDA 0
Uninstall of Symantec Corporate 9 breaks Outlook 2003 3

Top