469,301 Members | 2,278 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

How to "Insert into" variable table name?

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
4 7836
beacon
579 512MB
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
579 512MB
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
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
579 512MB
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.

Similar topics

2 posts views Thread by newbie_mw | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.