Can't get query criteria to read a form text field (1 Viewer)

[nortonm]

Hi - I have a list on a form that you can select multiple values, and when btn pressed it copies them to a text field. Then the other button should open the query with the contents of that text field in the criteria. But when you run the query it won't read directly from the form, and it brings up no values. However, If you copy the string into your clipboard and paste that exact same string manually into the criteria field and run the report, it finds all the values no problem! I've attached the screenshots. I've been at this 5 hours now, and I can't figure out what i'm doing wrong! Has anybody come across this inability to get the value from the text box?

On the 'Form' screenshot btn 'Copy List' code puts the values into the correct string (see 'Cohort List' in Form screenshot) to run the query (adding IN() and the "s.):

Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer

iCount = 0

If Me!lstCohort.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!lstCohort.ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & Me!lstCohort.ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = "In (" & """" & sTemp & """" & "," & """" & Me!lstCohort.ItemData(oItem) & """" & ")"
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If

Me!txtCohortList.Value = sTemp

 

[ebs17]

A listing is created in sTemp.
The criterion construct with IN requires a listing.
So: the beginning and end of the chain are consistent.

However, the content of a text field is exactly one value, here in the form of a string. When passed to this text field, the listing ceases to be a listing.

So something needs to change. There are various options for passing a multiple selection as a filter criterion.

Solved - Using either Global Variable or Form field as query criteria

Hi all, it's a number of years since I was a member here before. Back using Access/VBA/SQL after a long absence. I'm having trouble with getting a global variable and/or a form field value to work as a criteria in a query. I think it's because the values are an array. I'm getting the multi...

www.access-programmers.co.uk

 

[arnelgp]

    Dim sTemp As String
    Dim oItem As Variant

    For Each oItem In Me.lstChort.ItemsSelected
        sTemp = sTemp & ",""" & Me.lstChort.ItemData(oItem) & """"
    Next
    If Len(sTemp) Then
        sTemp = "In (" & Mid$(sTemp, 2) & ")"
    Else
        MsgBox "Nothing was selected from the list", vbInformation
    End If
    Me.txtCohortList = sTemp

 

[Gasman]

Could use Instr() perhaps?

SELECT tblDaily.DailyID, tblDaily.*, tblDaily.DailyID
FROM tblDaily
WHERE ((InStr(1,"3,5,6,8,9",[DailyID])>0));

 

[ebs17]

Could use Instr() perhaps?

Instr finds 1 not only in 1, but also in 21, 315, ... because it simply searches for a character or a substring.
Therefore, some additional precautions are necessary.

 

[Gasman]

Instr finds 1 not only in 1, but also in 21, 315, ... because it simply searches for a character or a substring.
Therefore, some additional precautions are necessary.

1 is the starting position?
However yes, extra characters like the , would be needed.

It was an example after all

 

[ebs17]

1 is the starting position?

Meant here as possible content of DailyID.

 

[nortonm]

Apologies - I've had a bad week - (been dumped!) - i'll feed back as soon as I can.

 

[nortonm]

Many thanks for your replies. I didn't manage to get the array to be added to the query design grid successfully, so for now I've had to resort to combo box and single values in the query. I'll try and pick up the thread when I have more time, i'll update if I sort it. Many Thanks.

 

[Pat Hartman]

Here's some examples that show how to use the In() clause.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk