Interested in RecordSource functionality

[AccessIsEggs]

Hello,

If we were to take an array loaded with data, is it possible to filter the WHERE clause in a record source with this array?

ie

WHERE [Tools].[ToolID]=" & arrTooling(i) & ""

or something similar

 

[theDBguy]

Hi. Yes, that should be possible. Have you tried it?

 

[pbaldy]

I doubt it because the database engine can't resolve the array (you could do it for a single value from the array). You could loop through the array and build a comma delimited string variable, then:

"...WHERE [Tools].[ToolID] IN(" & VariableName & ")"

 

[Deleted member 147267]

I'm on the side of possible because this was posted in vba forum. If it was posted in queries (assuming there is such a forum) I'd say no. Should be an interesting outcome...

 

[AccessIsEggs]

I'm going to give the comma delimited string a try, I did try it the other way but it didn't work. I think that if we design the string to properly function like the WHERE clause does and then add the two strings together it should work.

 

[theDBguy]

I'm going to give the comma delimited string a try, I did try it the other way but it didn't work. I think that if we design the string to properly function like the WHERE clause does and then add the two strings together it should work.

Hi. I said "yes" because the sample code given was

WHERE [Tools].[ToolID]=" & arrTooling(i) & ""

, and I didn't see any problem with it. If the intent was something else, then I didn't pick it up. Sorry.


Edit: If the sample code given only included the array name without an index number, then I might have said no. For example:

WHERE [Tools].[ToolID]=" & arrTooling()

 

[Deleted member 147267]

I did try it the other way but it didn't work.

I/we must see that phrase 100x per month and yet it is of zero help. Maybe all you did wrong was not wrap quotes around text. This suggests to me that i is a string, but you have not delimited it.
WHERE [Tools].[ToolID]=" & arrTooling(i) & ""

 

[pbaldy]

I interpreted the question as wanting to filter on all values in the array, not a specific one. I don't think you could pass an array and filter on all values in the array without looping it into a string. I agree you could filter on a single value like the example, which is why I added "you could do it for a single value from the array".

 

[AccessIsEggs]

I apologize for any and all miscommunications I may have caused, it wasn't my intent.

I was able to get something functional by looping through my array and making a giant WHERE ... AND ... AND ... AND ... etc statement.

I appreciate the input from everyone.

 

[theDBguy]

I apologize for any and all miscommunications I may have caused, it wasn't my intent.

I was able to get something functional by looping through my array and making a giant WHERE ... AND ... AND ... AND ... etc statement.

I appreciate the input from everyone.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Mark_]

Something to remember about a WHERE clause, it can only see what is actually passed in the WHERE. An array in memory would reside on the computer it was created on. As the server itself may be on a different machine that machine would not normally be able to see the contents of an array directly. As such you need to either pass the server a complete text string of what you want to match to OR reference a query/table that can be joined to.

While your array itself can't be joined to, is there an underlying query or table that you can join to?

 

[Pat Hartman]

Building the In() clause would have been better. Think about the difference between these two results:

Where somefield In("a, b, c")

and

Where somefield in("a", "b", "c")

In the first case, you have one string with a value = "a, b, c" which is unlikely to match anything. In the second case, the In() contains THREE separate strings.

That is why you cannot do this using parameter substitution in a saved querydef but you can do it if you build the Where clause using VBA.

Here's an example of how to build the string:

    Me.txtSelectedSequences = Null
    For Each i In Me.lstSequence.ItemsSelected
        strIN = strIN & QUOTE & Me.lstSequence.ItemData(i) & QUOTE & ","
    Next i
    If strIN & "" = "" Then
        Me.txtSelectedSequences = Null
    Else
        strIN = Left(strIN, Len(strIN) - 1) ' remove trailing comma.
        Me.txtSelectedSequences = strIN
    End If

To make the code easier to read and not have bunches of "'s in packs, I use a constant named QUOTE whenever my intention is to insert into a string a single ".