Help with the Append Query in VBA

[Yami86]

HELLO:
I need help with the Append Query in the Following Code:

Public Sub CreateForm()
Dim DateInv As String
Dim strQryDelete As String
Dim strQryAppend As String


DateInv = fnGetDate ' fnGetDate is a function that gets the date from an
input box (mmddaaaa)'
strQryDelete = "DELETE * FROM tblCourseReport"
strQryAppend = "INSERT INTO CursoInforme SELECT * FROM [" & DateInv & "]"

DoCmd.RunSQL strQryDate
DoCmd.RunSQL strQryAppend
End Sub

Note:
***DateInv is the variable that changes everyday. The table is name after the
variable for example: 04032009.
All the field values in DateInv is added to another table in the Current
Database which is tblCourseReport but I just don't know how to do this. Having problem with the syntax of the append query. The error says that there is a Syntax error in the From Clause.

Please help me to make this WORK.!!!!!!!!

Thanks

 

[James Dudden]

Hi,
Have you tried without the brackets?

SELECT * FROM " & DateInv

 

[Yami86]

yes I have, tried it without the brackets.

 

[MSAccessRookie]

yes I have, tried it without the brackets.

I am going to assume that because your variable represents a Table Name, it is a String. If I am correct, then try surrounding it with single quotes ( ' ). I also agree with James that the brackets should be optional, unless the string contains special characters or spaces, in which case, they will be required.

 

[James Dudden]

try surrounding it with single quotes ( ' ).

Following on from MSAccessRookie I think surrounding it in quotes is the next step as follows:

...SELECT * FROM '" & DATEINV & "'"

If this fails try adding fixed criteria instead of using a variable i.e. ...SELECT * FROM TABLENAME "

Another thought, I usually code a semi-colon at the end but am not 100% if this is needed or not but you could try:

...SELECT * FROM '" & DATEINV & "';"

Regards
JD

 

[Kafrin]

You do definitely need a semi-colon at the end.

I've never tried concatenating in a table name before, but since you don't want it to come through as a string within the query I think that probably your existing code is correct except for the semi-colon, so the last part should be:
& "];"

 

[evanscamman]

I would create the query in the query builder and make sure it is working there. Then paste the SQL statement into VBA, making sure to double up any instances of quotation marks. Once this is working it should be easy to substitute a variable for the table name.

Evan

 

[MSAccessRookie]

Am i correct in understanding that your variable is a date? If so, I believe it has to be prefixed and suffixed with a number sign.

Evan

It also my belief that he wants a date, and you are correct that he will need number signs ( # ) as delimiters. Good catch!

 

[evanscamman]

Actually, after rereading his posts, I'm not so sure that he is using a date.
He has a table that has been named with an 8 digit number that represents a date. He wants to lookup information in this table, but the table name will be dynamic - apparently he has loads of autogenerated tables. That's why I edited my previous post to explain how to do this.

I will add, however, that I think you are going about this the wrong way.
Why would you add tables - one for each date? Wouldn't it be better to have a single table with a date field. Then use a simple query to return only the records with a date that matches your criteria.

Evan