Help with varaible in sql select statment

[sna1maa14]

I want to create a table that will use todays date in the format of "tblmmm_yy" as the table name here is what i have. Im sure this is an easy fix but i just can't seem to get it.

Dim strTablename As String

strTablename = Format(Date, "mmm_yy")

DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT CenterNumber,DtReceived,TranDate,HeaderSubTotal,HeaderTax," & _
"CustFName,CustLName,Identifier,DisplayName,CACPrefixID,DetailID,SortField," & _
"Qty,UnitCost,SubTotal,ProfitCenter,State,PostalCode INTO tbl & strTablename" & _
"FROM dbo_camsWeekly WHERE (((Format(dbo_camsWeekly!DtReceived,'mm/yy'))=Format(Date(),'mm/yy')));"

Thanks,
snam1aa

 

[willir]

As shown, the SELECT statement will not create a table.

Review
SELECT ... Into
as in
SELECT * Into Table2
FROM Table1

Also the CreateDef method...

Dim dbs As DAO.Database, tblTemp As TableDef

Set tblTemp = dbs.CreateTableDef(YourTableName)
    
With tblTemp
     .Fields.Append .CreateField("pk_id", dbLong)
     .Fields.Append .CreateField("long_id", dbLong)
     .Fields.Append .CreateField("my_date", dbDate)
     .Fields.Append .CreateField("my_status", dbText, 15)
     .Fields.Append .CreateField("my_bigger_number", dbSingle)
     .Fields.Append .CreateField("comments", dbMemo)
End With            

dbs.TableDefs.Append tblTemp

 

[PHV]

Replace this:
"Qty,UnitCost,SubTotal,ProfitCenter,State,PostalCode INTO tbl & strTablename" & _
By this:
"Qty,UnitCost,SubTotal,ProfitCenter,State,PostalCode INTO tbl" & strTablename & " " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sna1maa14]

PHV,
Thanks that worked like a charm. I could have swore I tried that yesterday with the quotes but maybe I had one too may spaces or something. Who know after 8hrs of coding it all starts to look a like. By the way thank you for your helpful posts in the past. You and others have been a life saver for a newbie like me!