Solved Using either Global Variable or Form field as query criteria

[GinnyR]

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 values from a list box and outputting the string value to a textbox. When I copy in the string value it works perfectly as the Criteria for the query, I know this is not good practice, but it's fulfilling a need (unless anyone has a better suggestion for getting multiple criteria into the query - I would be most grateful to hear of it). The array works as IN("Item1","Item2",Item3,ItemN), but when I add that string to a get_GlobalVariable() it doesn't work, nor getting the value directly from the Form textbox value. Any help with this would be appreciated.

 

[Gasman]

If the criteria came from more than one place, I tended to use a TempVar.

 

[561414]

but when I add that string to a get_GlobalVariable() it doesn't work, nor getting the value directly from the Form textbox value. Any help with this would be appreciated.

Are you sure the value is comitted before you attempt to access it?

 

[GinnyR]

Thanks 5 for the reply. Yes, I'm sure. I have the output on the form, and also when I run the code I have debug.print showing the value(s). I have this working fine with a single value committed to the textbox, but not when I have the string array. Yet the array works fine when I manually paste it into the criteria field.

 

[GinnyR]

If the criteria came from more than one place, I tended to use a TempVar.

Ok thanks for the suggestion Gasman, I'll have a look at that - didn't realise there was a difference. I have done all the F8 and F9 work, but nothing popping out at me.

 

[Gasman]

Start Debug.Print variables to see what you have.

 

[The_Doc_Man]

The question is, how often does that list change? And under what circumstances?

 

[Gasman]

I did it like this

strSql = strSql & " PIVOT qryWeekCallSummary.Caller IN (" & strCaller & ")"

strCaller came from
strCaller = PersonList()

Public Function PersonList() As String
    Dim lngCount  As Long
    Dim strSql    As String
    Dim strPeople As String
    giMaxPeople = 10
    
    
    strSql = " SELECT [CallerName]" & _
             " FROM tblCaller" & _
             " WHERE [Required] = True" & _
             " ORDER BY [CallerName]"
             
    With CurrentDb.OpenRecordset(strSql)
        Do Until .EOF
            lngCount = lngCount + 1
            If lngCount > giMaxPeople Then Exit Do
        
            strPeople = strPeople & Chr(34) & !CallerName & Chr(34) & ","
            .MoveNext
        Loop
    End With
    
    If Right(strPeople, 1) = "," Then
        PersonList = Left(strPeople, Len(strPeople) - 1)
    End If
    
End Function

Ignore the PIVOT option, that was for my use.

 

[GinnyR]

The question is, how often does that list change? And under what circumstances?

Doc_man the query will be used to organise the list of businesses that the rep will need to call on in the next couple of weeks. The query will use the County or Counties required to pull together the list (hence multiple values). This will be done through Forms, so that the user compiling the data won't have access to the backend, hence the use of listbox and textbox to use as criteria to send to the query. I hope I'm making sense. When I get a single value from the textbox using [Forms]![FormName]![ControlName] in the query criteria it works fine, but not with multiple values.

 

[GinnyR]

I did it like this

strSql = strSql & " PIVOT qryWeekCallSummary.Caller IN (" & strCaller & ")"

strCaller came from
strCaller = PersonList()

Public Function PersonList() As String
    Dim lngCount  As Long
    Dim strSql    As String
    Dim strPeople As String
    giMaxPeople = 10
   
   
    strSql = " SELECT [CallerName]" & _
             " FROM tblCaller" & _
             " WHERE [Required] = True" & _
             " ORDER BY [CallerName]"
            
    With CurrentDb.OpenRecordset(strSql)
        Do Until .EOF
            lngCount = lngCount + 1
            If lngCount > giMaxPeople Then Exit Do
       
            strPeople = strPeople & Chr(34) & !CallerName & Chr(34) & ","
            .MoveNext
        Loop
    End With
   
    If Right(strPeople, 1) = "," Then
        PersonList = Left(strPeople, Len(strPeople) - 1)
    End If
   
End Function

Ignore the PIVOT option, that was for my use.

Thanks Gasman, I'll try replicating the attached and see how I get on with it. I'm assuming you ended up with a string output from your function, what does your query criteria look like?
Thanks for going to so much trouble with this.
Ginny

 

[The_Doc_Man]

Doc_man the query will be used to organise the list of businesses that the rep will need to call on in the next couple of weeks. The query will use the County or Counties required to pull together the list (hence multiple values). This will be done through Forms, so that the user compiling the data won't have access to the backend, hence the use of listbox and textbox to use as criteria to send to the query. I hope I'm making sense. When I get a single value from the textbox using [Forms]![FormName]![ControlName] in the query criteria it works fine, but not with multiple values.

The part that doesn't make sense yet is that you want to have a list for which the "IN (item1, item2, ..., itemN)" syntax will be used, and you say (in post #1) that the items come from some type of array. But then you talk about sending criteria yet the user won't have access to the backend. You are asking about a listbox, but that works through a query just like forms use queries. My issue is how this list is communicated and when. And where, in the sense that a listbox needs a source, too.

 

[ebs17]

Are you using a list box with multiple selections in the form for a selection? This can only be evaluated using VBA. How accurate is your evaluation?

There are several ways to use a multiple selection in a filter, for example ...

WHERE FieldA IN ("A", "C", "E")

WHERE Instr(1, "|A|C|E|", "|" & FieldA & "|") > 0

WHERE FieldA IN (SELECT Param FROM ParameterTable)

It is certainly understandable that you have to formulate your selection in such a way that it can be used and function in the selected filter variant.
With an existing string array, variant 2 could be used.

Public Function MyChoice() As String
     MyChoice = "|" & Join(YourStringArray, "|") & "|"
End function

'------------------------------------------------

WHERE Instr(1, MyChoice(), "|" & FieldA & "|") > 0

In serious database environments, value will be placed on performance, even with larger amounts of data, and it is essential to enable index use there. These now offer variants 1 and 3, but not variant 2.

Additional aspect: An OR link of the multiple selection was considered here. With an AND link, things get a little more complex.

 

[cheekybuddha]

Hi it appears you were creating your list of values in a string an placing it in a form textbox or global variable.

If so, you will not be able to use the textbox reference directly as a query criteria.

eg your textbox called txtIDs contains: 1, 2, 3, 4, 5
You will not be able to use: "IN (" & [Forms]![YourForm]![txtIDs] & ")" as ther criteria.

You will need to reconstruct the SQL using VBA as shown above.

 

[CJ_London]

You will not be able to use: "IN (" & [Forms]![YourForm]![txtIDs] & ")" as ther criteria.

you can use the eval function

WHERE Eval([somefield] & " IN (" & [Forms]![YourForm]![txtIDs] & ")") = true

 

[cheekybuddha]

WHERE Eval([somefield] & " IN (" & [Forms]![YourForm]![txtIDs] & ")") = true

A cunning hack (if losing sargability is not an issue)

 

[CJ_London]

don't think we are talking huge amounts of data here

 

[GinnyR]

you can use the eval function

WHERE Eval([somefield] & " IN (" & [Forms]![YourForm]![txtIDs] & ")") = true

Yes, I have a listbox set to multivalue, a textbox and a button to run the query on the form. I would like to try this solution suggested. My problem is what exactly should I have in the textbox for the query criteria - [Forms]![YourForm]![txtIDs]. I can construct the textbox to contain it to be anything - I've tried "("Item1","Item2","ItemN")", I've tried ("Item1","Item2","ItemN") with the 'IN' in the SQL. I've tried (Item1,Item2,Item3) also this with spaces I've also tried the IN with each of these options. When I type in a single word with no quotes or parenthesis it works perfectly.

What is the 'somefield' in the Eval function?

 

[GinnyR]

don't think we are talking huge amounts of data here

Correct - not a lot of data

 

[CJ_London]

What is the 'somefield' in the Eval function?

you haven't provided any information regarding your actual field names, but using Gasmans, example, it would be Caller

txtIDs should appear as 'Item1','Item2','ItemN'

use single quotes because you are adding into a string which uses double quote delimiters

 

[GinnyR]

Hi it appears you were creating your list of values in a string an placing it in a form textbox or global variable.

If so, you will not be able to use the textbox reference directly as a query criteria.

eg your textbox called txtIDs contains: 1, 2, 3, 4, 5
You will not be able to use: "IN (" & [Forms]![YourForm]![txtIDs] & ")" as ther criteria.

You will need to reconstruct the SQL using VBA as shown above.

Yes, I have tried it a few different ways, putting the string values in form textbox and in a global variable and also in a global single field table. Would WHERE FieldA IN (SELECT Param FROM ParameterTable) work if I could put the values from the listbox into a table?