Append Query - Formula in Name not working

[Hillary]

I have an append query which I am going to use to append to different tables at different times. The tables always have the same format but different names. Then naming convention is ="tbl"&Date()&" K12" which results in tbl2/28/2005 K12

In the Append Query, under Query>Append Query>Table Name: I have ="tbl" & Date() & " K12". The problem is, when the system appends the data, it puts single quotes around the Table Name ('="tbl" & Date() & " K12"') which changes the formula to text.

Is there anyway to have an Append Query that appends to different tables based on a formula?

Thanks,

Hillary

 

[SkipVought]

Hi,

Date is a NUMBER and must be formatted into a STRING

="tbl"&Format(Date(),"mm/dd/yyyy")&" K12"

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[PHV]

I'd suggest to build and execute the INSERT INTO sql code with VBA.

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

 

[Hillary]

I've never used VBA.

Is there a way to modify the beginning of the SQL Statement? It begins, INSERT INTO [="tbl" & Date() & " K12"]

I tried removeing the brackets but that doesn't work. I'm looking for a way to force the formula inside the brackets to run.

Thanks again for your help,

Hillary

 

[Hillary]

Skip,

I have to respectfully disagree with you that the Date must be formatted into a string. I am using the formula exactly the same way in a Macro - Rename function - and it works there. I'm using Office 2003 SP1.

Would it work differently in a macro than a formula?


Hillary

 

[PHV]

You can't do that you want with the query designer.

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

 

[traingamer]

Is there anyway to have an Append Query that appends to different tables based on a formula?

No. You can use VBA, though.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SkipVought]

Then your lucky that vb is CONVERTING the date serial number into the text format that you need. I do not know all the assumptions that vb or Access or Excel makes when doing CONVERSIONS. I'd rather be in control of that.

in vba...

sSQL = "INSERT INTO tbl" & Format(Date(),"dd-mm-yyyy") & " K12 ........"

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}