Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Question Unanswered: Problem referencing form control from query

    Hey everyone. Maybe someone can lend me a hand.

    I have this query:
    Code:
    SELECT Site, [DOC Number], [Last Name], [First Name]
    FROM qryDsgINR_All
    WHERE Site In ([Forms]![Listbox Test]![txtSQL])
    ORDER BY Site;
    And I have this form module:
    Option Compare Database
    Option Explicit
    Dim SQLstring As String

    Private Sub btnClick_Click()
    Dim variable As String
    Dim i As Integer

    For i = 0 To Sites.ListCount - 1
    If Sites.Selected(i) Then
    variable = Sites.Column(1, i)
    Construct (variable)
    End If
    Next

    txtSQL.SetFocus
    txtSQL.Text = SQLstring


    DoCmd.OpenQuery "test", acViewNormal
    End Sub

    Private Sub Construct(ByVal Value As String)
    SQLstring = SQLstring + "'" & Value & "', "
    End Sub
    Pretty simple stuff. the SQLstring variable gathers the indexes selected in the list box, and the idea is to use those as the parameters for the IN clause in the query. So I click the button, walk thru the code and see that it's putting the string together correctly, stuff it into a text box control and think "ho ho ho I so smart", expecting the query to open up with the records for those selected sites.

    Wroooooongola. The query opens, but isn't returning any records. why. why. why. This method normally works for me.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  2. #2
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    bump...

    No body can think of why it's not working?
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What does the SQLstring contains?

    Have a nice day!

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. What is SQLstring?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    Quote Originally Posted by Sinndho
    What does the SQLstring contains?

    Have a nice day!
    The SQLstring contains a concatenated string comprised of a list of sites selected in the list box.

    It comes out like this: 'site1' , 'site2', 'site3', etc...

    Which is what need to go to the IN clause in the SQL query.

    When I print out the contents of the string in the Immediate window, copy it, and paste it into the IN clause in my query, it returns the results just fine. But when I try to reference to text box I put the string value into, I get nothing.

    I am thoroughly perplexed.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It's being evaluated as a scalar value instead of a set of values.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Exclamation

    Quote Originally Posted by Teddy
    It's being evaluated as a scalar value instead of a set of values.
    I'm sorry, that wasn't clear at all. What is "it", what do you mean a scalar value as opposed to a set of values. ARe you saying that the query doesn't recognize anything in the textbox it's referencing?

    Do you have any ideas as to what I ought to do about it?
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The query isn't constructed the way you think it is.

    Say you've got the following in your textbox:

    'value1', 'value2'

    Then a query:

    SELECT *
    FROM someTable
    WHERE someField IN (forms!yourForm!yourText)

    You're expecting that to be interpreted as:

    SELECT *
    FROM someTable
    WHERE someField IN ('value1', 'value2')

    But it's REALLY being interpreted as:

    SELECT *
    FROM someTable
    WHERE someField IN (" 'value1', 'value2' ")

    So now you're looking for someField to be equal to 'value1', 'value2' instead of either value1 OR value2.

    The whole criteria is being matched as a single value.

    Make sense?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97
    hooooooooo okay. That makes more sense... Now I just have to figure out how to fix it....Maybe...just not use the IN Clause and use OR... think that would work?...

    ...I'm gonna go try that... if I /phail again, I'm sure everyone in creation will know about it.

    ty ty

    Quote Originally Posted by Teddy
    The query isn't constructed the way you think it is.

    Say you've got the following in your textbox:

    'value1', 'value2'

    Then a query:

    SELECT *
    FROM someTable
    WHERE someField IN (forms!yourForm!yourText)

    You're expecting that to be interpreted as:

    SELECT *
    FROM someTable
    WHERE someField IN ('value1', 'value2')

    But it's REALLY being interpreted as:

    SELECT *
    FROM someTable
    WHERE someField IN (" 'value1', 'value2' ")

    So now you're looking for someField to be equal to 'value1', 'value2' instead of either value1 OR value2.

    The whole criteria is being matched as a single value.

    Make sense?
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  10. #10
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    /facepalm

    Well my second idea didn't work either... I'm at a loss here.

    Quote Originally Posted by Teddy
    The query isn't constructed the way you think it is.

    Say you've got the following in your textbox:

    'value1', 'value2'

    Then a query:

    SELECT *
    FROM someTable
    WHERE someField IN (forms!yourForm!yourText)

    You're expecting that to be interpreted as:

    SELECT *
    FROM someTable
    WHERE someField IN ('value1', 'value2')

    But it's REALLY being interpreted as:

    SELECT *
    FROM someTable
    WHERE someField IN (" 'value1', 'value2' ")

    So now you're looking for someField to be equal to 'value1', 'value2' instead of either value1 OR value2.

    The whole criteria is being matched as a single value.

    Make sense?
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I tend to favor temporary tables in this kind of scenario...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  12. #12
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Smile thanks

    Quote Originally Posted by Teddy
    I tend to favor temporary tables in this kind of scenario...
    Thanks for the suggestions. Fortunately for me, I've found a very good tutorial on the net that illustrates exactly what I wanted to do.

    God love Google, although as a general rule this is my first stop.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What manner of solution did you end up opting for?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Sep 2008
    Location
    NM, USA
    Posts
    97

    Talking The Solution!

    Here is my solution!~

    Code:
        
            Dim db As DAO.Database
            Dim qdf As DAO.QueryDef
            Dim varItem As Variant
            Dim strCriteria As String
            Dim strSQL As String
        
            Set db = CurrentDb()
            Set qdf = db.QueryDefs("qryBySite")
        
            For Each varItem In Me.lstSites.ItemsSelected
                strCriteria = strCriteria & ", '" & Me.lstSites.ItemData(varItem) & "'"
            Next varItem
        
            If Len(strCriteria) = 0 Then
                MsgBox "You did not select anything from the list" _
                , vbExclamation, "Nothing to find!"
                Exit Sub
            End If
        
            strCriteria = Right(strCriteria, Len(strCriteria) - 1)
        
            strSQL = "SELECT  Site, [DOC Number], [Last Name], [First Name], [Collection Date], [Mg/Week], [INR], [Action Taken] FROM qryDsgINR_All " & _
                 "WHERE qryDsgINR_All.Site IN(" & strCriteria & ");"
        
            qdf.SQL = strSQL
            DoCmd.OpenReport "repDsgINR_Sites", acViewPreview
                   
        
            Set db = Nothing
            Set qdf = Nothing
    Why didn't I think of that before... /ponder.
    "Unix is user friendly... It's just very picky about its friends."

    The best things in life...are well-documented.

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ah, built the whole string in advance.

    That works.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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