Using an Append query in VBA to change the field name provides theSyntax Error 3075

  • Thread starter Thread starter Leanne Everingham
  • Start date Start date
L

Leanne Everingham

I am trying to run multiple append queries using SQL to change the field names required for each value in my records. I am in the progress of appending the field name as a value in a table and then will use a while loop to insert the field name into a types column of the receiving table. I am not sure what is wrong with my syntax, my code is as follows:

Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo err_Handler

Dim MyDB As Database, rs As Recordset
Dim fldName As String

Set MyDB = CurrentDb
DoCmd.SetWarnings False
fldName = "Solids"

DoCmd.RunSQL ("INSERT INTO tmpSampleReport ( [DateTime], [Sample Point], Val, Area )" & _
"SELECT SampleDataCollect.DateTime, SampleDataCollect.SamplePointName, SampleDataCollect." & fldName & ", 'The missing operator is somewhere here:
Area.AreaName" & _
"FROM SampleDataCollect INNER JOIN Area ON SampleDataCollect.AreaID = Area.AreaID" & _
"WHERE ((SampleDataCollect.DateTime)>DateAdd(7,-1,Date()) AND (SampleDataCollect." & fldName & ") Is Not Null;")


EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx
 
For one thing you are missing spaces in the query string.

"INSERT INTO tmpSampleReport ( [DateTime], [Sample Point], Val, Area )" & _
" SELECT SampleDataCollect.DateTime, SampleDataCollect.SamplePointName,
SampleDataCollect." & fldName & ", Area.AreaName" & _
" FROM SampleDataCollect INNER JOIN Area ON SampleDataCollect.AreaID =
Area.AreaID" & _
" WHERE SampleDataCollect.DateTime>DateAdd(""d"",-1,Date()) AND
SampleDataCollect." & fldName & " Is Not Null"

For another DateAdd as far as I know should read
DateAdd("d",-1,Date())
And to insert that into the string you are building you need to double the quotes.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
And I missed that you are using VAL which is a reserved word, so I would
expect you need to bracket it - [Val] - to avoid an error.

For the future:
A good trouble shooting technique is to use a string variable and assign the
SQL statement to the string. That way you can use Debug.Print strSQL in your
code to examine the string that is generated.

If you get an error, you can copy the generated string and paste it into blank
query and attempt to run it. Often this technique will show you exactly where
the error is.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John said:
For one thing you are missing spaces in the query string.

"INSERT INTO tmpSampleReport ( [DateTime], [Sample Point], Val, Area )" & _
" SELECT SampleDataCollect.DateTime, SampleDataCollect.SamplePointName,
SampleDataCollect." & fldName & ", Area.AreaName" & _
" FROM SampleDataCollect INNER JOIN Area ON SampleDataCollect.AreaID =
Area.AreaID" & _
" WHERE SampleDataCollect.DateTime>DateAdd(""d"",-1,Date()) AND
SampleDataCollect." & fldName & " Is Not Null"

For another DateAdd as far as I know should read
DateAdd("d",-1,Date())
And to insert that into the string you are building you need to double
the quotes.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Leanne said:
I am trying to run multiple append queries using SQL to change the
field names required for each value in my records. I am in the
progress of appending the field name as a value in a table and then
will use a while loop to insert the field name into a types column of
the receiving table. I am not sure what is wrong with my syntax, my
code is as follows:

Option Compare Database
Option Explicit

Private Sub Form_Load()
On Error GoTo err_Handler

Dim MyDB As Database, rs As Recordset
Dim fldName As String

Set MyDB = CurrentDb
DoCmd.SetWarnings False
fldName = "Solids"

DoCmd.RunSQL ("INSERT INTO tmpSampleReport ( [DateTime], [Sample
Point], Val, Area )" & _
"SELECT SampleDataCollect.DateTime, SampleDataCollect.SamplePointName,
SampleDataCollect." & fldName & ", 'The missing operator is somewhere
here:
Area.AreaName" & _
"FROM SampleDataCollect INNER JOIN Area ON SampleDataCollect.AreaID =
Area.AreaID" & _
"WHERE ((SampleDataCollect.DateTime)>DateAdd(7,-1,Date()) AND
(SampleDataCollect." & fldName & ") Is Not Null;")


EggHeadCafe - Software Developer Portal of Choice WCF Workflow
Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx
Click to expand...
Click to expand...
 
Back
Top