SQL Query runs, unless run from VBA

[Alc]

I have a query that gets constructed, via a sequence of events, in VBA.

 INSERT INTO tblARank2 ( DEALER_CODE, WARRANTY_CLAIM_NUMBER, VIN, MODEL, MODEL2, CALENDAR_PROCESS_DATE, REPAIR_ORDER_DATE, DEFECT_PART_NUMBER, REPLACEMENT_PART_NUMBER_1, REPAIR_DESCRIPTION, CLAIM_DEFECT_DESCRIPTION, CLAIM_CONTENTION_DESCRIPTION, WARRANTY_CLAIM_REMARKS, MODEL_ENGINEER, DISPOSITION, QIS, QIC, MILEAGE, UNIT_BIRTHDAY, DATA_SOURCE, SYMPTOM_CODE )
SELECT IWH_WARRANTY_CLAIM_COMBINED_FACT_V.DEALER_CODE, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.WARRANTY_CLAIM_NUMBER, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN, Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,4,3) & Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,10,1) AS MODEL, Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,4,2) & Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,10,1) AS MODEL2, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.CALENDAR_PROCESS_DATE, REPAIR_ORDER_START_DATE, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.DEFECT_PART_NUMBER, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.REPLACEMENT_PART_NUMBER_1, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.REPAIR_DESCRIPTION, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.CLAIM_DEFECT_DESCRIPTION, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.CLAIM_CONTENTION_DESCRIPTION, IWH_WARRANTY_CLAIM_COMBINED_FACT_V.WARRANTY_CLAIM_REMARKS, Null AS MODEL_ENGINEER, Null AS DISPOSITION, Null AS QIS, Null AS QIC, MILEAGE_AT_FAILURE AS MILEAGE, UNIT_BIRTHDAY, "WARRANTY", CONTENTION_CODE
FROM IWH_WARRANTY_CLAIM_COMBINED_FACT_V INNER JOIN IWH_BASIC_UNIT_FACT ON IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN = IWH_BASIC_UNIT_FACT.VIN
WHERE IWH_WARRANTY_CLAIM_COMBINED_FACT_V.CALENDAR_PROCESS_DATE>#10/28/2015#
AND (SERVICE_PRODUCT_LINE = "H" OR SERVICE_PRODUCT_LINE = "A") AND NOT (CONTENTION_CODE LIKE 'K*' OR CONTENTION_CODE LIKE 'M*' OR CONTENTION_CODE LIKE 'L*') AND (Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,10,1) = "D" or Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,10,1) = "E" or Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,10,1) = "F" or Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,10,1) = "G" or Mid(IWH_WARRANTY_CLAIM_COMBINED_FACT_V.VIN,10,1) = "C")
ORDER BY DEFECT_PART_NUMBER;

If I output it to a text box on a form and copy/paste the above text to a new query, it runs perfectly.

However, if I try to run the constructed string directly from the VBA code, I get an error:

 Run-time error '3129'
 Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE", 'SELECT', or 'UPDATE'.

These are the relevant lines of code

Me.Text34 = strSQL                  ' -- outputs a useable SQL statement
DoCmd.RunSQL "strSQL"           ' -- produces the error message

any ideas as to what can cause this kind of disparity?

 

[pbaldy]

Sure, you don't want the quotes around the variable.

 

[Alc]

Oh for F*%$* sake! :banghead:

THANKYOU!!

I've been looking and looking at it and completely missed that. Can't even claim to be new to it.

 

[pbaldy]

LOL! We've all done it.