Multiple selections on a list to be used in query field 1

[Triacona]

Dear All,

Thanks for a great forum!!

I have a queryCpXResults.

It has a field called AllUses.

In that fields criteria I have put the following code:

Like "*" & [Forms]![MainScreen].[ListCpXresult] & "*"

Multi Select is set to simple.

So I want to be able to select multiple codes on ListCpXresult, or just be able to leave it blank.

Unfortunately the above is not working, I select multiple values and it gives an output that is the same as if nothing is selected.

Am I doing this right?

Thank you for your help in advance


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

Here is the code for the private sub.

Private Sub CpRunListQuery_Click()
On Error GoTo Err_CpRunListQuery_Click

   Dim db As DAO.Database, qdf As DAO.QueryDef, SQL As String
   Dim Lbx As ListBox, idx, Pack As String
   
   Set db = CurrentDb
   Set qdf = db.QueryDefs("TestCpXResults")
   Set Lbx = ListCpXresult
   SQL = "SELECT UCPI.REFVAL, UCPI.TRADEAS, UCPI.ADDRESS AS [Add], " & _
                "UCPI.CPUSE AS MainUse, CpUse.CODETEXT AS MainUseDsc, UCPI.CONTACT, " & _
                "UCPU.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc " & _
         "FROM ((UNI7LIVE_CPINFO AS UCPI " & _
         "INNER JOIN CpUseCodes AS CpUse ON UCPI.CPUSE = CpUse.CODEVALUE) " & _
         "INNER JOIN UNI7LIVE_CPUSES AS UCPU ON UCPI.KEYVAL = UCPU.PKEYVAL) " & _
         "INNER JOIN CpUse AS CpUsesCodes ON UCPU.CPUSE = CpUsesCodes.CODEVALUE " & _
         "GROUP BY UCPI.REFVAL, UCPI.TRADEAS, UCPI.ADDRESS, UCPI.CPUSE, " & _
                  "CpUse.CODETEXT, UCPI.CONTACT, UCPU.CPUSE, " & _
                  "CpUsesCodes.CODETEXT, UCPI.CLOSEDD"



   For Each idx In Lbx.ItemsSelected
      If Pack <> "" Then
         Pack = Pack & " AND ((UNI7LIVE_CPINFO.CPUSE) Like '*" & Lbx.Column(1, idx) & "*')"
      Else
         Pack = "HAVING ((UCPU.CPUSE) Like '*" & Lbx.Column(1, idx) & "*')"
      End If
   Next
   
   If Pack <> "" Then
      Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
            "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   Else
      Pack = Pack & "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   End If
   
   Debug.Print SQL & Pack
   
   qdf.Close

   
   Set qdf = Nothing
   Set db = Nothing
   Set Lbx = Nothing
   


Exit_CpRunListQuery_Click:
    Exit Sub

Err_CpRunListQuery_Click:
    msgbox Err.Description
    Resume Exit_CpRunListQuery_Click
    
End Sub

Thanks!

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

My form MainScreen
Has A list - ListCp
The other List is ListCpXresult
Then there are a few buttons...

Name: Run Report Code Name:CpRunReport
Name: Run Query Code Name:CpRunQuery
Name: Output Query To Excel Code Name:CpOutputQryToExcel
Name: Output Query to XML Code Name:CpOutputQueryToXML


The user chooses on the list ListCp the report/query they wish to run and then clicks one of the above buttons.

I have added as I have said the Name: Run List Query button codename:CpRunListQuery
To run the code you have...
Is this correct or is there some way it would activate through a query?

Thanks again for all your brilliant help!


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

The debug message...

Syntax error (missing operator) in query expression 'UCPI.CLOSEDDORDER BY UNI7LIVE_CPINFO.TRADEAS'

This does not appear if UI select something on the List - ListCpXresult
is selected...
Then nothing happens...?

Thanks for your help!

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

When I checked the TestCpXResults query the SQL had changed according to the code you gave...

The only problem is that it cannot run because

The Microsoft Jet database engine cannot find the input table or query 'CPUse'. Make sure it exists and that its name is spelled correctly.

Below is the SQL of the newly code created query TestCpXresults

SELECT UCPI.REFVAL, UCPI.TRADEAS, UCPI.ADDRESS AS [Add], UCPI.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UCPI.CONTACT, UCPU.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc

FROM CpUse AS CpUsesCodes, (UNI7LIVE_CPINFO AS UCPI 
INNER JOIN CpUseCodes ON UCPI.CPUSE = CpUseCodes.CODEVALUE) 
INNER JOIN UNI7LIVE_CPUSES AS UCPU ON UCPI.KEYVAL = UCPU.PKEYVAL

GROUP BY UCPI.REFVAL, UCPI.TRADEAS, UCPI.ADDRESS, UCPI.CPUSE, CpUseCodes.CODETEXT, UCPI.CONTACT, UCPU.CPUSE, CpUsesCodes.CODETEXT, UCPI.CLOSEDD

HAVING((UCPU.CPUSE) Like '*In Scope*') And ((UNI7LIVE_CPINFO.CLOSEDD) Is Null)

ORDER BY UNI7LIVE_CPINFO.TRADEAS;

Hope this helps!
And thank you so much!

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

I have redone the SQL as follows:

SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS [Add], " & _
        "UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, " & _
        "UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc" & _
        "FROM ((UNI7LIVE_CPINFO " & _
        "INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE) " & _
        "INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) " & _
        "INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE" & _
        "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, " & _
        "CpUseCodes.CODETEXT , UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, " & _
        "CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD & _
        "HAVING (((UNI7LIVE_CPINFO.CLOSEDD) Is Null))" & _
        "ORDER BY UNI7LIVE_CPINFO.TRADEAS"

This query works but the VB code is giving me a syntax error.

Compile Error.
Syntax Error.

I hope this helps...
Thanks again


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear TheAceMan1,

hehe,

I fixed the syntax error, it now compiles...

SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS [Add], " & _
        "UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, " & _
        "UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc" & _
        "FROM ((UNI7LIVE_CPINFO " & _
        "INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE) " & _
        "INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) " & _
        "INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE" & _
        "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, " & _
        "CpUseCodes.CODETEXT , UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, " & _
[highlight red]        "CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD " & _[/highlight red]
        "HAVING (((UNI7LIVE_CPINFO.CLOSEDD) Is Null))" & _
        "ORDER BY UNI7LIVE_CPINFO.TRADEAS"

The red highlighted code was where the syntax error was ... it was just & _ without the quotes ".

Thanks

Thank you,

Kind regards

Triacona

 

[TheAceMan1]

[blue]The red highlighted code was where the syntax error was ... [/blue]

Really! You have three other missing spaces which should still give you a [blue]syntax error[/blue]. I've tagged all errors in [red]red[/red]:

[blue]SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS [Add], " & _
        "UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, " & _
        "UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc[COLOR=Black Red] [/color]" & _
        "FROM ((UNI7LIVE_CPINFO " & _
        "INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE) " & _
        "INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) " & _
        "INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE[COLOR=Black Red] [/color]" & _
        "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, " & _
        "CpUseCodes.CODETEXT , UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, " & _
        "CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD[COLOR=Blue Red] "[/color] & _
        "HAVING (((UNI7LIVE_CPINFO.CLOSEDD) Is Null))[COLOR=Black Red] [/color]" & _
        "ORDER BY UNI7LIVE_CPINFO.TRADEAS"[/blue]

This is the point of the [blue]Debug.Print[/blue] statement. [blue]You can easily check the syntax![/blue] ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Dear TheAceMan1,

Thanks I have corrected all the mistakes

It now creates the query with the following code...

Private Sub CpRunListQuery_Click()
On Error GoTo Err_CpRunListQuery_Click

   Dim db As DAO.Database, qdf As DAO.QueryDef, SQL As String
   Dim Lbx As ListBox, idx, Pack As String
   
   Set db = CurrentDb
   Set qdf = db.QueryDefs("TestCpXResults")
   Set Lbx = ListCpXresult

      SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS Add, " & _
            "UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, " & _
            "UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc " & _
            "FROM ((UNI7LIVE_CPINFO " & _
            "INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE) " & _
            "INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) " & _
            "INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE " & _
            "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, " & _
            "CpUseCodes.CODETEXT , UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, " & _
            "CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD "
            
   For Each idx In Lbx.ItemsSelected
      If Pack <> "" Then
         Pack = Pack & " AND ((UNI7LIVE_CPUSES.CPUSE) Like  '* " & Lbx.Column(0, idx) & " * ')"
      Else
         Pack = "HAVING ((UNI7LIVE_CPUSES.CPUSE) Like '* " & Lbx.Column(0, idx) & " *' )"
      End If
   Next
   
   If Pack <> "" Then
      Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
            "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   Else
      Pack = Pack & "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   End If
   
   Debug.Print SQL & Pack
   
   qdf.Close

   
   Set qdf = Nothing
   Set db = Nothing
   Set Lbx = Nothing
   


Exit_CpRunListQuery_Click:
    Exit Sub

Err_CpRunListQuery_Click:
    msgbox Err.Description
    Resume Exit_CpRunListQuery_Click
    
End Sub

The only problem now is that it inserts the criteria into the query with the incorrect syntax and therefore getting no results.
I have opened the query and in the AllUses: UNI7LIVE_CPUSES.CPUSE field the following is inserted:

Like '* INSCOP *' And Like '* SENSIT * '

As you can see the [red]'[/red] is a symbol that does not work in queries... is there another way to insert " so it will come up

Like [b][red]"[/red][/b]* INSCOP *[b][red]"[/red][/b] And Like [b][red]"[/red][/b]* SENSIT * [b][red]"[/red][/b]

Do I use triple quotes """ to use a " in the query?
Thanks again for all the help!


Thank you,

Kind regards

Triacona

 

[TheAceMan1]

[blue]The only problem now is that it inserts the criteria into the query with the incorrect syntax and therefore getting no results.[/blue]

[ol][li]For starters you've included improper spacing in the criteria:

[blue]      If Pack <> "" Then
         Pack = Pack & " AND ((UNI7LIVE_CPUSES.CPUSE) Like  '*[COLOR=Black Red] [/color]" & Lbx.Column(0, idx) & "[COLOR=Black Red] [/color]* ')"
      Else
         Pack = "HAVING ((UNI7LIVE_CPUSES.CPUSE) Like '*[COLOR=Black Red] [/color]" & Lbx.Column(0, idx) & "[COLOR=Black Red] [/color]*' )"
      End If[/blue]

These spaces are included in the search and the main reason why no records are returned![/li]
[li]I should've mentioned the [blue]HAVING[/blue] clause much earlier on. This clause is only used in a [blue]Sum[/blue] query and specifically in conjunction with the [blue]GROUP BY[/blue] clause. So this needs to be changed to a [blue]WHERE[/blue] clause.[/li]
[li]There's also a problem with the logic of using the [blue]AND[/blue] operator in the criteria. Since your pinging [blue]UNI7LIVE_CPUSES.CPUSE[/blue] against more than one value in the listbox, [blue]UNI7LIVE_CPUSES.CPUSE[/blue] can't be like more than one listbox value! So using [blue]AND[/blue] supports returning no records. More logical would be to use the [blue]OR[/blue] operator. You want to see the record if [blue]UNI7LIVE_CPUSES.CPUSE[/blue] is LIKE value1 OR value2 or value3 ... ect. So [blue]AND[/blue] should be changed to [blue]OR[/blue]:

[blue]   Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
            "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
should be
   Pack = Pack & " [purple][b]OR[/b][/purple] ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
            "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"[/blue]

[/li]
[li] It shouldn't matter, but there's an extra space here:

[blue]         Pack = Pack & " AND ((UNI7LIVE_CPUSES.CPUSE) Like [COLOR=black red] [/color]'* " & Lbx.Column(0, idx) & " * ')"[/blue]

... and a missing space here:

[blue]   Else
      Pack = Pack & "[COLOR=black red] [/color]ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   End If[/blue]

[/li]
[li]Single Quotes [red]'[/red] do work, as long as they don't exist in the field data. I've been using them for years. If they do exist in the field data then your forced to use double quotes [red]"[/red].[/li]
[li]You need to know your syntax is proper before you try anything else, so [blue]get these problems fixed 1st.[/blue][/li]
[li]BTW: remove the [blue]Debug.Print[/blue] line I gave you earlier and set them where you see them below:

[blue]   If Pack <> "" Then
      Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
            "ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      [purple][b]Debug.Print SQL & Pack[/b][/purple]
      qdf.SQL = SQL & Pack
   Else
      Pack = Pack & " ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      [purple][b]Debug.Print SQL & Pack[/b][/purple]
      qdf.SQL = SQL & Pack
   End If[/blue]

The lines print to the [blue]Immediate Window[/blue]. Here you can easily check that the syntax is proper. When all is good, remove or rem the lines.[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Triacona]

Dear TheAceMan1,

Thank you for all your help!

I have removed all the spaces, and added the ones you told me to.

If I use the WHERE clause in place of the HAVING it gives me this debug message.

Syntax Error(missing operator) in query expression UNI7LIVE_CPINFO.CLOSEDD WHERE((UNI7LIVE_CPUSES.CPUSE) like '*INSCOP*') AND ((UNI7LIVE_CPUSES.CPUSE) like '*SENSIT*') AND ((UNI7LIVE_CPINFO.CLOSEDD) IS NULL)

The HAVING quote seems to work...

I have implemented the [blue]OR[/blue] and it works!!!!

The whole thing now works! WHOOOOOOHOOOO!



Thank you for all your great and patient help!!

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear the AceMan1,


I have one last question.
What is the difference between a public and private sub?
Will a public sub, which contains the Query name, execute when that query is called to start by other button off a list?

Here is the code.

Private Sub CpRunListQuery_Click()
On Error GoTo Err_CpRunListQuery_Click

   Dim db As DAO.Database, qdf As DAO.QueryDef, SQL As String
   Dim Lbx As ListBox, idx, Pack As String
   
   Set db = CurrentDb
   Set qdf = db.QueryDefs("TestCpXResults")
   Set Lbx = ListCpXresult
 
    SQL = "SELECT UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS AS Add, " & _
            "UNI7LIVE_CPINFO.CPUSE AS MainUse, CpUseCodes.CODETEXT AS MainUseDsc, UNI7LIVE_CPINFO.CONTACT, " & _
            "UNI7LIVE_CPUSES.CPUSE AS AllUses, CpUsesCodes.CODETEXT AS AllUsesDsc " & _
            "FROM ((UNI7LIVE_CPINFO " & _
            "INNER JOIN CpUseCodes ON UNI7LIVE_CPINFO.CPUSE = CpUseCodes.CODEVALUE) " & _
            "INNER JOIN UNI7LIVE_CPUSES ON UNI7LIVE_CPINFO.KEYVAL = UNI7LIVE_CPUSES.PKEYVAL) " & _
            "INNER JOIN CpUseCodes AS CpUsesCodes ON UNI7LIVE_CPUSES.CPUSE = CpUsesCodes.CODEVALUE " & _
            "GROUP BY UNI7LIVE_CPINFO.REFVAL, UNI7LIVE_CPINFO.TRADEAS, UNI7LIVE_CPINFO.ADDRESS, UNI7LIVE_CPINFO.CPUSE, " & _
            "CpUseCodes.CODETEXT , UNI7LIVE_CPINFO.CONTACT, UNI7LIVE_CPUSES.CPUSE, " & _
            "CpUsesCodes.CODETEXT, UNI7LIVE_CPINFO.CLOSEDD "
            
   For Each idx In Lbx.ItemsSelected
      If Pack <> "" Then
         Pack = Pack & " OR ((UNI7LIVE_CPUSES.CPUSE) Like '*" & Lbx.Column(0, idx) & "*')"
      Else
         Pack = " HAVING ((UNI7LIVE_CPUSES.CPUSE) Like '*" & Lbx.Column(0, idx) & "*' )"
      End If
   Next
   
   If Pack <> "" Then
      Pack = Pack & " AND ((UNI7LIVE_CPINFO.CLOSEDD) Is Null) " & _
            " ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   Else
      Pack = Pack & " ORDER BY UNI7LIVE_CPINFO.TRADEAS;"
      qdf.SQL = SQL & Pack
   End If
   
   Debug.Print SQL & Pack
   DoCmd.OpenQuery ListCP, acViewNormal
   
   
   
   qdf.Close
   
    
   
   Set qdf = Nothing
   Set db = Nothing
   Set Lbx = Nothing
   


Exit_CpRunListQuery_Click:
    Exit Sub

Err_CpRunListQuery_Click:
    msgbox Err.Description
    Resume Exit_CpRunListQuery_Click
    
End Sub

Thank you for all your help!!

Thank you,

Kind regards

Triacona