pass a list of values into an access query function

moradwan

New Member
Joined
Jul 24, 2009
Messages
40
Hello,
I have a query that I run on a table, "tbl", that has two columns, "col_A" and "col_B". I am running an AND query on the tbl where col_B is "EIS" and col_A has a match for an instr. Here is the query that creates tbl_B24 when the instr function using parameter "B24" returns a match on col_A.

SELECT tbl.[col_A], tbl.col_B INTO tbl_B24
FROM tbl
WHERE (((tbl.col_B)="EIS") And ((InStr(tbl![col_A],"B24"))>"0"));

My question is: Is there a way to run this query n times, where n is the length of list, creating n tables.

For instance:
((InStr(tbl![col_A],"C24"))>"0")) --> create table tbl_C24
((InStr(tbl![col_A],"D24"))>"0")) --> create table tbl_D24
((InStr(tbl![col_A],"E24"))>"0")) --> create table tbl_E24
((InStr(tbl![col_A],"F24"))>"0")) --> create table tbl_F24

Thanks,
radwan
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
radwan,
I believe the below module snippet will accomplish your task.
You can replace the strSQL$ with whatever you want in the resulting 'C24' table.

Code:
Sub CreateSubTables()
Dim db As Database
Dim rs As Recordset
DoCmd.SetWarnings 0
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl", dbOpenTable)
    Do While Not rs.EOF
    
        strSQL$ = "SELECT tbl.Col_A, tbl.Col_B INTO " & rs![Col_A] & " FROM tbl WHERE (((tbl.Col_A)='" & rs![Col_A] & "'))"
        DoCmd.RunSQL strSQL$
    
        rs.MoveNext
    Loop
DoCmd.SetWarnings 1
End Sub
 
Upvote 0
Ok thanks for the help.
I copied and pasted that code into the module section of the database. However, I get this error.

Run-time error '3219':

Invalid operation

and the highlighted row is this one:

Set rs = db.OpenRecordset("tbl", dbOpenTable)

</pre>
 
Upvote 0
ok
This is what I did. I replaced the strSQL$ with a C24. Now I am getting a diff error. This one says:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
thanks,
radwan
 
Upvote 0
Here is the code

The highlighted row is: DoCmd.RunSQL C24


PHP:
Sub CreateSubTables()
Dim db As Database
Dim rs As Recordset
DoCmd.SetWarnings 0
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl", dbOpenTable)
    Do While Not rs.EOF
    
        C24 = "SELECT tbl.Col_A, tbl.Col_B INTO " & rs![Col_A] & " FROM tbl WHERE (((tbl.Col_A)='" & rs![Col_A] & "'))"
        DoCmd.RunSQL C24

    
        rs.MoveNext
    Loop
DoCmd.SetWarnings 1
End Sub
 
Upvote 0
I just ran your code and it ran fine.
In your VBA Code area, go to tools/references.
Do you have a "Microsoft DAO ..." Library checked?
 
Upvote 0
Code:
Sub doit()
    
    Dim db As Database
    Dim rs As Recordset
    
    Dim sql As String
    Dim i As Integer
    Dim col_a_values As Variant
    
    col_a_values = Array("C24", "D24", "E24", "F24")
    
    Set db = CurrentDb()
    
    DoCmd.SetWarnings 0
    
    For i = 0 To UBound(col_a_values)
        sql = "SELECT count(*) " & vbCrLf
        sql = sql & "FROM tbl " & vbCrLf
        sql = sql & "WHERE ( ( tbl.col_B = 'EIS' ) And ( tbl.col_A like '*" & col_a_values(i) & "*' ) ) " & vbCrLf
        
        'MsgBox sql
        
        Set rs = db.OpenRecordset(sql)
        
        If rs(0) > 0 Then
            rs.Close
            sql = "SELECT tbl.CustomerName, tbl.CustomerID INTO tbl_" & col_a_values(i) & vbCrLf
            sql = sql & "FROM tblCustomer as tbl " & vbCrLf
            sql = sql & "WHERE ( ( tbl.CustomerID > 0 ) And ( tbl.CustomerName like '*" & col_a_values(i) & "*' ) ) " & vbCrLf
            
            'MsgBox sql
            
            db.Execute sql
        End If
    Next
    
    DoCmd.SetWarnings 1
    
    Set rs = Nothing
    Set db = Nothing
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top