What is wrong with this query?

  • Thread starter nlburgess via AccessMonster.com
  • Start date
N

nlburgess via AccessMonster.com

strSql = "INSERT INTO tblARDatabase ( PTN_CTL_NO, CL_TYPE, RID, RELATION,
PROV_NO, PROV_NAME, CARR_ID, CARR_NAME, CARR_ADDR1, CARR_ADDR2, CARR_CITY,
CARR_STATE, CARR_ZIP, ICN, CLAIM_TYPE, LSTFRDT01, LSTTHDT01, TOT_CHG, BALDUE,
FILLER20, TotalAmountRequested, AccrInterest, [Date], TypeMSPSituation,
TotalMedicareCharges, TotalAccrInterest, OrigMailDt )"

SELECT MASTER_CLAIMS.DCN, MASTER_CLAIMS.CL_TYPE, MASTER_CLAIMS.RID,
MASTER_CLAIMS.RELATION, MASTER_CLAIMS.PROV_NO, MASTER_CLAIMS.PROV_NAME,
MASTER_CLAIMS.CARR_ID, MASTER_CLAIMS.CARR_NAME, MASTER_CLAIMS.CARR_ADDR1,
MASTER_CLAIMS.CARR_ADDR2, MASTER_CLAIMS.CARR_CITY, MASTER_CLAIMS.CARR_STATE,
MASTER_CLAIMS.CARR_ZIP, MASTER_CLAIMS.ICN, MASTER_CLAIMS.CLAIM_TYPE,
MASTER_CLAIMS.LSTFRDT01, MASTER_CLAIMS.LSTTHDT01, MASTER_CLAIMS.TOT_CHG,
MASTER_CLAIMS.BALDUE, MASTER_CLAIMS.FILLER20, MASTER_CLAIMS.[Total Amount
Requested], MASTER_CLAIMS.[Accrued Interest], MASTER_CLAIMS.InterestDate,
MASTER_CLAIMS.[Type of MSP Situation], MASTER_CLAIMS.[Total Medicare Charges],
MASTER_CLAIMS.[Total Accrued Interest], MASTER_CLAIMS.OMD

FROM MASTER_CLAIMS LEFT JOIN tblARDatabase ON MASTER_CLAIMS.ICN =
tblARDatabase.ICN

WHERE (((tblARDatabase.ICN) Is Null) AND ((MASTER_CLAIMS.Status)="sent"))"

This should be one statement in order for me to execute the strSQL but
apparently my syntax is all wrong.
 
B

BruceM

For one thing you need to double up the quotes around "sent":
""sent""
Otherwise Access will see the first double quote as the end of a string.

There may be something else, but I am not sufficiently adept at SQL to spot
it. However, the double quote thing is a good place to start.
 
J

John Spencer

Perhaps something like


strSql = "INSERT INTO tblARDatabase ( PTN_CTL_NO, CL_TYPE, RID, RELATION,
PROV_NO, PROV_NAME, CARR_ID, CARR_NAME, CARR_ADDR1, CARR_ADDR2, CARR_CITY,
CARR_STATE, CARR_ZIP, ICN, CLAIM_TYPE, LSTFRDT01, LSTTHDT01, TOT_CHG,
BALDUE,
FILLER20, TotalAmountRequested, AccrInterest, [Date], TypeMSPSituation,
TotalMedicareCharges, TotalAccrInterest, OrigMailDt )"

strSQL = strSQL & _
"SELECT MASTER_CLAIMS.DCN, MASTER_CLAIMS.CL_TYPE, MASTER_CLAIMS.RID,
MASTER_CLAIMS.RELATION, MASTER_CLAIMS.PROV_NO, MASTER_CLAIMS.PROV_NAME,
MASTER_CLAIMS.CARR_ID, MASTER_CLAIMS.CARR_NAME, MASTER_CLAIMS.CARR_ADDR1,
MASTER_CLAIMS.CARR_ADDR2, MASTER_CLAIMS.CARR_CITY, MASTER_CLAIMS.CARR_STATE,
MASTER_CLAIMS.CARR_ZIP, MASTER_CLAIMS.ICN, MASTER_CLAIMS.CLAIM_TYPE,
MASTER_CLAIMS.LSTFRDT01, MASTER_CLAIMS.LSTTHDT01, MASTER_CLAIMS.TOT_CHG,
MASTER_CLAIMS.BALDUE, MASTER_CLAIMS.FILLER20, MASTER_CLAIMS.[Total Amount
Requested], MASTER_CLAIMS.[Accrued Interest], MASTER_CLAIMS.InterestDate,
MASTER_CLAIMS.[Type of MSP Situation], MASTER_CLAIMS.[Total Medicare
Charges],
MASTER_CLAIMS.[Total Accrued Interest], MASTER_CLAIMS.OMD
FROM MASTER_CLAIMS LEFT JOIN tblARDatabase ON MASTER_CLAIMS.ICN =
tblARDatabase.ICN
WHERE (((tblARDatabase.ICN) Is Null) AND ((MASTER_CLAIMS.Status)=""sent""))"

Note the inclusion of the doubled quote marks around the word sent.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nlburgess via AccessMonster.com said:
strSql = "INSERT INTO tblARDatabase ( PTN_CTL_NO, CL_TYPE, RID, RELATION,
PROV_NO, PROV_NAME, CARR_ID, CARR_NAME, CARR_ADDR1, CARR_ADDR2, CARR_CITY,
CARR_STATE, CARR_ZIP, ICN, CLAIM_TYPE, LSTFRDT01, LSTTHDT01, TOT_CHG,
BALDUE,
FILLER20, TotalAmountRequested, AccrInterest, [Date], TypeMSPSituation,
TotalMedicareCharges, TotalAccrInterest, OrigMailDt )"

SELECT MASTER_CLAIMS.DCN, MASTER_CLAIMS.CL_TYPE, MASTER_CLAIMS.RID,
MASTER_CLAIMS.RELATION, MASTER_CLAIMS.PROV_NO, MASTER_CLAIMS.PROV_NAME,
MASTER_CLAIMS.CARR_ID, MASTER_CLAIMS.CARR_NAME, MASTER_CLAIMS.CARR_ADDR1,
MASTER_CLAIMS.CARR_ADDR2, MASTER_CLAIMS.CARR_CITY,
MASTER_CLAIMS.CARR_STATE,
MASTER_CLAIMS.CARR_ZIP, MASTER_CLAIMS.ICN, MASTER_CLAIMS.CLAIM_TYPE,
MASTER_CLAIMS.LSTFRDT01, MASTER_CLAIMS.LSTTHDT01, MASTER_CLAIMS.TOT_CHG,
MASTER_CLAIMS.BALDUE, MASTER_CLAIMS.FILLER20, MASTER_CLAIMS.[Total Amount
Requested], MASTER_CLAIMS.[Accrued Interest], MASTER_CLAIMS.InterestDate,
MASTER_CLAIMS.[Type of MSP Situation], MASTER_CLAIMS.[Total Medicare
Charges],
MASTER_CLAIMS.[Total Accrued Interest], MASTER_CLAIMS.OMD

FROM MASTER_CLAIMS LEFT JOIN tblARDatabase ON MASTER_CLAIMS.ICN =
tblARDatabase.ICN

WHERE (((tblARDatabase.ICN) Is Null) AND ((MASTER_CLAIMS.Status)="sent"))"

This should be one statement in order for me to execute the strSQL but
apparently my syntax is all wrong.
Click to expand...
 
N

nlburgess via AccessMonster.com

I figured it out thanks a lot. My main thing was the ""sent"" and the other
is when the strSQL is so long that is on multiple lines it should be written
like

strSQL: "INSERT INTO ..." & _ "SELECT..." & _ "FROM..." & _ "WHERE..."

John said:
Perhaps something like

strSql = "INSERT INTO tblARDatabase ( PTN_CTL_NO, CL_TYPE, RID, RELATION,
PROV_NO, PROV_NAME, CARR_ID, CARR_NAME, CARR_ADDR1, CARR_ADDR2, CARR_CITY,
CARR_STATE, CARR_ZIP, ICN, CLAIM_TYPE, LSTFRDT01, LSTTHDT01, TOT_CHG,
BALDUE,
FILLER20, TotalAmountRequested, AccrInterest, [Date], TypeMSPSituation,
TotalMedicareCharges, TotalAccrInterest, OrigMailDt )"

strSQL = strSQL & _
"SELECT MASTER_CLAIMS.DCN, MASTER_CLAIMS.CL_TYPE, MASTER_CLAIMS.RID,
MASTER_CLAIMS.RELATION, MASTER_CLAIMS.PROV_NO, MASTER_CLAIMS.PROV_NAME,
MASTER_CLAIMS.CARR_ID, MASTER_CLAIMS.CARR_NAME, MASTER_CLAIMS.CARR_ADDR1,
MASTER_CLAIMS.CARR_ADDR2, MASTER_CLAIMS.CARR_CITY, MASTER_CLAIMS.CARR_STATE,
MASTER_CLAIMS.CARR_ZIP, MASTER_CLAIMS.ICN, MASTER_CLAIMS.CLAIM_TYPE,
MASTER_CLAIMS.LSTFRDT01, MASTER_CLAIMS.LSTTHDT01, MASTER_CLAIMS.TOT_CHG,
MASTER_CLAIMS.BALDUE, MASTER_CLAIMS.FILLER20, MASTER_CLAIMS.[Total Amount
Requested], MASTER_CLAIMS.[Accrued Interest], MASTER_CLAIMS.InterestDate,
MASTER_CLAIMS.[Type of MSP Situation], MASTER_CLAIMS.[Total Medicare
Charges],
MASTER_CLAIMS.[Total Accrued Interest], MASTER_CLAIMS.OMD
FROM MASTER_CLAIMS LEFT JOIN tblARDatabase ON MASTER_CLAIMS.ICN =
tblARDatabase.ICN
WHERE (((tblARDatabase.ICN) Is Null) AND ((MASTER_CLAIMS.Status)=""sent""))"

Note the inclusion of the doubled quote marks around the word sent.
strSql = "INSERT INTO tblARDatabase ( PTN_CTL_NO, CL_TYPE, RID, RELATION,
PROV_NO, PROV_NAME, CARR_ID, CARR_NAME, CARR_ADDR1, CARR_ADDR2, CARR_CITY,
Click to expand...
[quoted text clipped - 23 lines]
This should be one statement in order for me to execute the strSQL but
apparently my syntax is all wrong.
Click to expand...
Click to expand...
 
Top