Nested IIf doesn't work

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

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!!!

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)

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: