• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

SQL error in response to insert into statement at select line

Recieving an error message in VBA on this line of my sql statment in Access 2003 database.
"SELECT NewRecordtbl.ContactID, NewRecordtbl.NewID, NewRecordtbl.FirstName, NewRecordtbl.LastName, NewRecordtbl.MI, NewRecordtbl.Address, NewRecordtbl.City,"

Error is expected line number or label or statment or end of statement.

any ideas??
0
cruisie
Asked:
cruisie
  • 6
  • 6
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
post the complete sql view of your query

SELECT NewRecordtbl.ContactID, NewRecordtbl.NewID, NewRecordtbl.FirstName, NewRecordtbl.LastName, NewRecordtbl.MI, NewRecordtbl.Address, NewRecordtbl.City
From NewRecordtbl

if City is the last field of the query, remove the comma after City
0
 
cruisieAuthor Commented:
code snippet is attached
Private Sub Inactive_Click()
Dim stAppendInactive As String
 
stAppendInactive = "INSERT INTO Inactivetbl ( ContactID, NewID, FirstName, LastName, MI, Address, City, StateOrProvince, PostalCode, Region, Country, CompanyName, Title, WorkPhone, WorkExtension," & _
                   "HomePhone, MobilePhone, FaxNumber, EmailName, ContactTypeID, ReferredBy, Notes, Hometown, FEMACellPhone, HomeEmail, EmployeeID, Site_Deployed_to, Accreditation_Level, WorkAddress," & _
                   "Work_City, Work_State, WorkZipCode, EnteredOn, EnteredBy, UpdatedOn, UpdatedBy, Status, Date_Deployed, Inactive, InactiveOn, InactiveBy, NewVersionCheck, Waiver, CO, BPM, LIMS," & _
                   "LIMSMOBILE, APM, CS, ICS100, ICS200, ICS700, ICS800, TPM, WM, RF, CO_Date, BPM_Date, LIMS_Date, LIMSMOBILE_Date, APM_Date, CS_Date, ICS100_Date, ICS200_Date, ICS700_Date," & _
                   "ICS800_Date, TPM_Date, WM_Date, RF_Date, Instructor_CO, Instructor_BPM, Instructor_LIMS, Instructor_LIMSMOBILE, Instructor_APM, Instructor_CS, Instructor_TPM, Instructor_WM," & _
                   "APM_Begin_Date, APM_End_Date, TPM_Begin_Date, TPM_End_Date, WM_Begin_Date, WM_End_Date, RF_Begin_Date, RF_End_Date, Test_Score_CO, Test_Score_BPM, Test_Score_LIMS," & _
                   "Test_Score_MOBILE, Test_Score_APM, Material_CO, Material_BPM, Material_LIMS, Material_MOBILE, Material_APM, Orientation, CPPS_Certification_Date, CPPA_Certification_Date," & _
                   "CPPM_Certification_Date, CPPA_Certification_Date_Expiration, CPPM_Certification_Date_expires, CPPS, CPPA, CPPM, CO_Location, BPM_Location, LIMS_Location, Mobile_Location, APM_Location," & _
                   "TPM_Location, WM_Location, RF_Location, Participation_CO, Participation_BPM, Participation_LIMS, Participation_MOBILE, Participation_APM, Termination_date, Entitlements, Class_membership," & _
                   "No_Association, NPMA_Expiration_Date, Level_1, Level_2, Level_3, Level_4, [Nomination_letter Receive_date], Letter_orgin_DR, Accreditation_Level1_date, Accreditation_Level2_date," & _
                   "Accreditation_Level3_date, Accreditation_Level4_date, RO_Date, APO_Date, PMO_Date, RO_Location, APO_Location, PMO_Location, APO_certification_expiration_date, Waiver_Requestor," & _
                   "Waiver_date, Waiver_Expiration, Waiver_location, Waiver_Notes, Level1_Score, Level2_Score, Level3_Score, Level4_Score, Test_Date1, Test_Date2, Test_Date3, Test_Date4, APO, [LOG CHIEF], PMO, Disaster )"
                   "SELECT NewRecordtbl.ContactID, NewRecordtbl.NewID, NewRecordtbl.FirstName, NewRecordtbl.LastName, NewRecordtbl.MI, NewRecordtbl.Address, NewRecordtbl.City," & _
                   " NewRecordtbl.StateOrProvince, NewRecordtbl.PostalCode, NewRecordtbl.Region, NewRecordtbl.Country, NewRecordtbl.CompanyName, NewRecordtbl.Title, NewRecordtbl.WorkPhone," & _
                   " NewRecordtbl.WorkExtension, NewRecordtbl.HomePhone, NewRecordtbl.MobilePhone, NewRecordtbl.FaxNumber, NewRecordtbl.EmailName, NewRecordtbl.ContactTypeID," & _
                   " NewRecordtbl.ReferredBy, NewRecordtbl.Notes, NewRecordtbl.Hometown, NewRecordtbl.FEMACellPhone, NewRecordtbl.HomeEmail, NewRecordtbl.EmployeeID, NewRecordtbl.Site_Deployed_to," & _
                   " NewRecordtbl.Accreditation_Level, NewRecordtbl.WorkAddress, NewRecordtbl.Work_City, NewRecordtbl.Work_State, NewRecordtbl.WorkZipCode, NewRecordtbl.EnteredOn," & _
                   " NewRecordtbl.InactiveBy, NewRecordtbl.NewVersionCheck, NewRecordtbl.Waiver, NewRecordtbl.CO, NewRecordtbl.BPM, NewRecordtbl.LIMS, NewRecordtbl.LIMSMOBILE, NewRecordtbl.APM," & _
                   " NewRecordtbl.NewVersionCheck, NewRecordtbl.Waiver, NewRecordtbl.CO, NewRecordtbl.BPM, NewRecordtbl.LIMS, NewRecordtbl.LIMSMOBILE, NewRecordtbl.APM, NewRecordtbl.CS," & _
                   " NewRecordtbl.ICS100, NewRecordtbl.ICS200, NewRecordtbl.ICS700, NewRecordtbl.ICS800, NewRecordtbl.TPM, NewRecordtbl.WM, NewRecordtbl.RF, NewRecordtbl.CO_Date, NewRecordtbl.BPM_Date," & _
                   " NewRecordtbl.LIMS_Date, NewRecordtbl.LIMSMOBILE_Date, NewRecordtbl.APM_Date, NewRecordtbl.CS_Date, NewRecordtbl.ICS100_Date, NewRecordtbl.ICS200_Date, NewRecordtbl.ICS700_Date," & _
                   " NewRecordtbl.ICS800_Date, NewRecordtbl.TPM_Date, NewRecordtbl.WM_Date, NewRecordtbl.RF_Date, NewRecordtbl.Instructor_CO, NewRecordtbl.Instructor_BPM, NewRecordtbl.Instructor_LIMS," & _
                   " NewRecordtbl.Instructor_LIMSMOBILE, NewRecordtbl.Instructor_APM, NewRecordtbl.Instructor_CS, NewRecordtbl.Instructor_TPM, NewRecordtbl.Instructor_WM, NewRecordtbl.Instructor_RF," & _
                   " NewRecordtbl.Training_Notes, NewRecordtbl.SSN, NewRecordtbl.CO_Begin_Date, NewRecordtbl.CO_End_Date, NewRecordtbl.BPM_Begin_Date, NewRecordtbl.BPM_End_Date," & _
                   " NewRecordtbl.LIMS_Begin_Date, NewRecordtbl.LIMS_End_Date, NewRecordtbl.Mobile_Begin_Date, NewRecordtbl.Mobile_End_Date, NewRecordtbl.APM_Begin_Date, NewRecordtbl.APM_End_Date," & _
                   " NewRecordtbl.TPM_Begin_Date, NewRecordtbl.TPM_End_Date, NewRecordtbl.WM_Begin_Date, NewRecordtbl.WM_End_Date, NewRecordtbl.RF_Begin_Date, NewRecordtbl.RF_End_Date," & _
                   " NewRecordtbl.Test_Score_CO, NewRecordtbl.Test_Score_BPM, NewRecordtbl.Test_Score_LIMS, NewRecordtbl.Test_Score_MOBILE, NewRecordtbl.Test_Score_APM, NewRecordtbl.Material_CO," & _
                   " NewRecordtbl.Material_BPM, NewRecordtbl.Material_LIMS, NewRecordtbl.Material_MOBILE, NewRecordtbl.Material_APM, NewRecordtbl.Orientation, NewRecordtbl.CPPS_Certification_Date," & _
                   " NewRecordtbl.CPPA_Certification_Date, NewRecordtbl.CPPM_Certification_Date, NewRecordtbl.CPPA_Certification_Date_Expiration, NewRecordtbl.CPPM_Certification_Date_expires, NewRecordtbl.CPPS," & _
                   " NewRecordtbl.CPPA, NewRecordtbl.CPPM, NewRecordtbl.CO_Location, NewRecordtbl.BPM_Location, NewRecordtbl.LIMS_Location, NewRecordtbl.Mobile_Location, NewRecordtbl.APM_Location," & _
                   " NewRecordtbl.TPM_Location, NewRecordtbl.WM_Location, NewRecordtbl.RF_Location, NewRecordtbl.Participation_CO, NewRecordtbl.Participation_BPM, NewRecordtbl.Participation_LIMS," & _
                   " NewRecordtbl.Participation_MOBILE, NewRecordtbl.Participation_APM, NewRecordtbl.Termination_date, NewRecordtbl.Entitlements, NewRecordtbl.Class_membership, NewRecordtbl.No_Association," & _
                   " NewRecordtbl.NPMA_Expiration_Date, NewRecordtbl.Level_1, NewRecordtbl.Level_2, NewRecordtbl.Level_3, NewRecordtbl.Level_4, NewRecordtbl.[Nomination_letter Receive_date]," & _
                   " NewRecordtbl.Letter_orgin_DR, NewRecordtbl.Accreditation_Level1_date, NewRecordtbl.Accreditation_Level2_date, NewRecordtbl.Accreditation_Level3_date, NewRecordtbl.Accreditation_Level4_date, NewRecordtbl.RO_Date, NewRecordtbl.APO_Date, NewRecordtbl.PMO_Date, NewRecordtbl.RO_Location, NewRecordtbl.APO_Location, NewRecordtbl.PMO_Location," & _
                   " NewRecordtbl.APO_certification_expiration_date, NewRecordtbl.Waiver_Requestor, NewRecordtbl.Waiver_date, NewRecordtbl.Waiver_Expiration, NewRecordtbl.Waiver_location, NewRecordtbl.Waiver_Notes, NewRecordtbl.Level1_Score, NewRecordtbl.Level2_Score, NewRecordtbl.Level3_Score, NewRecordtbl.Level4_Score, NewRecordtbl.Test_Date1, NewRecordtbl.Test_Date2," & _
                   " NewRecordtbl.Test_Date3, NewRecordtbl.Test_Date4, NewRecordtbl.APO, NewRecordtbl.[LOG CHIEF], NewRecordtbl.PMO, NewRecordtbl.Disaster,FROM NewRecordtbl;"
            DoCmd.SetWarnings False
            DoCmd.RunSQL stAppendInactive
                   
 
 
 
 
 
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try this revision



Dim stAppendInactive As String
 
stAppendInactive = "INSERT INTO Inactivetbl ( ContactID, NewID, FirstName, LastName, MI, Address, City, StateOrProvince, PostalCode, Region, Country, CompanyName, Title, WorkPhone, WorkExtension," & _
                   "HomePhone, MobilePhone, FaxNumber, EmailName, ContactTypeID, ReferredBy, Notes, Hometown, FEMACellPhone, HomeEmail, EmployeeID, Site_Deployed_to, Accreditation_Level, WorkAddress," & _
                   "Work_City, Work_State, WorkZipCode, EnteredOn, EnteredBy, UpdatedOn, UpdatedBy, Status, Date_Deployed, Inactive, InactiveOn, InactiveBy, NewVersionCheck, Waiver, CO, BPM, LIMS," & _
                   "LIMSMOBILE, APM, CS, ICS100, ICS200, ICS700, ICS800, TPM, WM, RF, CO_Date, BPM_Date, LIMS_Date, LIMSMOBILE_Date, APM_Date, CS_Date, ICS100_Date, ICS200_Date, ICS700_Date," & _
                   "ICS800_Date, TPM_Date, WM_Date, RF_Date, Instructor_CO, Instructor_BPM, Instructor_LIMS, Instructor_LIMSMOBILE, Instructor_APM, Instructor_CS, Instructor_TPM, Instructor_WM," & _
                   "APM_Begin_Date, APM_End_Date, TPM_Begin_Date, TPM_End_Date, WM_Begin_Date, WM_End_Date, RF_Begin_Date, RF_End_Date, Test_Score_CO, Test_Score_BPM, Test_Score_LIMS," & _
                   "Test_Score_MOBILE, Test_Score_APM, Material_CO, Material_BPM, Material_LIMS, Material_MOBILE, Material_APM, Orientation, CPPS_Certification_Date, CPPA_Certification_Date," & _
                   "CPPM_Certification_Date, CPPA_Certification_Date_Expiration, CPPM_Certification_Date_expires, CPPS, CPPA, CPPM, CO_Location, BPM_Location, LIMS_Location, Mobile_Location, APM_Location," & _
                   "TPM_Location, WM_Location, RF_Location, Participation_CO, Participation_BPM, Participation_LIMS, Participation_MOBILE, Participation_APM, Termination_date, Entitlements, Class_membership," & _
                   "No_Association, NPMA_Expiration_Date, Level_1, Level_2, Level_3, Level_4, [Nomination_letter Receive_date], Letter_orgin_DR, Accreditation_Level1_date, Accreditation_Level2_date," & _
                   "Accreditation_Level3_date, Accreditation_Level4_date, RO_Date, APO_Date, PMO_Date, RO_Location, APO_Location, PMO_Location, APO_certification_expiration_date, Waiver_Requestor," & _
                   "Waiver_date, Waiver_Expiration, Waiver_location, Waiver_Notes, Level1_Score, Level2_Score, Level3_Score, Level4_Score, Test_Date1, Test_Date2, Test_Date3, Test_Date4, APO, [LOG CHIEF], PMO, Disaster )"
stAppendInactive = stAppendInactive & " SELECT ContactID, NewID, FirstName, LastName, MI, Address, City," & _
                   " StateOrProvince, PostalCode, Region, Country, CompanyName, Title, WorkPhone," & _
                   " WorkExtension, HomePhone, MobilePhone, FaxNumber, EmailName, ContactTypeID," & _
                   " ReferredBy, Notes, Hometown, FEMACellPhone, HomeEmail, EmployeeID, Site_Deployed_to," & _
                   " Accreditation_Level, WorkAddress, Work_City, Work_State, WorkZipCode, EnteredOn," & _
                   " InactiveBy, NewVersionCheck, Waiver, CO, BPM, LIMS, LIMSMOBILE, APM," & _
                   " NewVersionCheck, Waiver, CO, BPM, LIMS, LIMSMOBILE, APM, CS," & _
                   " ICS100, ICS200, ICS700, ICS800, TPM, WM, RF, CO_Date, BPM_Date," & _
                   " LIMS_Date, LIMSMOBILE_Date, APM_Date, CS_Date, ICS100_Date, ICS200_Date, ICS700_Date," & _
                   " ICS800_Date, TPM_Date, WM_Date, RF_Date, Instructor_CO, Instructor_BPM, Instructor_LIMS," & _
                   " Instructor_LIMSMOBILE, Instructor_APM, Instructor_CS, Instructor_TPM, Instructor_WM, Instructor_RF," & _
                   " Training_Notes, SSN, CO_Begin_Date, CO_End_Date, BPM_Begin_Date, BPM_End_Date," & _
                   " LIMS_Begin_Date, LIMS_End_Date, Mobile_Begin_Date, Mobile_End_Date, APM_Begin_Date, APM_End_Date," & _
                   " TPM_Begin_Date, TPM_End_Date, WM_Begin_Date, WM_End_Date, RF_Begin_Date, RF_End_Date," & _
                   " Test_Score_CO, Test_Score_BPM, Test_Score_LIMS, Test_Score_MOBILE, Test_Score_APM, Material_CO," & _
                   " Material_BPM, Material_LIMS, Material_MOBILE, Material_APM, Orientation, CPPS_Certification_Date," & _
                   " CPPA_Certification_Date, CPPM_Certification_Date, CPPA_Certification_Date_Expiration, CPPM_Certification_Date_expires, CPPS," & _
                   " CPPA, CPPM, CO_Location, BPM_Location, LIMS_Location, Mobile_Location, APM_Location," & _
                   " TPM_Location, WM_Location, RF_Location, Participation_CO, Participation_BPM, Participation_LIMS," & _
                   " Participation_MOBILE, Participation_APM, Termination_date, Entitlements, Class_membership, No_Association," & _
                   " NPMA_Expiration_Date, Level_1, Level_2, Level_3, Level_4, [Nomination_letter Receive_date]," & _
                   " Letter_orgin_DR, Accreditation_Level1_date, Accreditation_Level2_date, Accreditation_Level3_date, Accreditation_Level4_date, RO_Date, APO_Date, PMO_Date, RO_Location, APO_Location, PMO_Location," & _
                   " APO_certification_expiration_date, Waiver_Requestor, Waiver_date, Waiver_Expiration, Waiver_location, Waiver_Notes, Level1_Score, Level2_Score, Level3_Score, Level4_Score, Test_Date1, Test_Date2," & _
                   " Test_Date3, Test_Date4, APO, [LOG CHIEF], PMO, Disaster FROM NewRecordtbl;"
            DoCmd.SetWarnings False
DoCmd.RunSQL stAppendInactive

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
cruisieAuthor Commented:
well I didn't get an error so that's a good thing.  Please explain the purpose behind this  double statement.
0
 
Rey Obrero (Capricorn1)Commented:
too much  "& _ " will cause an error in the string...

another thing, are you inserting all the fields from NewRecordtbl to Inactivetbl ?

if you do it will be better to use

insert into Inactivetbl
select NewRecordtbl.*
from NewRecordtbl

0
 
cruisieAuthor Commented:
I am getting all kinds of errors with this statement.  even after I cut and paste the sql from the query.  what's the difference between this statement and the one I used before other then the size and simplicity?
0
 
aikimarkCommented:
NOTE: in order to use the "select NewRecordtbl.*" both tables need to have identical structures and column order.

You can also create a stored query in your MS-Access database and not worry about building the dynamic SQL.  Your program code size will shrink, the compile times will shorten, and you will have a simpler application to maintain.
0
 
Rey Obrero (Capricorn1)Commented:
copying an sql of a query and using it as statement in VBA is not that easy.
i normally break them in lines like this especially the long ones like what you got.

dim sql as string
sql="select f1,f2,f3,"
sql=sql & " f4,f5,f6,"
sql=sql & " f7,f8"
sql=sql & " from NameOftable"

to make it easier to read
0
 
cruisieAuthor Commented:
Breaking the lines into smaller segments is the easy part and I agree with you on making it easier to read.  However, the code I decided to use was the short version as your suggested.  any help would be greatly apprciated
0
 
Rey Obrero (Capricorn1)Commented:
(However, the code I decided to use was the short version as your suggested)

and, what is this code?
0
 
cruisieAuthor Commented:
stAppendInactive = "INSERT INTO Inactivetbl"
                   "SELECT NewRecordtbl.*" & _
                   "FROM NewRecordtbl;"
I keep getting errors
0
 
aikimarkCommented:

stAppendInactive = "INSERT INTO Inactivetbl " & _
                   "SELECT NewRecordtbl.* " & _
                   "FROM NewRecordtbl;"

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
you have to provide spaces

this is a one liner WILL WORK
stAppendInactive = "INSERT INTO Inactivetbl SELECT NewRecordtbl.* FROM NewRecordtbl;"


this is multi line

stAppendInactive = "INSERT INTO Inactivetbl " & _
                   " SELECT NewRecordtbl.*" & _
                   " FROM NewRecordtbl;"
0
 
cruisieAuthor Commented:
That worked thanks.  
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now