Calculating an In('1','2','3'...) expression from values in a table

[MWKillebrew]

Hello,

I'm trying to create a tool for co-workers in my department to append data into a local Access database from an AS400 system. I'm trying to make this as user-friendly as possible for end users that won't be familiar or comfortable with adjusting the query SQL.

I have a passthrough query I've already written, but my problem is that I have an In() expression in the query that can vary depending on what the user criteria is. I've already tried to work around this by attempting to create an inner join between my local table (tbl_insco_criteria) and the AS400 file I'm querying, but this hasn't worked. Using a Select query linked to this Passthrough query to do this same action works, but takes considerably more time to generate due to the number of records involved, thus my attempt to find a way to calculate an updated In() expression.

I already have a VBA function that can perform a Find/Replace action to update the character string that needs to be updated, however, I'm having difficulty in writing a function that will calculate an updated In() expression from records entered into a table that can vary in the number of records entered, that can then be passed to my passthrough query using the Find/Replace function.

The table name is tbl_insco_criteria with a single column name of INSCO that is in Long Integer number format. An example of a string that might be generated is IN('305','306','380','800','801'). I would be placing the expression calculation into a form that would then be referenced by the Find/Replace function when that action is performed.

Is what I'm attempting to do even feasible? Any assistance would be appreciated.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

This will turn a column of data into an SQL IN clause:

Sub SQL_IN()

    Dim v1, v2, v3 ' All are variants
    
    ' Read used range of data into an array
    v1 = ActiveSheet.UsedRange

    ' Turn the 2D array into a 1D array
    v2 = Application.Transpose(v1)

    ' Create the IN clause
    v3 = "IN('" & Join(v2, "','") & "')"
    
    Debug.Print v3

End Sub

 

[Joe4]

Rick,

That looks like an Excel solution, not an Access one (note that this question is found in the Access forum, not the Excel one).

 

[RickXL]

It might be more useful as a function:

Function SQL_IN(r As Range) As Variant
    SQL_IN2 = "IN('" & Join(Application.Transpose(ActiveSheet.UsedRange), "','") & "')"
End Function

Sub test()
    Debug.Print SQL_IN(ActiveSheet.UsedRange)
End Sub

EDIT @Joe4: Then I don't suppose he will like the function either

 

[MWKillebrew]

I apologize for my ignorance, but I'm having difficulty implementing the function you provided (I was thinking the same thing as Joe4 on your initial response). I'm getting an error stating "Compile error: User defined type not defined" and highlights the following:

Function SQL_IN(r As Range) As Variant

Also, since this is supposed to be based on a field in a table, do I need to specify the table name and field where you have ActiveSheet.UsedRange, or should it be identifying this when I call the function to a query or form field?

 

[RickXL]

Sorry, it is my fault. I tend to assume that the queries in MrExcel will always be Excel ones.

So this is not an Access solution.

Joe4 was right, please accept my apologies.

Regards,

 

[Joe4]

Cross-posted: Calculating an In('1','2','3'...) expression from values in a table

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).