Nested IIf doesn't work


D

dsc2bjn

I created a nested IIf statement to allow the user to select differing values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow two
values to print on the report (I created a drop down menu, the user to select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
Ad

Advertisements

L

Larry Linson

There are instances where only an IIF can be used, but if you have the
option to use a function in which you can indent and structure the code, IFs
or CASE statements are easier to follow, unless your IIF is truly trivial.

Larry Linson
Microsoft Office Access MVP

Douglas J. Steele said:
You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
I created a nested IIf statement to allow the user to select differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow two
values to print on the report (I created a drop down menu, the user to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

dsc2bjn

I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



Douglas J. Steele said:
You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
I created a nested IIf statement to allow the user to select differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow two
values to print on the report (I created a drop down menu, the user to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

dsc2bjn

I am a VB novice, so...

How would I go about using a function to allow the user to have a choice of
what records to retrieve at the time they wish to generate a report?

Larry Linson said:
There are instances where only an IIF can be used, but if you have the
option to use a function in which you can indent and structure the code, IFs
or CASE statements are easier to follow, unless your IIF is truly trivial.

Larry Linson
Microsoft Office Access MVP

Douglas J. Steele said:
You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
I created a nested IIf statement to allow the user to select differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow two
values to print on the report (I created a drop down menu, the user to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

Douglas J. Steele

Okay, I suppose it might be correct. The True part of that last IIf
statement is

([Other Weakness Corrective Actions].[Deficiency Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other portions are
return a string. I had been thinking that it was supposed to be another IIf
statement.

I don't see, though, how you expect to get two values on the report from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



Douglas J. Steele said:
You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
I created a nested IIf statement to allow the user to select differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow two
values to print on the report (I created a drop down menu, the user to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period
(Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
Ad

Advertisements

D

dsc2bjn

I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion return multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the statement is typed
incorrectly or too complex to be evaluated.

Douglas J. Steele said:
Okay, I suppose it might be correct. The True part of that last IIf
statement is

([Other Weakness Corrective Actions].[Deficiency Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other portions are
return a string. I had been thinking that it was supposed to be another IIf
statement.

I don't see, though, how you expect to get two values on the report from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



Douglas J. Steele said:
You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a nested IIf statement to allow the user to select differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow two
values to print on the report (I created a drop down menu, the user to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period
(Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

Douglas J. Steele

I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end up with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 = "CCE". That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself. You'll either have
to dynamically generate the SQL for the query, or else don't put a criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion return multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the statement is typed
incorrectly or too complex to be evaluated.

Douglas J. Steele said:
Okay, I suppose it might be correct. The True part of that last IIf
statement is

([Other Weakness Corrective Actions].[Deficiency Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other portions
are
return a string. I had been thinking that it was supposed to be another
IIf
statement.

I don't see, though, how you expect to get two values on the report from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



:

You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a nested IIf statement to allow the user to select
differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow
two
values to print on the report (I created a drop down menu, the user
to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period
(Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness
Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
L

Larry Linson

I would approach that one of these ways:

1. If in a developed application, use the Where Condition or Filter
arguments of the DoCmd.OpenReport to select only the desired records, OR,
create SQL that included a WHERE clause to retrieve only the desired
records, and, in the Open event of the report, replace the original
RecordSource with the modified SQL that includes the WHERE clause.

"Made-up" examples:

DoCmd.OpenReport "rptBoats", acViewPreview, , "[BoatData] =
""DeckBoat""", acWindowNormal

2. If it were an end-user database, and I was running the Report myself,
manually, I would probably refer to a Saved Query in the RecordSource of the
Report, and would open the Saved Query and insert the Criteria value in the
Criteria Line underneath the Field of interest... in the counterpart of the
above, I'd find the column in the Query Builder grid containing the field
BoatData and insert the value "DeckBoat" in the Criteria line. Then when I
ran the report, only those Records having Boat Data = "Deck Boat" would be
selected for the Report.

Larry Linson
Microsoft Office Access MVP


dsc2bjn said:
I am a VB novice, so...

How would I go about using a function to allow the user to have a choice
of
what records to retrieve at the time they wish to generate a report?

Larry Linson said:
There are instances where only an IIF can be used, but if you have the
option to use a function in which you can indent and structure the code,
IFs
or CASE statements are easier to follow, unless your IIF is truly
trivial.

Larry Linson
Microsoft Office Access MVP

Douglas J. Steele said:
You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a nested IIf statement to allow the user to select differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow two
values to print on the report (I created a drop down menu, the user to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period
(Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

dsc2bjn

That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the time the
report is generated (as you suggest)?

Douglas J. Steele said:
I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end up with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 = "CCE". That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself. You'll either have
to dynamically generate the SQL for the query, or else don't put a criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion return multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the statement is typed
incorrectly or too complex to be evaluated.

Douglas J. Steele said:
Okay, I suppose it might be correct. The True part of that last IIf
statement is

([Other Weakness Corrective Actions].[Deficiency Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other portions
are
return a string. I had been thinking that it was supposed to be another
IIf
statement.

I don't see, though, how you expect to get two values on the report from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



:

You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a nested IIf statement to allow the user to select
differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to allow
two
values to print on the report (I created a drop down menu, the user
to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period
(Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness
Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

Douglas J. Steele

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO', "CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the time the
report is generated (as you suggest)?

Douglas J. Steele said:
I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end up with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 = "CCE". That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself. You'll either
have
to dynamically generate the SQL for the query, or else don't put a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion return
multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the statement is
typed
incorrectly or too complex to be evaluated.

:

Okay, I suppose it might be correct. The True part of that last IIf
statement is

([Other Weakness Corrective Actions].[Deficiency Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other portions
are
return a string. I had been thinking that it was supposed to be
another
IIf
statement.

I don't see, though, how you expect to get two values on the report
from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



:

You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a nested IIf statement to allow the user to select
differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to
allow
two
values to print on the report (I created a drop down menu, the
user
to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period
(Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness
Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness
Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness
Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
Ad

Advertisements

D

dsc2bjn

That would work for the condition where the value is either BO or CCE, but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL" (which
currently the user selects from a pop-up form?

Would I need to do a If statement within the "On Open" event?

Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ("CCE')"

ETC.
End If
end if






Douglas J. Steele said:
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO', "CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the time the
report is generated (as you suggest)?

Douglas J. Steele said:
I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end up with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 = "CCE". That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself. You'll either
have
to dynamically generate the SQL for the query, or else don't put a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion return
multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the statement is
typed
incorrectly or too complex to be evaluated.

:

Okay, I suppose it might be correct. The True part of that last IIf
statement is

([Other Weakness Corrective Actions].[Deficiency Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other portions
are
return a string. I had been thinking that it was supposed to be
another
IIf
statement.

I don't see, though, how you expect to get two values on the report
from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



:

You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a nested IIf statement to allow the user to select
differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to
allow
two
values to print on the report (I created a drop down menu, the
user
to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting Period
(Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness
Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness
Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness
Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

Douglas J. Steele

If they've chosen All, you don't need a condition. If they've chosen some
other combination of conditions, you'll have to build a string containing
the conditions of interest, and then use that variable. It's not clear to me
how you're prompting them for their choice, so I really can't offer any
advice on how to populate that variable, but assuming you've got a variable
strChoice containing "'BO', 'CCE'", your condition would be

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN (" & strChoice
& ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
That would work for the condition where the value is either BO or CCE, but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL" (which
currently the user selects from a pop-up form?

Would I need to do a If statement within the "On Open" event?

Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ("CCE')"

ETC.
End If
end if






Douglas J. Steele said:
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO',
"CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the time the
report is generated (as you suggest)?

:

I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end up with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 = "CCE".
That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself. You'll
either
have
to dynamically generate the SQL for the query, or else don't put a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion return
multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and
IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the statement is
typed
incorrectly or too complex to be evaluated.

:

Okay, I suppose it might be correct. The True part of that last IIf
statement is

([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other
portions
are
return a string. I had been thinking that it was supposed to be
another
IIf
statement.

I don't see, though, how you expect to get two values on the report
from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



:

You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a nested IIf statement to allow the user to select
differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want to
allow
two
values to print on the report (I created a drop down menu, the
user
to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting
Period
(Print
Preview)]![Weakness Source]="CCE","CCE",IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness
Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness
Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness
Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

dsc2bjn

Douglas,
Would you walk me through the code you provided?

I had created a combo box and populated the values of "BO", "CCE", "SCAMPI",
and "BO &CCE" within the combox box. The thought being I coud run a query to
return both sets of records of "BO" and "CCE" with the last selection.

I changed the combo box to a list box as your code instructs.

If follow your code correctly it is suppose to allow the user to select any
multple of the choices defined; however, the list box does not allow me to
select multiple items.






Douglas J. Steele said:
Dim strChoice As String
Dim varSelected As Variant

If Me!NameOfListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me!NameOfListbox.ItermsSelected
strChoice = strChoice & Me!NameOfListbox.ItemData(varSelected) & ", "
Next varSelected
strChoice = Left(strChoice, Len(strChoice) - 2)
DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"
Else

DoCmd.OpenReport "NameOfReport", acViewPreview
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
I created a pop-up form with a pull down list that I created by typing in
the
values. My thought was when they clicked on a button I would run a query
that would match the selection with the field values in the database.
Since
"BO & CCE" are two choices, I thought I could write an IIf statement to
return both sets of records.

From what has been said in the message string, that doesn't seem possible.

So...now I need to figure out a way to allow them to pick from the list
and
return the record for those values.

I don't have any idea of how to create a variable string as you suggest.
Any additional information would be appreciated.


Douglas J. Steele said:
If they've chosen All, you don't need a condition. If they've chosen some
other combination of conditions, you'll have to build a string containing
the conditions of interest, and then use that variable. It's not clear to
me
how you're prompting them for their choice, so I really can't offer any
advice on how to populate that variable, but assuming you've got a
variable
strChoice containing "'BO', 'CCE'", your condition would be

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN (" &
strChoice
& ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


That would work for the condition where the value is either BO or CCE,
but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL" (which
currently the user selects from a pop-up form?

Would I need to do a If statement within the "On Open" event?

Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ("CCE')"

ETC.
End If
end if






:

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO',
"CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the time
the
report is generated (as you suggest)?

:

I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end up
with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 = "CCE".
That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself. You'll
either
have
to dynamically generate the SQL for the query, or else don't put a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion return
multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and
IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the statement
is
typed
incorrectly or too complex to be evaluated.

:

Okay, I suppose it might be correct. The True part of that last
IIf
statement is

([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other
portions
are
return a string. I had been thinking that it was supposed to be
another
IIf
statement.

I don't see, though, how you expect to get two values on the
report
from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency Source]))))

Please explain.



:

You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I created a nested IIf statement to allow the user to select
differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I want
to
allow
two
values to print on the report (I created a drop down menu,
the
user
to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness Reporting
Period
(Print
Preview)]![Weakness
Source]="CCE","CCE",IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness
Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other Weakness
Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness
Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the
IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

Douglas J. Steele

You need to set the list box's MultiSelect property to either Simple
(Multiple items are selected or deselected by clicking them with the mouse
or pressing the SPACEBAR) or Extended (Multiple items are selected by
holding down SHIFT and clicking them with the mouse or by holding down SHIFT
and pressing an arrow key to extend the selection from the previously
selected item to the current item. You can also select items by dragging
with the mouse. Holding down CTRL and clicking an item selects or deselects
that item.)

Once you've made that change:

Dim strChoice As String
Dim varSelected As Variant

' No need to set a criteria if nothing's selected in the list box

If Me!NameOfListBox.ItemsSelected.Count > 0 Then

' The ItemsSelected collection contains the details of each of the
' selected rows. While referring to an item in the ItemsSelected
' collection gives you a row number, you need to use a
' variant (varSelected) when using For Each with it.

For Each varSelected In Me!NameOfListbox.ItermsSelected

' The ItemData object will return the value of the bound column of the
' selected row. Add its value to the string, and put a comma (and space)
' afterwards.
' Note the change to the code here. Since you're using text values
' ("BO", "CCE", "SCAMPI", etc.), you need quotes around them.
' Chr$(34) returns a double quote.

strChoice = strChoice & Chr(34) & _
Me!NameOfListbox.ItemData(varSelected) & Chr$(34) & ", "

Next varSelected

' Since we were adding the comma and space after each entry,
' we need to remove the final pair.

strChoice = Left(strChoice, Len(strChoice) - 2)

' strChoice now contains a comma-separated list that we can
' use in an IN clause

DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"

Else

DoCmd.OpenReport "NameOfReport", acViewPreview

End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
Douglas,
Would you walk me through the code you provided?

I had created a combo box and populated the values of "BO", "CCE",
"SCAMPI",
and "BO &CCE" within the combox box. The thought being I coud run a query
to
return both sets of records of "BO" and "CCE" with the last selection.

I changed the combo box to a list box as your code instructs.

If follow your code correctly it is suppose to allow the user to select
any
multple of the choices defined; however, the list box does not allow me to
select multiple items.






Douglas J. Steele said:
Dim strChoice As String
Dim varSelected As Variant

If Me!NameOfListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me!NameOfListbox.ItermsSelected
strChoice = strChoice & Me!NameOfListbox.ItemData(varSelected) & ",
"
Next varSelected
strChoice = Left(strChoice, Len(strChoice) - 2)
DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"
Else

DoCmd.OpenReport "NameOfReport", acViewPreview
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
I created a pop-up form with a pull down list that I created by typing
in
the
values. My thought was when they clicked on a button I would run a
query
that would match the selection with the field values in the database.
Since
"BO & CCE" are two choices, I thought I could write an IIf statement to
return both sets of records.

From what has been said in the message string, that doesn't seem
possible.

So...now I need to figure out a way to allow them to pick from the list
and
return the record for those values.

I don't have any idea of how to create a variable string as you
suggest.
Any additional information would be appreciated.


:

If they've chosen All, you don't need a condition. If they've chosen
some
other combination of conditions, you'll have to build a string
containing
the conditions of interest, and then use that variable. It's not clear
to
me
how you're prompting them for their choice, so I really can't offer
any
advice on how to populate that variable, but assuming you've got a
variable
strChoice containing "'BO', 'CCE'", your condition would be

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN (" &
strChoice
& ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


That would work for the condition where the value is either BO or
CCE,
but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL"
(which
currently the user selects from a pop-up form?

Would I need to do a If statement within the "On Open" event?

Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
("CCE')"

ETC.
End If
end if






:

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO',
"CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the
time
the
report is generated (as you suggest)?

:

I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end up
with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 =
"CCE".
That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself. You'll
either
have
to dynamically generate the SQL for the query, or else don't put
a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion
return
multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and
IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the
statement
is
typed
incorrectly or too complex to be evaluated.

:

Okay, I suppose it might be correct. The True part of that
last
IIf
statement is

([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other
portions
are
return a string. I had been thinking that it was supposed to
be
another
IIf
statement.

I don't see, though, how you expect to get two values on the
report
from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency
Source])<>"SCAMPI",

The portion after the last IIf is the False condition:
[Other Weakness Corrective Actions]![Deficiency
Source]))))

Please explain.



:

You're missing the "IIf" in front of the last condition.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I created a nested IIf statement to allow the user to
select
differing
values
from the database at the time they wish to run a report.

Everything works ok, except in the condition where I
want
to
allow
two
values to print on the report (I created a drop down
menu,
the
user
to
select
thier value(s)).

I have tried multiple variations with no luck.


My code :

IIf([Forms]![frmOther Weakness Reporting Period (Print
Preview)]![Weakness
Source]="BO","BO",IIf([Forms]![frmOther Weakness
Reporting
Period
(Print
Preview)]![Weakness
Source]="CCE","CCE",IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="SCAMPI","SCAMPI",IIf([Forms]![frmOther Weakness
Reporting
Period
(Print Preview)]![Weakness Source]="BOCCE",([Other
Weakness
Corrective
Actions].[Deficiency Source])<>"SCAMPI",[Other Weakness
Corrective
Actions]![Deficiency Source]))))


(the portion that is not working is the
IIf([Forms]![frmOther
Weakness
Reporting Period (Print Preview)]![Weakness
Source]="BOCCE",([Other
Weakness
Corrective Actions].[Deficiency Source])<>"SCAMPI",)
 
D

dsc2bjn

Douglas,
First of all, THANKS!!

The code is errorring out, "Syntax error (missing operator) in query
expression '(Defieciency Source IN ("SCAMPI"))".

I tried placing and underscore between Defieciency Source, but then it
prompts me for a value.



Douglas J. Steele said:
You need to set the list box's MultiSelect property to either Simple
(Multiple items are selected or deselected by clicking them with the mouse
or pressing the SPACEBAR) or Extended (Multiple items are selected by
holding down SHIFT and clicking them with the mouse or by holding down SHIFT
and pressing an arrow key to extend the selection from the previously
selected item to the current item. You can also select items by dragging
with the mouse. Holding down CTRL and clicking an item selects or deselects
that item.)

Once you've made that change:

Dim strChoice As String
Dim varSelected As Variant

' No need to set a criteria if nothing's selected in the list box

If Me!NameOfListBox.ItemsSelected.Count > 0 Then

' The ItemsSelected collection contains the details of each of the
' selected rows. While referring to an item in the ItemsSelected
' collection gives you a row number, you need to use a
' variant (varSelected) when using For Each with it.

For Each varSelected In Me!NameOfListbox.ItermsSelected

' The ItemData object will return the value of the bound column of the
' selected row. Add its value to the string, and put a comma (and space)
' afterwards.
' Note the change to the code here. Since you're using text values
' ("BO", "CCE", "SCAMPI", etc.), you need quotes around them.
' Chr$(34) returns a double quote.

strChoice = strChoice & Chr(34) & _
Me!NameOfListbox.ItemData(varSelected) & Chr$(34) & ", "

Next varSelected

' Since we were adding the comma and space after each entry,
' we need to remove the final pair.

strChoice = Left(strChoice, Len(strChoice) - 2)

' strChoice now contains a comma-separated list that we can
' use in an IN clause

DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"

Else

DoCmd.OpenReport "NameOfReport", acViewPreview

End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
Douglas,
Would you walk me through the code you provided?

I had created a combo box and populated the values of "BO", "CCE",
"SCAMPI",
and "BO &CCE" within the combox box. The thought being I coud run a query
to
return both sets of records of "BO" and "CCE" with the last selection.

I changed the combo box to a list box as your code instructs.

If follow your code correctly it is suppose to allow the user to select
any
multple of the choices defined; however, the list box does not allow me to
select multiple items.






Douglas J. Steele said:
Dim strChoice As String
Dim varSelected As Variant

If Me!NameOfListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me!NameOfListbox.ItermsSelected
strChoice = strChoice & Me!NameOfListbox.ItemData(varSelected) & ",
"
Next varSelected
strChoice = Left(strChoice, Len(strChoice) - 2)
DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"
Else

DoCmd.OpenReport "NameOfReport", acViewPreview
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a pop-up form with a pull down list that I created by typing
in
the
values. My thought was when they clicked on a button I would run a
query
that would match the selection with the field values in the database.
Since
"BO & CCE" are two choices, I thought I could write an IIf statement to
return both sets of records.

From what has been said in the message string, that doesn't seem
possible.

So...now I need to figure out a way to allow them to pick from the list
and
return the record for those values.

I don't have any idea of how to create a variable string as you
suggest.
Any additional information would be appreciated.


:

If they've chosen All, you don't need a condition. If they've chosen
some
other combination of conditions, you'll have to build a string
containing
the conditions of interest, and then use that variable. It's not clear
to
me
how you're prompting them for their choice, so I really can't offer
any
advice on how to populate that variable, but assuming you've got a
variable
strChoice containing "'BO', 'CCE'", your condition would be

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN (" &
strChoice
& ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


That would work for the condition where the value is either BO or
CCE,
but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL"
(which
currently the user selects from a pop-up form?

Would I need to do a If statement within the "On Open" event?

Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
("CCE')"

ETC.
End If
end if






:

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN ('BO',
"CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the
time
the
report is generated (as you suggest)?

:

I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end up
with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 =
"CCE".
That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself. You'll
either
have
to dynamically generate the SQL for the query, or else don't put
a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion
return
multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part) and
IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the
statement
is
typed
incorrectly or too complex to be evaluated.

:

Okay, I suppose it might be correct. The True part of that
last
IIf
statement is

([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI"

which means it'll return True or False, whereas all the other
portions
are
return a string. I had been thinking that it was supposed to
be
another
IIf
statement.

I don't see, though, how you expect to get two values on the
report
from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency
Source])<>"SCAMPI",
 
Ad

Advertisements

D

Douglas J. Steele

If the name of the field has a space in it, you need to enclose the name in
square brackets:

[Defieciency Source] IN ("SCAMPI")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
Douglas,
First of all, THANKS!!

The code is errorring out, "Syntax error (missing operator) in query
expression '(Defieciency Source IN ("SCAMPI"))".

I tried placing and underscore between Defieciency Source, but then it
prompts me for a value.



Douglas J. Steele said:
You need to set the list box's MultiSelect property to either Simple
(Multiple items are selected or deselected by clicking them with the
mouse
or pressing the SPACEBAR) or Extended (Multiple items are selected by
holding down SHIFT and clicking them with the mouse or by holding down
SHIFT
and pressing an arrow key to extend the selection from the previously
selected item to the current item. You can also select items by dragging
with the mouse. Holding down CTRL and clicking an item selects or
deselects
that item.)

Once you've made that change:

Dim strChoice As String
Dim varSelected As Variant

' No need to set a criteria if nothing's selected in the list box

If Me!NameOfListBox.ItemsSelected.Count > 0 Then

' The ItemsSelected collection contains the details of each of the
' selected rows. While referring to an item in the ItemsSelected
' collection gives you a row number, you need to use a
' variant (varSelected) when using For Each with it.

For Each varSelected In Me!NameOfListbox.ItermsSelected

' The ItemData object will return the value of the bound column of the
' selected row. Add its value to the string, and put a comma (and space)
' afterwards.
' Note the change to the code here. Since you're using text values
' ("BO", "CCE", "SCAMPI", etc.), you need quotes around them.
' Chr$(34) returns a double quote.

strChoice = strChoice & Chr(34) & _
Me!NameOfListbox.ItemData(varSelected) & Chr$(34) & ", "

Next varSelected

' Since we were adding the comma and space after each entry,
' we need to remove the final pair.

strChoice = Left(strChoice, Len(strChoice) - 2)

' strChoice now contains a comma-separated list that we can
' use in an IN clause

DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"

Else

DoCmd.OpenReport "NameOfReport", acViewPreview

End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


dsc2bjn said:
Douglas,
Would you walk me through the code you provided?

I had created a combo box and populated the values of "BO", "CCE",
"SCAMPI",
and "BO &CCE" within the combox box. The thought being I coud run a
query
to
return both sets of records of "BO" and "CCE" with the last selection.

I changed the combo box to a list box as your code instructs.

If follow your code correctly it is suppose to allow the user to select
any
multple of the choices defined; however, the list box does not allow me
to
select multiple items.






:

Dim strChoice As String
Dim varSelected As Variant

If Me!NameOfListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me!NameOfListbox.ItermsSelected
strChoice = strChoice & Me!NameOfListbox.ItemData(varSelected) &
",
"
Next varSelected
strChoice = Left(strChoice, Len(strChoice) - 2)
DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"
Else

DoCmd.OpenReport "NameOfReport", acViewPreview
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a pop-up form with a pull down list that I created by
typing
in
the
values. My thought was when they clicked on a button I would run a
query
that would match the selection with the field values in the
database.
Since
"BO & CCE" are two choices, I thought I could write an IIf statement
to
return both sets of records.

From what has been said in the message string, that doesn't seem
possible.

So...now I need to figure out a way to allow them to pick from the
list
and
return the record for those values.

I don't have any idea of how to create a variable string as you
suggest.
Any additional information would be appreciated.


:

If they've chosen All, you don't need a condition. If they've
chosen
some
other combination of conditions, you'll have to build a string
containing
the conditions of interest, and then use that variable. It's not
clear
to
me
how you're prompting them for their choice, so I really can't offer
any
advice on how to populate that variable, but assuming you've got a
variable
strChoice containing "'BO', 'CCE'", your condition would be

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN (" &
strChoice
& ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


That would work for the condition where the value is either BO or
CCE,
but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL"
(which
currently the user selects from a pop-up form?

Would I need to do a If statement within the "On Open" event?

Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
("CCE')"

ETC.
End If
end if






:

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
('BO',
"CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the
time
the
report is generated (as you suggest)?

:

I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end
up
with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 =
"CCE".
That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself.
You'll
either
have
to dynamically generate the SQL for the query, or else don't
put
a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion
return
multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part)
and
IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the
statement
is
typed
incorrectly or too complex to be evaluated.

:

Okay, I suppose it might be correct. The True part of that
last
IIf
statement is

([Other Weakness Corrective Actions].[Deficiency
Source])<>"SCAMPI"

which means it'll return True or False, whereas all the
other
portions
are
return a string. I had been thinking that it was supposed
to
be
another
IIf
statement.

I don't see, though, how you expect to get two values on
the
report
from
that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I don't understand what you mean.

The last IIf statement is:

IIf([Forms]![frmOther Weakness Reporting
Period]![Weakness
Source]="BOCCE",([Other Weakness Corrective
Actions].[Deficiency
Source])<>"SCAMPI",
 
D

dsc2bjn

I had put in brackets and it wasn't working. I tried a couple of other
things and they didn't work either. I would get things like method does not
exist and the such.

After a couple of days of frustration, took an archive copy of the database
and put the code fresh.

What do you know...it worked. Too much baggage laying around from failed
attempts I guess. Or the Lynx gremlins.


THANKS!!!


Douglas J. Steele said:
If the name of the field has a space in it, you need to enclose the name in
square brackets:

[Defieciency Source] IN ("SCAMPI")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
Douglas,
First of all, THANKS!!

The code is errorring out, "Syntax error (missing operator) in query
expression '(Defieciency Source IN ("SCAMPI"))".

I tried placing and underscore between Defieciency Source, but then it
prompts me for a value.



Douglas J. Steele said:
You need to set the list box's MultiSelect property to either Simple
(Multiple items are selected or deselected by clicking them with the
mouse
or pressing the SPACEBAR) or Extended (Multiple items are selected by
holding down SHIFT and clicking them with the mouse or by holding down
SHIFT
and pressing an arrow key to extend the selection from the previously
selected item to the current item. You can also select items by dragging
with the mouse. Holding down CTRL and clicking an item selects or
deselects
that item.)

Once you've made that change:

Dim strChoice As String
Dim varSelected As Variant

' No need to set a criteria if nothing's selected in the list box

If Me!NameOfListBox.ItemsSelected.Count > 0 Then

' The ItemsSelected collection contains the details of each of the
' selected rows. While referring to an item in the ItemsSelected
' collection gives you a row number, you need to use a
' variant (varSelected) when using For Each with it.

For Each varSelected In Me!NameOfListbox.ItermsSelected

' The ItemData object will return the value of the bound column of the
' selected row. Add its value to the string, and put a comma (and space)
' afterwards.
' Note the change to the code here. Since you're using text values
' ("BO", "CCE", "SCAMPI", etc.), you need quotes around them.
' Chr$(34) returns a double quote.

strChoice = strChoice & Chr(34) & _
Me!NameOfListbox.ItemData(varSelected) & Chr$(34) & ", "

Next varSelected

' Since we were adding the comma and space after each entry,
' we need to remove the final pair.

strChoice = Left(strChoice, Len(strChoice) - 2)

' strChoice now contains a comma-separated list that we can
' use in an IN clause

DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"

Else

DoCmd.OpenReport "NameOfReport", acViewPreview

End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,
Would you walk me through the code you provided?

I had created a combo box and populated the values of "BO", "CCE",
"SCAMPI",
and "BO &CCE" within the combox box. The thought being I coud run a
query
to
return both sets of records of "BO" and "CCE" with the last selection.

I changed the combo box to a list box as your code instructs.

If follow your code correctly it is suppose to allow the user to select
any
multple of the choices defined; however, the list box does not allow me
to
select multiple items.






:

Dim strChoice As String
Dim varSelected As Variant

If Me!NameOfListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me!NameOfListbox.ItermsSelected
strChoice = strChoice & Me!NameOfListbox.ItemData(varSelected) &
",
"
Next varSelected
strChoice = Left(strChoice, Len(strChoice) - 2)
DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"
Else

DoCmd.OpenReport "NameOfReport", acViewPreview
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a pop-up form with a pull down list that I created by
typing
in
the
values. My thought was when they clicked on a button I would run a
query
that would match the selection with the field values in the
database.
Since
"BO & CCE" are two choices, I thought I could write an IIf statement
to
return both sets of records.

From what has been said in the message string, that doesn't seem
possible.

So...now I need to figure out a way to allow them to pick from the
list
and
return the record for those values.

I don't have any idea of how to create a variable string as you
suggest.
Any additional information would be appreciated.


:

If they've chosen All, you don't need a condition. If they've
chosen
some
other combination of conditions, you'll have to build a string
containing
the conditions of interest, and then use that variable. It's not
clear
to
me
how you're prompting them for their choice, so I really can't offer
any
advice on how to populate that variable, but assuming you've got a
variable
strChoice containing "'BO', 'CCE'", your condition would be

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN (" &
strChoice
& ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


That would work for the condition where the value is either BO or
CCE,
but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL"
(which
currently the user selects from a pop-up form?

Would I need to do a If statement within the "On Open" event?

Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
("CCE')"

ETC.
End If
end if






:

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
('BO',
"CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the
time
the
report is generated (as you suggest)?

:

I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end
up
with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 =
"CCE".
That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself.
You'll
either
have
to dynamically generate the SQL for the query, or else don't
put
a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I guess a better thing to ask would be:

"How do I write a IIf statement to have the true portion
return
multiple
values?

I have tried: IIf(this happens, <>"SCAMPI", False part)
and
IIf(this
happens, "BO" or CCE", [False part)

When I use the <>, nothing is returned.

When I use the 'or', I get an error message saying the
statement
is
 
Ad

Advertisements

D

dsc2bjn

Thanks again!

This works great for previewing the report.

I now want take the same approach with the report being sent via email
(snapshot view). I created a macro which handles the email issue, but can't
figure out how to pass the criteria which limits the reporting to the
selection made on the form.

Any suggestions are greatly appreciated.


dsc2bjn said:
I had put in brackets and it wasn't working. I tried a couple of other
things and they didn't work either. I would get things like method does not
exist and the such.

After a couple of days of frustration, took an archive copy of the database
and put the code fresh.

What do you know...it worked. Too much baggage laying around from failed
attempts I guess. Or the Lynx gremlins.


THANKS!!!


Douglas J. Steele said:
If the name of the field has a space in it, you need to enclose the name in
square brackets:

[Defieciency Source] IN ("SCAMPI")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dsc2bjn said:
Douglas,
First of all, THANKS!!

The code is errorring out, "Syntax error (missing operator) in query
expression '(Defieciency Source IN ("SCAMPI"))".

I tried placing and underscore between Defieciency Source, but then it
prompts me for a value.



:

You need to set the list box's MultiSelect property to either Simple
(Multiple items are selected or deselected by clicking them with the
mouse
or pressing the SPACEBAR) or Extended (Multiple items are selected by
holding down SHIFT and clicking them with the mouse or by holding down
SHIFT
and pressing an arrow key to extend the selection from the previously
selected item to the current item. You can also select items by dragging
with the mouse. Holding down CTRL and clicking an item selects or
deselects
that item.)

Once you've made that change:

Dim strChoice As String
Dim varSelected As Variant

' No need to set a criteria if nothing's selected in the list box

If Me!NameOfListBox.ItemsSelected.Count > 0 Then

' The ItemsSelected collection contains the details of each of the
' selected rows. While referring to an item in the ItemsSelected
' collection gives you a row number, you need to use a
' variant (varSelected) when using For Each with it.

For Each varSelected In Me!NameOfListbox.ItermsSelected

' The ItemData object will return the value of the bound column of the
' selected row. Add its value to the string, and put a comma (and space)
' afterwards.
' Note the change to the code here. Since you're using text values
' ("BO", "CCE", "SCAMPI", etc.), you need quotes around them.
' Chr$(34) returns a double quote.

strChoice = strChoice & Chr(34) & _
Me!NameOfListbox.ItemData(varSelected) & Chr$(34) & ", "

Next varSelected

' Since we were adding the comma and space after each entry,
' we need to remove the final pair.

strChoice = Left(strChoice, Len(strChoice) - 2)

' strChoice now contains a comma-separated list that we can
' use in an IN clause

DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"

Else

DoCmd.OpenReport "NameOfReport", acViewPreview

End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,
Would you walk me through the code you provided?

I had created a combo box and populated the values of "BO", "CCE",
"SCAMPI",
and "BO &CCE" within the combox box. The thought being I coud run a
query
to
return both sets of records of "BO" and "CCE" with the last selection.

I changed the combo box to a list box as your code instructs.

If follow your code correctly it is suppose to allow the user to select
any
multple of the choices defined; however, the list box does not allow me
to
select multiple items.






:

Dim strChoice As String
Dim varSelected As Variant

If Me!NameOfListBox.ItemsSelected.Count > 0 Then
For Each varSelected In Me!NameOfListbox.ItermsSelected
strChoice = strChoice & Me!NameOfListbox.ItemData(varSelected) &
",
"
Next varSelected
strChoice = Left(strChoice, Len(strChoice) - 2)
DoCmd.OpenReport "NameOfReport", acViewPreview, , _
"Field1 IN (" & strChoice & ")"
Else

DoCmd.OpenReport "NameOfReport", acViewPreview
End If


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I created a pop-up form with a pull down list that I created by
typing
in
the
values. My thought was when they clicked on a button I would run a
query
that would match the selection with the field values in the
database.
Since
"BO & CCE" are two choices, I thought I could write an IIf statement
to
return both sets of records.

From what has been said in the message string, that doesn't seem
possible.

So...now I need to figure out a way to allow them to pick from the
list
and
return the record for those values.

I don't have any idea of how to create a variable string as you
suggest.
Any additional information would be appreciated.


:

If they've chosen All, you don't need a condition. If they've
chosen
some
other combination of conditions, you'll have to build a string
containing
the conditions of interest, and then use that variable. It's not
clear
to
me
how you're prompting them for their choice, so I really can't offer
any
advice on how to populate that variable, but assuming you've got a
variable
strChoice containing "'BO', 'CCE'", your condition would be

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN (" &
strChoice
& ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


That would work for the condition where the value is either BO or
CCE,
but
what about the other conditions: "BO"; "CCE", "SCAMPI, and "ALL"
(which
currently the user selects from a pop-up form?

Would I need to do a If statement within the "On Open" event?

Something like:
If form([formx]![field1]="BO" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
('BO')"
If form([formx]![field1]="CCE" then
DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
("CCE')"

ETC.
End If
end if






:

DoCmd.OpenReport "NameOfReport", acViewPreview, , "Field1 IN
('BO',
"CCE')"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That is exactly what I am attempting to do.

I had the IIf statement within the query.

How would I go about passing the criteria to the report at the
time
the
report is generated (as you suggest)?

:

I get it now. You've trying to create a criteria.

The problem with what you're trying to do is that you'll end
up
with a
condition like

Field1 = "BO" or "CCE"

which isn't valid (it needs to be Field1 = "BO" or Field1 =
"CCE".
That
means you cannot just put an IIf statement as a condition.

Presumably you're trying to do this for the query itself.
You'll
either
have
to dynamically generate the SQL for the query, or else don't
put
a
criteria
on the query, but pass a criteria when you open the report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I guess a better thing to ask would be:
 
Ad

Advertisements


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

Top