Got it this morning... Even though I was doing a select "AMT TO REFUND" as RefundAmt and it was still failing (ie: both embedding in quotes, and renaming result column via "AS", it still failed...

While sleeping on it, I tried something new this morning that worked, and will obviously pass-on to others...

Somehow, internally to the ODBC handler, if a field coming from (in this case), the Access database has fields that are EMBEDDED WITH SPACES, IT (the ODBC) handler will IMPLY the spaces are UNDERSCORES.

So, if you do [AMT TO REFUND] as... the " TO " portion causing the failure needed resolution as the "TO" was expecting some parameter??? dont know why. So, when i changed this field to [AMT_TO_REFUND] as RefundAmt, internally, the ODBC apparently thinks like this...

Access field "AMT TO REFUND"
also accept as "AMT_TO_REFUND"

so, when I changed to "AMT_TO_REFUND", if properly found the field name match and continued with the query with no problems...

WOW... This one hurt... and no obvious documentation about this internal handling... I new and so do many others about wrapping the field name in quotes, but this was a doosey...








>>Hi all,
>>
>>I'm having a problem querying from an Access table. When the table was setup by another organization, they have the fields such as "testing for a field" is the field name...
>>
>>I get a connection no problem, I can query other fields from the table, but even on OTHER fields that only have a single space, and qualifying them in the query as ...
>>
>>select "Some FIeld" as MyVFPFld from "Table 1"
>>
>>works through a SQLEXEC() call
>>
>>However, if
>>
>>select "Some Other Field" as MyFld from "Table 1" -- WORKS
>>
>>Strange, but the failure will ONLY FAIL if the field name as " TO " as part of it.. ie:
>>
>>select "AMT TO REFUND" as MyField from "Table 1" -- FAILS
>
>How about
>

>select [AMT TO REFUND] as MyField from [Table 1]
>