Insert into ..... Select statements.

[ADLC]

I am trying to run the following sql in MSAccess 2003, however, I get an
error 'Runtime error 3075' Syntax error (comma) in query expression '(
Asset_Info.AssetID , 'SYSADDEQ' , 'SYSTEM' , 'SYSTEM' , #03/03/2008 12:4200#)
'

mySQL = "INSERT INTO wf_history (permanent_id, wf_use, input_by, sponsor,
act_date) "
mySQL = mySQL + " SELECT( Asset_Info.AssetID , 'SYSADDEQ' , 'SYSTEM' ,
'SYSTEM' , " & Format(actDate, "\#mm/dd/yyyy hh:mm:ss\#") & " )"
mySQL = mySQL + " FROM Asset_Info "
mySQL = mySQL + " LEFT JOIN Assets ON Asset_Info.AssetID = Assets.
Permanent_id "
mySQL = mySQL + " WHERE Assets.Permanent_id Is Null "
mySQL = mySQL + " AND Asset_Info.AssetClass = 'equity' "
myDB.Execute mySQL

Can anyone advise how a beginner can learn to debug such an error.

Thank you.

 

[John Spencer]

I would add a Debug.Print statement to the code. And then copy the SQL
string from the immediate window and paste the SQL string into a new query.
Then I would try to run the query.

I think your problem may be the inclusion of ( ) around the fields in the
SELECT statement.

mySQL = "INSERT INTO wf_history " & _
"(permanent_id, wf_use, input_by, sponsor, act_date) "

mySQL = mySQL + " SELECT Asset_Info.AssetID , 'SYSADDEQ' , 'SYSTEM' ,
'SYSTEM' , " & Format(actDate, "\#mm/dd/yyyy hh:mm:ss\#") & " "
mySQL = mySQL + " FROM Asset_Info "
mySQL = mySQL + " LEFT JOIN Assets ON Asset_Info.AssetID = Assets.
Permanent_id "
mySQL = mySQL + " WHERE Assets.Permanent_id Is Null "
mySQL = mySQL + " AND Asset_Info.AssetClass = 'equity' "

Debug.Print mySQL

myDB.Execute mySQL


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[ADLC]

Many thanks for that.

I am having trouble with this one too: In the debug window It says "false
then some text of the query" then Runtime 3129 and Invalid SQL statement;
expected DELETE, INSERT, PROEDURE, SELECT or UPDATE. Can you help me
understand what this means. Thank you.

mySQL = "INSERT INTO Assets (Permanent_id, Asset_Type, Comp_name, Country,
Sector) "
mySQL = mySQL + " SELECT Asset_Info.AssetID, Asset_Info.AssetClass,
Format([Asset_info.AssetName]," < ") , "
mySQL = mySQL + " Asset_Info.CountryName, factset_gics_map.SectorGroup "
mySQL = mySQL + " FROM Asset_Info "
mySQL = mySQL + " LEFT JOIN Assets ON Asset_Info.AssetID = Assets.
Permanent_id "
mySQL = mySQL + " LEFT JOIN factset_gics_map ON Asset_Info.IndustryCode =
factset_gics_map.IndustryCode "
mySQL = mySQL + " WHERE Asset_Info.AssetClass = 'equity '"
mySQL = mySQL + " AND Assets.Permanent_id Is Null"

I would add a Debug.Print statement to the code. And then copy the SQL
string from the immediate window and paste the SQL string into a new query.
Then I would try to run the query.

I think your problem may be the inclusion of ( ) around the fields in the
SELECT statement.

mySQL = "INSERT INTO wf_history " & _
"(permanent_id, wf_use, input_by, sponsor, act_date) "

mySQL = mySQL + " SELECT Asset_Info.AssetID , 'SYSADDEQ' , 'SYSTEM' ,
'SYSTEM' , " & Format(actDate, "\#mm/dd/yyyy hh:mm:ss\#") & " "
mySQL = mySQL + " FROM Asset_Info "
mySQL = mySQL + " LEFT JOIN Assets ON Asset_Info.AssetID = Assets.
Permanent_id "
mySQL = mySQL + " WHERE Assets.Permanent_id Is Null "
mySQL = mySQL + " AND Asset_Info.AssetClass = 'equity' "

Debug.Print mySQL

myDB.Execute mySQL

I am trying to run the following sql in MSAccess 2003, however, I get an
error 'Runtime error 3075' Syntax error (comma) in query expression '(

[quoted text clipped - 16 lines]

Thank you.

 

[John Spencer]

mySQL = "INSERT INTO Assets " & _
"(Permanent_id, Asset_Type, Comp_name, Country,Sector) "

'===== To add quotes in a string you must double up on the quotes
'===== Also your brackets are wrong around Asset_Info.AssetName
mySQL = mySQL + " SELECT Asset_Info.AssetID, Asset_Info.AssetClass" & _
", Format([Asset_info].[AssetName],""< "") , "

'Alternative might be to use LCase(AssetName}

mySQL = mySQL + " Asset_Info.CountryName, factset_gics_map.SectorGroup "

' If you have more than two tables in the statement Access insists that you
use parentheses
'to pair the joins
mySQL = mySQL + " FROM (Asset_Info " & _
" LEFT JOIN Assets ON Asset_Info.AssetID = Assets.Permanent_id )" & _
" LEFT JOIN factset_gics_map " & _
" ON Asset_Info.IndustryCode =factset_gics_map.IndustryCode "

mySQL = mySQL + " WHERE Asset_Info.AssetClass = 'equity '"
mySQL = mySQL + " AND Assets.Permanent_id Is Null"

Most of that should have been visible if you used the Debug.print statement
and examined the string that was generated
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Many thanks for that.

I am having trouble with this one too: In the debug window It says "false
then some text of the query" then Runtime 3129 and Invalid SQL statement;
expected DELETE, INSERT, PROEDURE, SELECT or UPDATE. Can you help me
understand what this means. Thank you.

mySQL = "INSERT INTO Assets (Permanent_id, Asset_Type, Comp_name, Country,
Sector) "
mySQL = mySQL + " SELECT Asset_Info.AssetID, Asset_Info.AssetClass,
Format([Asset_info.AssetName]," < ") , "
mySQL = mySQL + " Asset_Info.CountryName, factset_gics_map.SectorGroup
"
mySQL = mySQL + " FROM Asset_Info "
mySQL = mySQL + " LEFT JOIN Assets ON Asset_Info.AssetID = Assets.
Permanent_id "
mySQL = mySQL + " LEFT JOIN factset_gics_map ON Asset_Info.IndustryCode
=
factset_gics_map.IndustryCode "
mySQL = mySQL + " WHERE Asset_Info.AssetClass = 'equity '"
mySQL = mySQL + " AND Assets.Permanent_id Is Null"

I would add a Debug.Print statement to the code. And then copy the SQL
string from the immediate window and paste the SQL string into a new
query.
Then I would try to run the query.

I think your problem may be the inclusion of ( ) around the fields in the
SELECT statement.

mySQL = "INSERT INTO wf_history " & _
"(permanent_id, wf_use, input_by, sponsor, act_date) "

mySQL = mySQL + " SELECT Asset_Info.AssetID , 'SYSADDEQ' , 'SYSTEM' ,
'SYSTEM' , " & Format(actDate, "\#mm/dd/yyyy hh:mm:ss\#") & " "
mySQL = mySQL + " FROM Asset_Info "
mySQL = mySQL + " LEFT JOIN Assets ON Asset_Info.AssetID = Assets.
Permanent_id "
mySQL = mySQL + " WHERE Assets.Permanent_id Is Null "
mySQL = mySQL + " AND Asset_Info.AssetClass = 'equity' "

Debug.Print mySQL

myDB.Execute mySQL

I am trying to run the following sql in MSAccess 2003, however, I get an
error 'Runtime error 3075' Syntax error (comma) in query expression '(

[quoted text clipped - 16 lines]

Thank you.

 

[ADLC]

Many thanks John.
Your advice has been very clear and helpful, I have learnt alot - coding is
not my dayjob.

mySQL = "INSERT INTO Assets " & _
"(Permanent_id, Asset_Type, Comp_name, Country,Sector) "

'===== To add quotes in a string you must double up on the quotes
'===== Also your brackets are wrong around Asset_Info.AssetName
mySQL = mySQL + " SELECT Asset_Info.AssetID, Asset_Info.AssetClass" & _
", Format([Asset_info].[AssetName],""< "") , "

'Alternative might be to use LCase(AssetName}

mySQL = mySQL + " Asset_Info.CountryName, factset_gics_map.SectorGroup "

' If you have more than two tables in the statement Access insists that you
use parentheses
'to pair the joins
mySQL = mySQL + " FROM (Asset_Info " & _
" LEFT JOIN Assets ON Asset_Info.AssetID = Assets.Permanent_id )" & _
" LEFT JOIN factset_gics_map " & _
" ON Asset_Info.IndustryCode =factset_gics_map.IndustryCode "

mySQL = mySQL + " WHERE Asset_Info.AssetClass = 'equity '"
mySQL = mySQL + " AND Assets.Permanent_id Is Null"

Most of that should have been visible if you used the Debug.print statement
and examined the string that was generated

Many thanks for that.

[quoted text clipped - 46 lines]