SQL Syntax What am I doing wrong ?

[FoxEgg]

The following works

SELECT patient_episodes.file_num , patient_episodes.title , patient_episodes.last_name FROM patient_episodes INTO CURSOR temp1 wHERE file_num = x

So I want to label the columns.... and yet this doesn't work

SELECT patient_episodes.file_num AS File Num, patient_episodes.title , patient_episodes.last_name FROM patient_episodes INTO CURSOR temp1 wHERE file_num = x

I have tried with "" and [ ] ... no go (The below doesn't work)

SELECT patient_episodes.file_num AS "File Num", patient_episodes.title , patient_episodes.last_name FROM patient_episodes INTO CURSOR temp1 wHERE file_num = x

What am I doing wrong ???

JF
FoxEgg

 

[Mike Lewis]

The problem is that you have got a space in File Num. Since this will be the name of the resulting field, the syntax is illegal - because spaces are not allowed in field names. The solution would be to change the space to, say, an underscore:

[tt]... AS File_Num ...[/tt]

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike, while all this is right, you are (indirectly) suggesting SELECT patient_episodes.file_num As File_Num.

To make it clearer:
>What am I doing wrong ???
You are trying the impossible, naming fields with spaces in them is not possible. VFP does not allow to delimit field names with [] or "", as other databases do. Even not with (), though that is for name expressions. The emphasizing is on expressions, (lcName+"1") will evaluate the expression lcName+"1" into a final name, but that also doesn't allow names with spaces in them, just dynamically named, so the result of such an expression also isn't allowed to have spaces.

You problem isn't table or cursor field names, though, your goal is to have grid header captions with spaces. Simply do so at design time. Only a grid you don't design will get it's captions from field names. Start using the grid builder and it'll help you set it the way you want. Yes, this is additional work, but only once.

Alternatively FOR EACH loColumn OF aGrid set loColumn.Header1.Caption = CHRTRAN(loColumn.Header1.Caption,"_"," ").

Bye, Olaf.

 

[FoxEgg]

Thank you... thank you...

... and of course the irony was that, all that I was trying to eliminate the underscore from File_Num and Last_Name etc etc so either I cannot OR I do as you guys have suggested

And thank you for the solution...

Much appreciated.

JF