Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3

    Answered: passing multiple strings to query criteria

    Hello all,

    I have a list box of text values. I am passing the selected values to a query criteria by using some VBA. I am saving the values to variables, and then I have a public function called "GetQueryParm()" that I have in the Criteria field of my query. If I only select one item from the list, it works fine. Once I select more than one item in the list, I then have to add in the OR between each item. I've tried adding additional quotes or parenthasis to the entire string, but it seem Access isn't interpreting the string correctly. It either sees 1062" Or "1013A" Or "1200A if I don't add the additional quotes. Then of course the syntax is incorrect. So then I add the quotes, and this is what I see in my Immediate window. "1062" Or "1013A" Or "1200A".
    You would think this should work, but I think access then adds another set of quotes to the outside creating ""1062" Or "1013A" Or "1200A"". Then of course that doesn't work either. I'm baffled how to pass this kind of string to my query. Any suggestions would be helpful.

    Thanks!

  2. Best Answer
    Posted by padraig

    "Hello,

    Just thought I'd update this with the solution I came up with. Thanks for all the help everyone.

    Dim intCounter As Integer
    Dim intIndex As Integer
    Dim varItem As Variant
    Dim strSQLSelect As String
    Dim strSQLHaving As String

    strSQLSelect = "SELECT qryExecutive1.BuildingNo, qryExecutive1.GrossSqFt, qryExecutive1.BuildingName, qryExecutive1.OverallRating, qryExecutive1.tbl2_2ComplianceObsolescence.Rating, qryExecutive1.tbl2_3FlexibilityPotentialForChange. Rating, qryExecutive1.tbl2_4CulturalHistorical.Rating, qryExecutive1.Total, qryExecutive3.SumOfCost, IIf([GrossSqFt]=0,Null,[Total]/[GrossSqFt]) AS Expr1, IIf([SumOfCost]=0,Null,[SumOfCost]/[Total]) AS HighPriorityCost FROM qryExecutive3 RIGHT JOIN qryExecutive1 ON qryExecutive3.BuildingNo = qryExecutive1.BuildingNo GROUP BY qryExecutive1.BuildingNo, qryExecutive1.GrossSqFt, qryExecutive1.BuildingName, qryExecutive1.OverallRating, qryExecutive1.tbl2_2ComplianceObsolescence.Rating, qryExecutive1.tbl2_3FlexibilityPotentialForChange. Rating, qryExecutive1.tbl2_4CulturalHistorical.Rating, qryExecutive1.Total, qryExecutive3.SumOfCost, IIf([GrossSqFt]=0,Null,[Total]/[GrossSqFt]), IIf([SumOfCost]=0,Null,[SumOfCost]/[Total]), qryExecutive3.Priority " _
    & "HAVING "

    intIndex = 0
    intCounter = Me!lstBuildingNo.ItemsSelected.count

    If Me!lstBuildingNo.ItemsSelected.count = 0 Then
    MsgBox "You didn't select anything."
    Else
    If intIndex <> intCounter - 1 Then
    For Each varItem In Me!lstBuildingNo.ItemsSelected
    If intIndex = 0 Then
    ' arrCount = 1 To intUpBound
    strSQLHaving = "(((qryExecutive1.BuildingNo)=" & Chr(34) & Me!lstBuildingNo.ItemData(varItem) & Chr(34) & "))"
    Else
    strSQLHaving = strSQLHaving & " Or ((qryExecutive1.BuildingNo)=" & Chr(34) & Me!lstBuildingNo.ItemData(varItem) & Chr(34) & ")"
    End If
    intIndex = intIndex + 1
    Next varItem
    Else
    For Each varItem In Me!lstBuildingNo.ItemsSelected
    strSQLHaving = "(((qryExecutive1.BuildingNo)=" & Chr(34) & Me!lstBuildingNo.ItemData(varItem) & Chr(34) & "))"
    Next varItem
    End If
    End If

    strSQL = strSQLSelect & strSQLHaving & ";"

    DoCmd.OpenReport "rptBuildingSummary", acViewPreview

    varItem = Empty"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    where mycolumn in ("1062", "1013A", "1200A")
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Quote Originally Posted by healdem
    where mycolumn in ("1062", "1013A", "1200A")
    I don't follow. When I paste that into the Criteria of the query to test, I get "Syntax error (comma) in query expression '(((qryExecutive1.BuildingNo)=("1062", "1013A", "1200A")))'.

  5. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When you build the string you can use single quotes instead of double quotes (' instead of ") or you can use Chr(34) which will not be interpreted by Access.

    Have a nice day!

  6. #5
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Quote Originally Posted by Sinndho
    When you build the string you can use single quotes instead of double quotes (' instead of ") or you can use Chr(34) which will not be interpreted by Access.

    Have a nice day!
    I've tried all these options. I still can't get more than one option to work. Any other ideas? Thanks for the suggestions though.

  7. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the where "in" clause works if you build the SQL dynamically, but you will need to use the full select syntax

    it wont work as you are trying
    I'd rather be riding on the Tiger 800 or the Norton

  8. #7
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Quote Originally Posted by healdem
    the where "in" clause works if you build the SQL dynamically, but you will need to use the full select syntax

    it wont work as you are trying
    I know it wont work as I'm trying. That's why I'm asking for help really.
    When you say "Build the SQL dynamically" what do you mean? Can you be more specific?

    I've tried putting an SQL statement into the report, and setting it to a variable. Then setting the variable to the Record Source property when the report opens. I get the same results though. When I choose one item, it works perfect. Any more than one, and nothing. For testing purposes, after the report opens, I have a Message box open up showing me the value that is being passed to the query, and it looks perfect.

  9. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    strSQL = "select my, column, list from mytable
    where mycolumn in (" & chr$(34) & "1062" & chr$(34) & ", " & chr$(34) & "1013A" & chr$(34) & ", " & chr$(34) & "1200A" & chr$(34) & ")"
    then open a recordset
    ///forget the code.... lookup the open recordset method for either ADO or DAO
    I'd rather be riding on the Tiger 800 or the Norton

  10. #9
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Hello,

    Just thought I'd update this with the solution I came up with. Thanks for all the help everyone.

    Dim intCounter As Integer
    Dim intIndex As Integer
    Dim varItem As Variant
    Dim strSQLSelect As String
    Dim strSQLHaving As String

    strSQLSelect = "SELECT qryExecutive1.BuildingNo, qryExecutive1.GrossSqFt, qryExecutive1.BuildingName, qryExecutive1.OverallRating, qryExecutive1.tbl2_2ComplianceObsolescence.Rating, qryExecutive1.tbl2_3FlexibilityPotentialForChange. Rating, qryExecutive1.tbl2_4CulturalHistorical.Rating, qryExecutive1.Total, qryExecutive3.SumOfCost, IIf([GrossSqFt]=0,Null,[Total]/[GrossSqFt]) AS Expr1, IIf([SumOfCost]=0,Null,[SumOfCost]/[Total]) AS HighPriorityCost FROM qryExecutive3 RIGHT JOIN qryExecutive1 ON qryExecutive3.BuildingNo = qryExecutive1.BuildingNo GROUP BY qryExecutive1.BuildingNo, qryExecutive1.GrossSqFt, qryExecutive1.BuildingName, qryExecutive1.OverallRating, qryExecutive1.tbl2_2ComplianceObsolescence.Rating, qryExecutive1.tbl2_3FlexibilityPotentialForChange. Rating, qryExecutive1.tbl2_4CulturalHistorical.Rating, qryExecutive1.Total, qryExecutive3.SumOfCost, IIf([GrossSqFt]=0,Null,[Total]/[GrossSqFt]), IIf([SumOfCost]=0,Null,[SumOfCost]/[Total]), qryExecutive3.Priority " _
    & "HAVING "

    intIndex = 0
    intCounter = Me!lstBuildingNo.ItemsSelected.count

    If Me!lstBuildingNo.ItemsSelected.count = 0 Then
    MsgBox "You didn't select anything."
    Else
    If intIndex <> intCounter - 1 Then
    For Each varItem In Me!lstBuildingNo.ItemsSelected
    If intIndex = 0 Then
    ' arrCount = 1 To intUpBound
    strSQLHaving = "(((qryExecutive1.BuildingNo)=" & Chr(34) & Me!lstBuildingNo.ItemData(varItem) & Chr(34) & "))"
    Else
    strSQLHaving = strSQLHaving & " Or ((qryExecutive1.BuildingNo)=" & Chr(34) & Me!lstBuildingNo.ItemData(varItem) & Chr(34) & ")"
    End If
    intIndex = intIndex + 1
    Next varItem
    Else
    For Each varItem In Me!lstBuildingNo.ItemsSelected
    strSQLHaving = "(((qryExecutive1.BuildingNo)=" & Chr(34) & Me!lstBuildingNo.ItemData(varItem) & Chr(34) & "))"
    Next varItem
    End If
    End If

    strSQL = strSQLSelect & strSQLHaving & ";"

    DoCmd.OpenReport "rptBuildingSummary", acViewPreview

    varItem = Empty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •