What is wrong with this query?

  • Thread starter Thread starter nlburgess via AccessMonster.com
  • Start date 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.
 
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.
 
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...
 
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...
 
Back
Top