Help editing VBA for Access

[AmandaF15]

So I was wondering if someone here could help me edit some VBA code for access.

Here is the code:

Public Function GTAS()
 
   Dim SBRLink2017 As DAO.Database
   Set SBRLink2017 = CurrentDb
  
   Dim delSQL As String
   Dim updSQL As String
   'Dim LinSQL As String
  
   DoCmd.SetWarnings False
  
   delSQL = "DELETE tbl_GTAS.* FROM tbl_GTAS';"
  
   DoCmd.RunSQL (delSQL)
  
Dim tdf As DAO.TableDef
 
Set db = CurrentDb
 
For Each tdf In db.TableDefs
 
    ' ignore system and temporary tables
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
 
        Dim sTable As String
        sTable = tdf.Name
 
        Dim strSQL
        strSQL = "INSERT INTO Tbl_GTAS ( SF133_Rpt_Line, LineDescription, LineAmt, TS)" & _
            " SELECT T.F1, T.F2, T.F3, '" [B]& Replace(sTable, "[_NEW SF 133]", "") & "' AS TS "[/B] & _
            "FROM [" & sTable & "] AS T " & _
            "GROUP BY T.F1, T.F2, T.F3,'" & Replace(sTable, "[_NEW SF 133]", "") & "';"
 
Debug.Print strSQL
        'do what you will with SQL
        DoCmd.RunSQL strSQL
    End If
 
Next
 
   updSQL = "UPDATE Tbl_GTAS SET Tbl_GTAS.TS_SF133_Rpt_Line = [TS] & '_' & [SF133_Rpt_Line];"
  
   DoCmd.RunSQL (updSQL)
  
'LinSQL = "DELETE Tbl_GTAS.LineAmt FROM Tbl_GTAS WHERE Tbl_GTAS.LineAmt Is Null OR Tbl_GTAS.LineAmt<0.001 And Tbl_GTAS.LineAmt>-0.001';"
  
   'DoCmd.RunSQL (LinSQL)
  
   DoCmd.SetWarnings True
   MsgBox ("The procedure is complete")
  
 End Function

I am specifically having trouble with this in the code: & Replace(sTable, "[_NEW SF 133]", "") & "' AS TS "


It is returning my TS like this:
Excel 2012

	A	B	C	D	E
4	TS	SF133_Rpt_Line	TS_SF133_Rpt_Line	LineAmt	LineDescription
5	75-1012-0943_NEW SF 133	1000	75-1012-0943_NEW SF 133_1000	$1,000,000.00	Description
6	75-1012-0943_NEW SF 133	1021	75-1012-0943_NEW SF 133_1021	$1,000,000.00	Description

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

When I really need it to show up like this: (without the _NEW SF 133)
Excel 2012

	A	B	C	D	E
4	TS	SF133_Rpt_Line	TS_SF133_Rpt_Line	LineAmt	LineDescription
5	75-1012-0943	1000	75-1012-0943_1000	$1,000,000.00	Description
6	75-1012-0943	1021	75-1012-0943_1021	$1,000,000.00	Description

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

Can anyone help me edit the replace function with something that will give me the TS only?

Thanks,
Amanda

 

[xenou]

Replace(sTable, "[_NEW SF 133]", "")


these means replace the text that says [_NEW SF 133] in sTable.

so it would work if the name of the table is SOME_TABLE[_NEW SF 133]


but it sounds like you are trying to change the values of records in the table, not the name of the table (and be careful that you are actually telling Access to match the brackets in the text, since you have [ and ] inside your quotes).

 

[xenou]

Possibly this is something more like what you want:

        strSQL = "INSERT INTO Tbl_GTAS ( SF133_Rpt_Line, LineDescription, LineAmt, TS)" & _
            " SELECT T.F1, T.F2, T.F3, Replace(T.TS, '_NEW SF 133', '') AS TS " & _
            "FROM [" & sTable & "] AS T " & _
            "GROUP BY T.F1, T.F2, T.F3, Replace(T.TS, '_NEW SF 133', '');"

Results:

INSERT INTO Tbl_GTAS ( SF133_Rpt_Line, LineDescription, LineAmt, TS) 
	SELECT 
	T.F1, 
	T.F2, 
	T.F3, 
	Replace(T.TS, '_NEW SF 133', '') AS TS 
FROM [Table1] AS T 
GROUP BY 
	T.F1, 
	T.F2, 
	T.F3, 
	Replace(T.TS, '_NEW SF 133', '')
;

but maybe you really mean:

        strSQL = "INSERT INTO Tbl_GTAS ( SF133_Rpt_Line, LineDescription, LineAmt, TS)" & _
            " SELECT T.F1, T.F2, T.F3, Replace(T.F4, '_NEW SF 133', '') " & _
            "FROM [" & sTable & "] AS T " & _
            "GROUP BY T.F1, T.F2, T.F3, Replace(T.F4, '_NEW SF 133', '');"

Result:

INSERT INTO Tbl_GTAS ( SF133_Rpt_Line, LineDescription, LineAmt, TS) 
	SELECT 
	T.F1, 
	T.F2, 
	T.F3, 
	Replace(T.F4, '_NEW SF 133', '') 
FROM [Table1] AS T 
GROUP BY 
	T.F1, 
	T.F2, 
	T.F3, 
	Replace(T.F4, '_NEW SF 133', '')
;

since your fields seems to be named F1, F2, F3, F4 ...

 

[AmandaF15]

So my tables are named 75-1012-0943_NEW SF 133. Each table has a different TS then the _NEW SF 133 (there are around 130 tables). These tables do not have the TS listed on them though so the data gets what TS they are from the table name. I get an error in my code when I try to leave the [] off my original code.

So I have added your piece of code to mine and it pops up and it gives me Runtime error 3464: Data type mismatch in criteria expression. Hitting debug highlights this line: DoCmd.RunSQL strSQL.

I also tried your code but leaving sTable after replace instead of T.F4 that however gives me a popup box for me to enter the TS into.

If this does not make much sense please tell me and I will try to explain myself better.

Thanks for the help!

 

[xenou]

What do you want your actual sql string to look like?

 

[AmandaF15]

I do not have a preference of what it looks like. I just need it to work.

 

[xenou]

I'll put it another way. Can you write one sql query that works. Without using vba?

 

[AmandaF15]

I guess I do not really understand. I am using sql in VBA to accomplish what I need in access. Do you need more background as to why the code is written like it is?

 

[xenou]

As a rule, you want to start by writing a sql command that works. Then convert that into your vba code. I don't think you've written this correctly and the vba is getting in the way since it's obscuring the problem.

 

[AmandaF15]

I am still relatively new at writing VBA so I was unsure of how to take my sql and make it into VBA.