By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,871 Members | 605 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,871 IT Pros & Developers. It's quick & easy.

How to "Insert into" variable table name?

P: 2
Hi
I have a database which archives data by financial year. I want to be able to run a query and append the data to a table which is named after the financial year, expendituresFY. The problem I have is I get a syntax error when I try and pass a variable with INSERT INTO. Is there another way of doing this? I'm pretty new to this so I'm probably missing something simple.

Expand|Select|Wrap|Line Numbers
  1. Dim FY As Integer
  2. Dim strDataUpdate As String
  3. Dim ExpendituresFY As String
  4.  
  5. If month(Now()) > 3 Then
  6.     FY = year(Now())
  7. Else:
  8.     FY = year(DateAdd("yyyy", -1, Date))
  9. End If
  10. ExpendituresFY = "Expenditures " & FY
  11. strDataUpdate = "INSERT INTO " & ExpendituresFY & ( Items, OrderDate, Requisition, Company ) " & _
  12.                "SELECT DISTINCT [long condition]...." & _
  13.                "FROM TempExp;"
  14. DoCmd.RunSQL (strDataUpdate)
  15.  
If I don't use a variable tablename then this executes with no errors.

Thanks

James
Dec 14 '10 #1
Share this Question
Share on Google+
4 Replies

beacon
100+
P: 579
Hi James,

I'm not promising this will work, but have you tried changing the data type for ExpendituresFY to Variant?

-beacon
Dec 14 '10 #2

beacon
100+
P: 579
If that doesn't work, I found a post in this forum that looks like it may be what you need. They aren't using INSERT INTO, but it's still SQL, so it should work.

http://bytes.com/topic/access/answer...es-table-names
Dec 14 '10 #3

P: 2
Thanks so much for the quick reply - I've been trying to get this to work for hours. What worked was using [ ] as follows:
Expand|Select|Wrap|Line Numbers
  1. strDataUpdate = "INSERT INTO [" & ExpendituresFY & "]( Items, OrderDate, Requisition, Company ) "
  2.  
I just wish I'd asked earlier. I was having no luck with google. Thanks again

James
Dec 14 '10 #4

beacon
100+
P: 579
Glad it worked! Learned something myself...which is always a good thing. =)
Dec 14 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.