Query results from a multi select list box.

[joebb3]

I'm at my wits end. I'm trying to get query results to match user selected in a multi select list box.

Here is what I have:

The list box is a list of statuses... (Below is an example)

A
B
C
D
E

The function

Function fun_ARStatus()
    Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Set frm = Forms!GenAlphaRoster
    Set ctl = frm!ARStatus
    PassARStatus = ""

    'Load the selected items into a string
    For Each varItm In ctl.ItemsSelected
        PassARStatus = PassARStatus & """" & ctl.ItemData(varItm) & """" & " Or "
    Next varItm

        'Get rid of trailing " Or "
        ARSLen = Len(PassARStatus)
        PassARStatus = Left(PassARStatus, ARSLen - 4)

    'Pass the final string to the query
    fun_ARStatus = PassARStatus

    Debug.Print fun_ARStatus
End Function

When A, B and D are selected, the debug output gives me: "A" Or "B" Or "D" (exactly what I want)

The criteria in the Query is: fun_ARStatus()
I have also tried: =fun_ARStatus()

When the query is run, I get NOTHING. If I put: "A" Or "B" Or "D" into the criteria MANUALLY, I get the correct query output!

What am I missing?!?! I have successfully used this method with other queries. The only difference here is I am using multiple selections.

Thanx in advance!
Joe

 

[JBinQLD]

Mate, your function has no return. Change the top line to:

Function fun_ARStatus() [red]as string[/red]

JB

 

[joebb3]

Thanx for the reply... But I'm still getting a blank query... Its driving me nuts!

 

[JBinQLD]

ok, rather than putting it in the criteria, what happens if you put it in as a column itself? That will show exactly what is being returned in query land

JB

 

[joebb3]

If I type: "A" Or "B" Or "D" into the query criteria, it works fine... I get all records with a status of A, B or D... If I use the function to pass it (the exact same string) the query returns nothing.

I'm not quite sure what you mean about putting in the the column itself.

Thanx!

 

[PHV]

If I use the function to pass it (the exact same string)
No, not the exact same string, but the following:
[!]"[/!]"A" Or "B" Or "D"[!]"[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

There we go, PHV has solved it for you. My idea would have alerted you to this same problem. Fo future reference, what I meant was, in query builder type fun_ArrStatus into the "Field" of the next blank column. Access would "correct" this to Expr1:fun_ArrStatus() but when you run the query you'd see exactly what your query interpretted as the result of your function.

Try it so you can see what I mean and perhaps it'll help debugging in future

JB

 

[PHV]

PHV has solved it for you
Certainly not.
I've just pointed out that you can't test for multiple values that way.

 

[joebb3]

After a few lines of coding, the Debug.Print output is now:
""A" Or "B" Or "D""

However the result is the same... a blank query.

What's bugging me is that I do this type of thing for several other reports generated by query and it works fine.

I've just never used the Multi Listbox to do it before.

Thax!
Joe

 

[PHV]

I've just never used the Multi Listbox to do it before
Again, you can't play with dynamics multivalues in the query grid.

 

[joebb3]

I'm not playing with them... Its static each time the query is run. It checks the list box to see which statuses are selected, creates the criteria string and displays the report listing the statuses slected. (Or that's what its supposed to do.

Are you saying that I can't do that?

Puzzled,
Joe

BTW: JB, I tried your suggestion and you are right... The correct string is being populated in the expr1: column.

Thanx

 

[JBinQLD]

Pls display your query in SQL view and paste it here mate

 

[PHV]

displays the report listing the statuses slected[/u]
Use the 4th argument of the DoCmd.OpenReport method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[joebb3]

Here is the query...

SELECT ContactInfo.id, ContactInfo.last, ContactInfo.first, ContactInfo.middle, ContactInfo.suffix, ContactInfo.rankrate, ContactInfo.email, ContactInfo.street1, ContactInfo.city, ContactInfo.state, ContactInfo.zip, ContactInfo.hphone, ContactInfo.wphone, ContactInfo.mphone, ContactInfo.cphone, ContactInfo.cphone2, ContactInfo.clast, ContactInfo.cfirst, ContactInfo.cmiddle, ContactInfo.crelation, ContactInfo.cemail, ContactInfo.cstreet, ContactInfo.ccity, ContactInfo.cstate, ContactInfo.czip, Mobilization.mob_Status, Mobilization.mob_dnd, Mobilization.mob_localmob
FROM ContactInfo INNER JOIN Mobilization ON ContactInfo.id = Mobilization.id
WHERE (((Mobilization.mob_Status)=fun_ARStatus()));

I'm also working on trying the 4th arg as suggested by PHV.

Thank you both!
Joe

 

[joebb3]

I see what you mean PHV...

I can put "A" into fun_ARStatus()
fun_ARStatus="A"
and it works... The problem comes with the OR... There is no way to set the Quotation Marks to get it to work. If I put:
fun_ARStatus= "A" Or "B"
I obviously get an error.
fun_ARStatus= ""A" Or "B""
No go...

hmmm....

 

[joebb3]

OK... Got it. Thanks for the Brain tweak guys...

Here is my solution.

Since the fun_ARStatus was report specific, I used:

Private Sub Report_Open(Cancel As Integer)
    Dim frm As Form, strMsg As String
    Dim strInput As String, strFilter As String
ARStatus = fun_ARStatus()
    strFilter = BuildCriteria("mob_Status", dbText, ARStatus)

Me.Filter = strFilter
Me.FilterOn = True

End Sub

on the report... I had to change the code of the function to output in the form of:

="A" or ="B" or ="D"

But it works fast and accurate.

Thanx again.