Access query SQL format into VB SQL format
Posted on 2007-03-26
I want to copy a recordset to excel and the sql statement I'm using for the recordset is based on a query I already had made. I thought I'd made all the necessary format changes between the SQL statement in the query and the VB code SQL statement but I can't get the VB code to run the sql statement and my best guess is that it is stumbling in the SELECT portion on the fields called Expr1 and Expr2 from the original query.
Expr1 is getting multiple fields of info converted into 1 field for excel with spaces in between the info
Expr2 is an immediate if-then statement converting multiple descriptions into 2 part# types
the error message i've been consistently getting is "Run-time error '-214721908 (80040e0c)': Command text was not set for the command object."
Here is the code
partSQL = "SELECT tblItemDetail.[Damage Type], tblItemDetail.PO, tblItemDetail.[Supplier / Salvage / Junk], tblEnteredClaims.Pro, _
tblItemDetail!Qty & ‘ ‘ & tblParts!UM & ‘ ‘ & tblItemDetail![Part #] & ‘ ‘ & tblParts!Description AS Expr1, _
IIf([Damage Type]=’damage’,’88888K52’,IIf([Damage Type]=’concealed damage’,’88888K52’,IIf([Damage Type]=’empty_ carton’,’88888K52’,’88888K42’))) AS Expr2 _
FROM (tblEnteredClaims INNER JOIN tblItemDetail ON tblEnteredClaims.Pro = tblItemDetail.Pro) INNER JOIN tblParts ON_ tblItemDetail.[Part #] = tblParts.[Part #] _
Having (((tblEnteredClaims.Pro) = ‘” & ProIn & “’))"