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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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
 

moradwan

New Member
Joined
Jul 24, 2009
Messages
40
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>
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
can you paste back the code?
what version of access are you running?
 

moradwan

New Member
Joined
Jul 24, 2009
Messages
40

ADVERTISEMENT

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
 

moradwan

New Member
Joined
Jul 24, 2009
Messages
40
Here is the code

The highlighted row is: DoCmd.RunSQL C24


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
    
        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
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559

ADVERTISEMENT

so you got past the db.openrecordset?
Paste back the code please
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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?
 

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,172
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,298
Messages
5,510,476
Members
408,791
Latest member
bwirth

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top