SQL code works in Access SQL window but not in VBA SQL code
wrote in message
...
On Aug 10, 9:01 am, wrote:
dbs.Execute "SELECT CHOOSEData.BFY, CHOOSEData.APPN_SYMB, " _
& "CHOOSEData.SBHD, CHOOSEData.BCN, CHOOSEData.SA_SX,
CHOOSEData.AAA_UIC, " _
& "CHOOSEData.ACRN, CHOOSEData.AMT, CHOOSEData.DOC_NUMBER, " _
& "CHOOSEData.FIPC, CHOOSEData.REG_NUMB, CHOOSEData.TRAN_TYPE, " _
& "CHOOSEData.DOV_NUM, CHOOSEData.PAA, CHOOSEData.COST_CODE, " _
& "CHOOSEData.OBJ_CODE, CHOOSEData.EFY, CHOOSEData.REG_MO, " _
& "CHOOSEData.RPT_MO, CHOOSEData.EFFEC_DATE, CHOOSEData.Orig_Sort, " _
& "CHOOSEData.LTrim_BFY, CHOOSEData.LTrim_AAA, CHOOSEData.LTrim_REG, " _
& "CHOOSEData.LTrim_DOV, CHOOSEData.AMT_Rev, CHOOSEData.CONCACT" _
& " INTO CHOOSERev" _
& Chr(10) & "FROM CHOOSEData" & Chr(10) _
& " WHERE (((CHOOSEData.TRAN_TYPE) In ('1K','2D')) And
((CHOOSEData.LTrim_REG)'7'));"
Sometimes Access gives strange error messages.
I would look at the following:
--There is no space after " INTO CHOOSERev".
--There is a line-feed character before and after "FROM CHOOSEData".
This is the nub of the problem. All that is needed is a space before and
after.
--There is a semi-colon embedded in the sql string.
It's the statement terminator, which is fine. You don't have to put one,
but it's OK if you do.
--There is a limit to the length of an sql statement and you are
wasting
part of that limited number of characters by repeating
"CHOOSEDATA.".
( I think the limit is 255 characters )
No, that limit only applies to an SQL string used as a RecordSource property
or RowSource property.
|