AccpacERPCustomLookup (3 replies)
Steve, the problem here is that you are embedding a field reference into the select string.
The reason you're getting an error are:
a. The referenced field (%I_GECO_ID) will resolve to a quoted string e.g. "434138" which in a SQL query means you're referencing a column or alias.
b. The resolved field means you have a double quote at the end of your statement which is an escaped quote in VBScript.
To resolve you can either build the select string using the concatenate operator (note the last part of the statement is a single quote enclosed with double quotes).
"SELECT * from ARIBDO where VALUE = '" & %I_GECO_ID & "'"
Or change the I_GECO_ID field from a Text type to a numeric type. This will prevent the value from resolving with double quotes.
You marvellous people. I knew it must be some way of getting the right quote combination in there but I couldn't do it.
Works fine thank you.
.
I’ve always had trouble with AccpacERPCustomLookup and I’m sure it’s a syntax thing. Could use DB lookup but it’s variable and multiple Sage 300 databases, so better with the AccpacERPCustomLookup function.
I can make it work if I put literal values in the query, but not if I use field values. For example, this works.
AccpacERPCustomLookup(%H_Legal_Entity, "SELECT * from ARIBDO where VALUE = '434118'", "OPTFIELD")
However, this doesn’t.
AccpacERPCustomLookup(%H_Legal_Entity, "SELECT * from ARIBDO where VALUE = %I_GECO_ID", "OPTFIELD")
I have tried variations of single and double quotes around the field name but everything produces an error usually along the lines of
Resolved Function - AccpacERPCustomLookup("ABEDAT", "SELECT * from ARIBDO where VALUE = "434138"", "OPTFIELD")
Error - Description: Expected ')'