>>Thank you, Mike,
>> for the explanation. I really like that idea. It might take me a bit to wrap my head around doing it all the time, from now on. :-) Can't go back and re-write all my code.
>>
>>I am trying to get the code to work now. I am having problems with the sql statement and am trying to figure out why now.
>
>Here's your query:
>

SELECT EVALUATE(latmp[1]) as &lcnewfield. FROM &lcoldfile. ;
>   INTO TABLE lcnewfile DATABASE &lcTAbleDbc.

>
>Here's my interpretation: (obviously I've not tested this)
>

>TEXT TO m.lcSql TEXTMERGE NOSHOW PRETEXT 15
>SELECT <<latmp[1]>> as <<m.lcnewfield>>
> FROM <<m.lcoldfile>>
> INTO TABLE <<m.lcnewfile>> DATABASE <<m.lcTAbleDbc>>
>ENDTEXT
>&lcSql.

>
>>
>>Beth
>>
My problem was with the FROM statement. The program would pop up the GETFILE diologe when executiong the string, and the error would be table not found. I had already opened the file above and selected it, but still got the error. I went with the string build ('' + m.var + '') because it did not error. I am going to look into this laster, when I have more time.

>>>Hi Beth
>>>
>>>Allow me to point out something different from the others. Consistency of technique is a huge benefit to productivity. I try to only build SQL with textmerge, that way I don't have to try and decide about little things like having to use & or name expressions or textmerge.
>>>
>>>The point is to get the string built in as simple and maintainable a fashion as possible. The execution of the command will usually exceed the time taken to build the command.
>>>
>>>So, to paraphrase Sergey's version...
>>>
>>>TEXT TO m.lcSql TEXTMERGE NOSHOW PRETEXT 15
>>>SELECT <> as <>
>>> FROM <>
>>> INTO TABLE <> DATABASE <>
>>>ENDTEXT
>>>&lcSql.
>>>
>>>TEXT...ENDTEXT for every SQL, making the SQL very easy to write/edit. <<>> for every variable. Very little to remember and (as a result) less room for mistakes.
>>>
>>>>Hi All,
>>>>
>>>> I am trying to set up a select statement, I do not know the field name ahead of time. I do an AFIELDS() to get the field name, since there is only one field in the table. I need to change the field name to another name which I get before this select statement. I am thinks this way:
>>>>

>>>>SELECT EVALUATE(latmp[1]) as &lcnewfield. FROM &lcoldfile. ;
>>>>   INTO TABLE lcnewfile DATABASE &lcTAbleDbc.
>>>>

>>>>
>>>>Am I using the macro substitution too much? Can I do this easier? or nicer?
>>>>
>>>>TIA
>>>>Beth