Insert Into Using Variable

[sross81]

I have a SQL statement that executes and I also get an error saying that there is something wrong with the insert statement. I know it has to do with the variable I am using as one of the values to insert.

strAddCountTable = "INSERT INTO (FYQtrYear,FacilityID,FacilityCode,Fail,Pass,Measure) " & _
"SELECT sp_GenerateVarianceReportData_AMI10Counts.FYQtrYear, sp_GenerateVarianceReportData_AMI10Counts.FacilityID, " & _
"sp_GenerateVarianceReportData_AMI10Counts.FacilityCode, sp_GenerateVarianceReportData_AMI10Counts.Fail, " & _
"sp_GenerateVarianceReportData_AMI10Counts.Pass, " & "'" & strMeasure & "' " & _
"FROM sp_GenerateVarianceReportData_AMI10Counts;"

For the value of measure I want whatever is in the strMeasure Variable to insert, but no matter how I set up my quotes the table just won't take it. It should be inserting 'AMI10' below. The quotes don't matter I really just want AMI10. Any ideas?

debug.Print strAddCountTable
INSERT INTO (FYQtrYear,FacilityID,FacilityCode,Fail,Pass,Measure) SELECT sp_GenerateVarianceReportData_AMI10Counts.FYQtrYear, sp_GenerateVarianceReportData_AMI10Counts.FacilityID, sp_GenerateVarianceReportData_AMI10Counts.FacilityCode, sp_GenerateVarianceReportData_AMI10Counts.Fail, sp_GenerateVarianceReportData_AMI10Counts.Pass, 'AMI10' FROM sp_GenerateVarianceReportData_AMI10Counts;

 

[pbaldy]

That part looks fine. If you really have spaces in your field names, they must be bracketed.

 

[MarkK]

It doesn't look like you've specified the table to insert into.

INSERT INTO [B][COLOR="DarkRed"]tblTable1[/COLOR][/B]
   ( field1, field2 )
SELECT field1, field2 
   FROM tblTable2

 

[pbaldy]

Oh, and you didn't specify the table name:

INSERT INTO TableName(FYQtrYear...

 

[MarkK]

...seconds apart...
Hey Paul. How's it going?

 

[pbaldy]

Going great Mark; you? We've had a dry winter; all the storms seem to divert up your way and miss us. Send some down here, will ya?

 

[sross81]

That space was just a paste error.

Your right it was just the table name missing. I have done this same kind of query so many times. I can't believe I didn't see that. Thank you for catching that mistake for me!