That really is a pain in the a$$. It appears that Access programmers have a habit of embedding spaces in form, query, table, etc names.
I've made the mistake of taking on an Access to SQL Data upsizing project, that those kind of names are all over the place.
>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]
>>
____________________________________
Don't Tread on Me
Overthrow the federal government NOW!
____________________________________