VBA only appends on second run?

[pbaldy]

I've never seen that, but I'm glad you got it working. If you want to fix having the SQL in VBA, post the code here.

[schulzy175]

So I found where I was being tricked. All my action queries are based off my main query titled "PriorSales_Append". This query was never run first so therefore, none of my other action queries had sales to populate and append. I switched my code around to where "PriorSales_Append" is run first and sure enough I've been able to run it just fine everytime. I found this out because it was appending 0 rows on all my other action queries so that only lead me to "PriorSales_Append" to be run first. Note To Self!: Append your sales first :P

[schulzy175]

I've never seen that, but I'm glad you got it working. If you want to fix having the SQL in VBA, post the code here.

Gladly. When I come across a problem, I don't like to set it aside for another solution because I want to learn as much as I can.

So here is the SQL that Access had made.

INSERT INTO LMSB_Volume_Append ( MTH, SITENAME, [Count] )
SELECT LMSB_Volume_Q.Month, LMSB_Volume_Q.SITENAME, Sum(LMSB_Volume_Q.CountOfNAME) AS SumOfCountOfNAME
FROM LMSB_Volume_Q
GROUP BY LMSB_Volume_Q.Month, LMSB_Volume_Q.SITENAME;

I went ahead and readjusted the code for VBA as follows:

"INSERT INTO LMSB_Volume_Append ( MTH, SITENAME, [Count] )" & _
"SELECT LMSB_Volume_Q.Month, LMSB_Volume_Q.SITENAME, Sum(LMSB_Volume_Q.CountOfNAME) AS SumOfCountOfNAME" & _
"FROM LMSB_Volume_Q" & _
"GROUP BY LMSB_Volume_Q.Month, LMSB_Volume_Q.SITENAME;"

Again, the error I receive is "Query must contain at least one query or table." People have stated online that there may need to be extra spaces or some fields need to be in brackets because the field name may be the same as Access's Default name types. i.e. Month, Day, Name, etc. I adjusted the sql as much as I could see would need changing, but I could never get it to run.

[pbaldy]

Use this to see what the final SQL looks like:

http://www.baldyweb.com/ImmediateWindow.htm

Right off, you will end up with each line running into the next because you have no spaces either at the end or the beginning. In other words:

...SumOfCountOfNAMEFROM...

[schulzy175]

I used debug.print to test and check my sql, but it wouldn't wrap the text with delimiters?

[pbaldy]

I assumed you were setting a variable, so you'd debug that:

strSQL = "INSERT..."

debug.print strSQL