Dynamic table name

[Guinaba]

Hi guys,

Not sure what I am missing in the SQL statement below. The table has a dynamic name based on the date. I keep getting the error: Syntax error in FROM clause.

Any suggestion?

Dim dbs As Database, rst As Recordset, x As Integer

  Set dbs = CurrentDb
  
  strTableName = "tb0_AldiDailyScan" & "_" & Date
  
  Set rst = dbs.OpenRecordset("SELECT * FROM " & strTableName)

  If Not (rst.EOF And rst.BOF) Then
    Do While Not rst.EOF
       If rst.Fields(1).Value = "" Then 'Delete blank records
          rst.Delete
       End If
    rst.MoveNext                 
    Loop
    rst.Close
    Set rst = Nothing
    Set db = Nothing
  End If
End Sub

 

[Joe4]

How exactly is your table named?

Insert a MsgBox before that line to show yourself the table name your are building:

MsgBox strTableName

I am willing to bet that you will find that the name being built does not match your actual table name, because you probably need to format the date part of your table name first (as Date usually contains slashes, and I don't think Access likes using those in table names).

 

[Guinaba]

How exactly is your table named?

Insert a MsgBox before that line to show yourself the table name your are building:

MsgBox strTableName

I am willing to bet that you will find that the name being built does not match your actual table name, because you probably need to format the date part of your table name first (as Date usually contains slashes, and I don't think Access likes using those in table names).

Thanks @Joe4! Spot on! I've removed the slashes from the date and it worked!

 

[Joe4]

Thanks @Joe4! Spot on! I've removed the slashes from the date and it worked!

Excellent! Glad it worked out for you!

I use that method quite a bit when I am trying to build a dynamic value, and things don't work out as I planned. I will use a MsgBox to see what exactly I am building, and make sure that it matches my expectations.