Query Error

G

Guest

I have the following query:


Dim strSQLAppendBKs As String


strSQLAppendBKs = "INSERT INTO tbl_masterpop " _
& "[loan acct #], popenterdt, Status, " _
& "PrevStatus, [Buying Center #], [Division Name], [Region Name], [Group
Name], " _
& "[Collection Center Name], [Distribution Channel], [New Institution Name],
" _
& "[Origination Channel] , [Sub Channel], [Short Name], [Loan Status],
[Contract Date], [Mat date], " _
& "[Next Pmt Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made],
" _
& "[# Reg Pmts Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last
Pmt Amt], [Amended?], [Amended This Mo?], [DEL Group], " _
& "[Days Delinquent], [Tot Past Due Pmts $], [Principal Balance], [Val
Reserve Dollar Amt], " _
& "NETBAL, [Ext?], [Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth
of Orig], [Active BK Flag Date], [Active BK Flag], [Inventory Status],
[Charge Off Principal], " _
& "[Chg Off Type], [CHG OFF Date], [Cure Letter Date] , [Skip Active Date],
[Skip Completion Date], [Skip Completion Sts], " _
& "[Redeemed Date], [Entered Date], [Repo Ordered Date], [Active Inactive
Flag] " _
& "SELECT " _
& "Tbl_resolved2[Loan Acct #], Tbl_resolved2PopEnterDt,
Tbl_resolved2ResolvedStatus, " _
& "Tbl_resolved2PrevStatus, Tbl_resolved2.[Buying Center #] ,
Tbl_resolved2.[Division Name], Tbl_resolved2.[Region Name],
Tbl_resolved2.[Group Name], Tbl_resolved2.[Collection Center Name], " _
& "Tbl_resolved2.[Distribution Channel], Tbl_resolved2.[New Institution
Name] , Tbl_resolved2.[Origination Channel], " _
& "Tbl_resolved2.[Sub Channel], Tbl_resolved2.[Short Name],
Tbl_resolved2.[Loan Status], Tbl_resolved2.[Contract Date],
Tbl_resolved2.[Mat date], Tbl_resolved2.[Next Pmt Due Date],
Tbl_resolved2.[Payoff Date]," _
& "Tbl_resolved2.[Paid Off?], Tbl_resolved2.[Nbr of Reg Pmts Made],
Tbl_resolved2.[# Reg Pmts Remaining], " _
& "Tbl_resolved2.[Pmt Scd Monthly Pmt Amt], Tbl_resolved2.[Last Pmt Date],
Tbl_resolved2.[Last Pmt Amt], Tbl_resolved2.[Amended?],
Tbl_resolved2.[Amended This Mo?], Tbl_resolved2.[DEL Group],
Tbl_resolved2.[Days Delinquent], " _
& "Tbl_resolved2.[Tot Past Due Pmts $], Tbl_resolved2.[Principal Balance],
tbl_resolved2.[Val Reserve Dollar Amt] , tbl_resolved2.NETBAL,
tbl_resolved2.[Ext?], tbl_resolved2.[Extended This Mo?],
tbl_resolved2.[Payoff Dollar Amt], tbl_resolved2.[Flat Void Mth of Orig], " _
& "tbl_resolved2.[Active BK Flag Date], tbl_resolved2.[Active BK Flag],
tbl_resolved2.[Inventory Status], tbl_resolved2.[Charge Off Principal],
tbl_resolved2.[Chg Off Type], tbl_resolved2.[CHG OFF Date], " _
& "tbl_resolved2.[Cure Letter Date], tbl_resolved2.[Skip Active Date],
tbl_resolved2.[Skip Completion Date], tbl_resolved2.[Skip Completion Sts],
tbl_resolved2.[Redeemed Date], tbl_resolved2.[Entered Date],
tbl_resolved2.[Repo Ordered Date], tbl_resolved2.[Active Inactive Flag], " _
& "FROM Tbl_resolved2 " _
& "WHERE popenterdt = " _
& " (SELECT min(popenterdt) " _
& " FROM tbl_resolved WHERE tbl_resolved.[loan acct #] =
tbl_resolved2.[loan acct #]);"


DoCmd.RunSQL strSQLAppendBKs

but I am getting an error message:
runtime error 3134: SYNTAX ERROR IN INSERT INTO statement.

what I am trying to do with the above query is append unique loan account
numbers, with the first [popenterdt] for that [loan acct #] (as there are
more than one instance of the [loan acct #]) into the tbl_masterpop.

thanks in advance,
geebee
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need parentheses around the INSERT INTO column list. Syntax is:

INSERT INTO <table name> (colA, colB, colC)
SELECT colA, colB, colC
FROM <other table name>
....

And, you need period separators between the table name and the column
name in your SELECT list.
& "SELECT " _
& "Tbl_resolved2[Loan Acct #], Tbl_resolved2PopEnterDt,
Tbl_resolved2ResolvedStatus, " _
& "Tbl_resolved2PrevStatus, Tbl_resolved2.[Buying Center #] ,
Click to expand...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDVKnYechKqOuFEgEQJbAQCg85b4NtBrjoNSBxvVA+kkPa8WSB8An0Z+
dRVtyAmKqV8y45iRqrXrTnRC
=g0nx
-----END PGP SIGNATURE-----
I have the following query:


Dim strSQLAppendBKs As String


strSQLAppendBKs = "INSERT INTO tbl_masterpop " _
& "[loan acct #], popenterdt, Status, " _
& "PrevStatus, [Buying Center #], [Division Name], [Region Name], [Group
Name], " _
& "[Collection Center Name], [Distribution Channel], [New Institution Name],
" _
& "[Origination Channel] , [Sub Channel], [Short Name], [Loan Status],
[Contract Date], [Mat date], " _
& "[Next Pmt Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made],
" _
& "[# Reg Pmts Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last
Pmt Amt], [Amended?], [Amended This Mo?], [DEL Group], " _
& "[Days Delinquent], [Tot Past Due Pmts $], [Principal Balance], [Val
Reserve Dollar Amt], " _
& "NETBAL, [Ext?], [Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth
of Orig], [Active BK Flag Date], [Active BK Flag], [Inventory Status],
[Charge Off Principal], " _
& "[Chg Off Type], [CHG OFF Date], [Cure Letter Date] , [Skip Active Date],
[Skip Completion Date], [Skip Completion Sts], " _
& "[Redeemed Date], [Entered Date], [Repo Ordered Date], [Active Inactive
Flag] " _
& "SELECT " _
& "Tbl_resolved2[Loan Acct #], Tbl_resolved2PopEnterDt,
Tbl_resolved2ResolvedStatus, " _
& "Tbl_resolved2PrevStatus, Tbl_resolved2.[Buying Center #] ,
Tbl_resolved2.[Division Name], Tbl_resolved2.[Region Name],
Tbl_resolved2.[Group Name], Tbl_resolved2.[Collection Center Name], " _
& "Tbl_resolved2.[Distribution Channel], Tbl_resolved2.[New Institution
Name] , Tbl_resolved2.[Origination Channel], " _
& "Tbl_resolved2.[Sub Channel], Tbl_resolved2.[Short Name],
Tbl_resolved2.[Loan Status], Tbl_resolved2.[Contract Date],
Tbl_resolved2.[Mat date], Tbl_resolved2.[Next Pmt Due Date],
Tbl_resolved2.[Payoff Date]," _
& "Tbl_resolved2.[Paid Off?], Tbl_resolved2.[Nbr of Reg Pmts Made],
Tbl_resolved2.[# Reg Pmts Remaining], " _
& "Tbl_resolved2.[Pmt Scd Monthly Pmt Amt], Tbl_resolved2.[Last Pmt Date],
Tbl_resolved2.[Last Pmt Amt], Tbl_resolved2.[Amended?],
Tbl_resolved2.[Amended This Mo?], Tbl_resolved2.[DEL Group],
Tbl_resolved2.[Days Delinquent], " _
& "Tbl_resolved2.[Tot Past Due Pmts $], Tbl_resolved2.[Principal Balance],
tbl_resolved2.[Val Reserve Dollar Amt] , tbl_resolved2.NETBAL,
tbl_resolved2.[Ext?], tbl_resolved2.[Extended This Mo?],
tbl_resolved2.[Payoff Dollar Amt], tbl_resolved2.[Flat Void Mth of Orig], " _
& "tbl_resolved2.[Active BK Flag Date], tbl_resolved2.[Active BK Flag],
tbl_resolved2.[Inventory Status], tbl_resolved2.[Charge Off Principal],
tbl_resolved2.[Chg Off Type], tbl_resolved2.[CHG OFF Date], " _
& "tbl_resolved2.[Cure Letter Date], tbl_resolved2.[Skip Active Date],
tbl_resolved2.[Skip Completion Date], tbl_resolved2.[Skip Completion Sts],
tbl_resolved2.[Redeemed Date], tbl_resolved2.[Entered Date],
tbl_resolved2.[Repo Ordered Date], tbl_resolved2.[Active Inactive Flag], " _
& "FROM Tbl_resolved2 " _
& "WHERE popenterdt = " _
& " (SELECT min(popenterdt) " _
& " FROM tbl_resolved WHERE tbl_resolved.[loan acct #] =
tbl_resolved2.[loan acct #]);"


DoCmd.RunSQL strSQLAppendBKs

but I am getting an error message:
runtime error 3134: SYNTAX ERROR IN INSERT INTO statement.

what I am trying to do with the above query is append unique loan account
numbers, with the first [popenterdt] for that [loan acct #] (as there are
more than one instance of the [loan acct #]) into the tbl_masterpop.

thanks in advance,
geebee
Click to expand...
 
G

Guest

I am getting the same error, even though I have:

Dim strSQLAppendBKs As String


strSQLAppendBKs = "INSERT INTO tbl_masterpop " _
& "([loan acct #], popenterdt, Status, " _
& "PrevStatus, [Buying Center #], [Division Name], [Region Name], [Group
Name], " _
& "[Collection Center Name], [Distribution Channel], [New Institution Name],
" _
& "[Origination Channel] , [Sub Channel], [Short Name], [Loan Status],
[Contract Date], [Mat date], " _
& "[Next Pmt Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made],
" _
& "[# Reg Pmts Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last
Pmt Amt], [Amended?], [Amended This Mo?], [DEL Group], " _
& "[Days Delinquent], [Tot Past Due Pmts $], [Principal Balance], [Val
Reserve Dollar Amt], " _
& "[NETBAL], [Ext?], [Extended This Mo?], [Payoff Dollar Amt], [Flat Void
Mth of Orig], [Active BK Flag Date], [Active BK Flag], [Inventory Status],
[Charge Off Principal], " _
& "[Chg Off Type], [CHG OFF Date], [Cure Letter Date], [Skip Active Date],
[Skip Completion Date], [Skip Completion Sts], " _
& "[Redeemed Date], [Entered Date], [Repo Ordered Date], [Active Inactive
Flag])" _
& "SELECT " _
& "Tbl_resolved2.[Loan Acct #], Tbl_resolved2.[PopEnterDt],
Tbl_resolved2.[ResolvedStatus], " _
& "Tbl_resolved2.[PrevStatus], Tbl_resolved2.[Buying Center #],
Tbl_resolved2.[Division Name], Tbl_resolved2.[Region Name],
Tbl_resolved2.[Group Name], Tbl_resolved2.[Collection Center Name], " _
& "Tbl_resolved2.[Distribution Channel], Tbl_resolved2.[New Institution
Name], Tbl_resolved2.[Origination Channel], " _
& "Tbl_resolved2.[Sub Channel], Tbl_resolved2.[Short Name],
Tbl_resolved2.[Loan Status], Tbl_resolved2.[Contract Date],
Tbl_resolved2.[Mat date], Tbl_resolved2.[Next Pmt Due Date],
Tbl_resolved2.[Payoff Date]," _
& "Tbl_resolved2.[Paid Off?], Tbl_resolved2.[Nbr of Reg Pmts Made],
Tbl_resolved2.[# Reg Pmts Remaining], " _
& "Tbl_resolved2.[Pmt Scd Monthly Pmt Amt], Tbl_resolved2.[Last Pmt Date],
Tbl_resolved2.[Last Pmt Amt], Tbl_resolved2.[Amended?],
Tbl_resolved2.[Amended This Mo?], Tbl_resolved2.[DEL Group],
Tbl_resolved2.[Days Delinquent], " _
& "Tbl_resolved2.[Tot Past Due Pmts $], Tbl_resolved2.[Principal Balance],
tbl_resolved2.[Val Reserve Dollar Amt] , tbl_resolved2.NETBAL,
tbl_resolved2.[Ext?], tbl_resolved2.[Extended This Mo?],
tbl_resolved2.[Payoff Dollar Amt], tbl_resolved2.[Flat Void Mth of Orig], " _
& "tbl_resolved2.[Active BK Flag Date], tbl_resolved2.[Active BK Flag],
tbl_resolved2.[Inventory Status], tbl_resolved2.[Charge Off Principal],
tbl_resolved2.[Chg Off Type], tbl_resolved2.[CHG OFF Date], " _
& "tbl_resolved2.[Cure Letter Date], tbl_resolved2.[Skip Active Date],
tbl_resolved2.[Skip Completion Date], tbl_resolved2.[Skip Completion Sts],
tbl_resolved2.[Redeemed Date], tbl_resolved2.[Entered Date],
tbl_resolved2.[Repo Ordered Date], tbl_resolved2.[Active Inactive Flag], " _
& "FROM Tbl_resolved2 " _
& "WHERE popenterdt = " _
& " (SELECT min(popenterdt) " _
& " FROM tbl_resolved WHERE tbl_resolved.[loan acct #] =
tbl_resolved2.[loan acct #]);"


DoCmd.RunSQL strSQLAppendBKs

any more suggestions?

thanks in advance,
geebee


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need parentheses around the INSERT INTO column list. Syntax is:

INSERT INTO <table name> (colA, colB, colC)
SELECT colA, colB, colC
FROM <other table name>
....

And, you need period separators between the table name and the column
name in your SELECT list.
& "SELECT " _
& "Tbl_resolved2[Loan Acct #], Tbl_resolved2PopEnterDt,
Tbl_resolved2ResolvedStatus, " _
& "Tbl_resolved2PrevStatus, Tbl_resolved2.[Buying Center #] ,
Click to expand...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDVKnYechKqOuFEgEQJbAQCg85b4NtBrjoNSBxvVA+kkPa8WSB8An0Z+
dRVtyAmKqV8y45iRqrXrTnRC
=g0nx
-----END PGP SIGNATURE-----
I have the following query:


Dim strSQLAppendBKs As String


strSQLAppendBKs = "INSERT INTO tbl_masterpop " _
& "[loan acct #], popenterdt, Status, " _
& "PrevStatus, [Buying Center #], [Division Name], [Region Name], [Group
Name], " _
& "[Collection Center Name], [Distribution Channel], [New Institution Name],
" _
& "[Origination Channel] , [Sub Channel], [Short Name], [Loan Status],
[Contract Date], [Mat date], " _
& "[Next Pmt Due Date], [Payoff Date], [Paid Off?], [Nbr of Reg Pmts Made],
" _
& "[# Reg Pmts Remaining], [Pmt Scd Monthly Pmt Amt], [Last Pmt Date], [Last
Pmt Amt], [Amended?], [Amended This Mo?], [DEL Group], " _
& "[Days Delinquent], [Tot Past Due Pmts $], [Principal Balance], [Val
Reserve Dollar Amt], " _
& "NETBAL, [Ext?], [Extended This Mo?], [Payoff Dollar Amt], [Flat Void Mth
of Orig], [Active BK Flag Date], [Active BK Flag], [Inventory Status],
[Charge Off Principal], " _
& "[Chg Off Type], [CHG OFF Date], [Cure Letter Date] , [Skip Active Date],
[Skip Completion Date], [Skip Completion Sts], " _
& "[Redeemed Date], [Entered Date], [Repo Ordered Date], [Active Inactive
Flag] " _
& "SELECT " _
& "Tbl_resolved2[Loan Acct #], Tbl_resolved2PopEnterDt,
Tbl_resolved2ResolvedStatus, " _
& "Tbl_resolved2PrevStatus, Tbl_resolved2.[Buying Center #] ,
Tbl_resolved2.[Division Name], Tbl_resolved2.[Region Name],
Tbl_resolved2.[Group Name], Tbl_resolved2.[Collection Center Name], " _
& "Tbl_resolved2.[Distribution Channel], Tbl_resolved2.[New Institution
Name] , Tbl_resolved2.[Origination Channel], " _
& "Tbl_resolved2.[Sub Channel], Tbl_resolved2.[Short Name],
Tbl_resolved2.[Loan Status], Tbl_resolved2.[Contract Date],
Tbl_resolved2.[Mat date], Tbl_resolved2.[Next Pmt Due Date],
Tbl_resolved2.[Payoff Date]," _
& "Tbl_resolved2.[Paid Off?], Tbl_resolved2.[Nbr of Reg Pmts Made],
Tbl_resolved2.[# Reg Pmts Remaining], " _
& "Tbl_resolved2.[Pmt Scd Monthly Pmt Amt], Tbl_resolved2.[Last Pmt Date],
Tbl_resolved2.[Last Pmt Amt], Tbl_resolved2.[Amended?],
Tbl_resolved2.[Amended This Mo?], Tbl_resolved2.[DEL Group],
Tbl_resolved2.[Days Delinquent], " _
& "Tbl_resolved2.[Tot Past Due Pmts $], Tbl_resolved2.[Principal Balance],
tbl_resolved2.[Val Reserve Dollar Amt] , tbl_resolved2.NETBAL,
tbl_resolved2.[Ext?], tbl_resolved2.[Extended This Mo?],
tbl_resolved2.[Payoff Dollar Amt], tbl_resolved2.[Flat Void Mth of Orig], " _
& "tbl_resolved2.[Active BK Flag Date], tbl_resolved2.[Active BK Flag],
tbl_resolved2.[Inventory Status], tbl_resolved2.[Charge Off Principal],
tbl_resolved2.[Chg Off Type], tbl_resolved2.[CHG OFF Date], " _
& "tbl_resolved2.[Cure Letter Date], tbl_resolved2.[Skip Active Date],
tbl_resolved2.[Skip Completion Date], tbl_resolved2.[Skip Completion Sts],
tbl_resolved2.[Redeemed Date], tbl_resolved2.[Entered Date],
tbl_resolved2.[Repo Ordered Date], tbl_resolved2.[Active Inactive Flag], " _
& "FROM Tbl_resolved2 " _
& "WHERE popenterdt = " _
& " (SELECT min(popenterdt) " _
& " FROM tbl_resolved WHERE tbl_resolved.[loan acct #] =
tbl_resolved2.[loan acct #]);"


DoCmd.RunSQL strSQLAppendBKs

but I am getting an error message:
runtime error 3134: SYNTAX ERROR IN INSERT INTO statement.

what I am trying to do with the above query is append unique loan account
numbers, with the first [popenterdt] for that [loan acct #] (as there are
more than one instance of the [loan acct #]) into the tbl_masterpop.

thanks in advance,
geebee
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

delete query 1
runtime error 3001 invalid argument error message 2
query speed 5
join type not supported 5
combine 2 access queries 3
runtime error 1
duplicate records 3
query error 1

Top