field name with space

guys....in my database table i have a field name with space in between for exaple "numero operation".....now i m using data environment to make a datareport....but in dataenvironment sql i get error when i use field name with space....i have two commands on dataenvironment and sql of each is given below.....

command1

SELECT DISTINCT `Numero Operation`, `date`, Societe, Client, Description, Destinataire, FROM recevoi WHERE (`Numero Operation`= ?)

command2

SELECT recevoi.`Numero Operation`, recevoi.Societe,
recevoi.`date`, fileinfo_recevoi.liste,
fileinfo_recevoi.programme, fileinfo_recevoi.type,
recevoi.Client, recevoi.Description, recevoi.Destinataire,
FROM recevoi, fileinfo_recevoi
WHERE recevoi.`Numero Operation` = fileinfo_recevoi.`Numero Operation`


it works perfectly when i use "numeroperation"...i mean without space...

i m gettng error "type incompitible" ....pls tell what is wrong in these sql...


thanx.

put square brackets around the fieldnames with spaces, and reserved words.

SELECT [Numero Operation], [date]

Put brackets around the field name

SELECT [Numero Operation] , etc ...

thanx davie...but i already tried it and it didnt work...pls note i m talking about sql in dataenvironement....with [] it works from code page of the form but not from the dataenvironment...

why don't you try building the SQL in SQL Server view or Access query (whatever you are using as a backend) and then copy the SQL into the command object.

i don't see any problem with using brackets nut may i ask what provider you use?

thanx duane...i use ms jet 3.51 provider...tell me if i need to change anything

just give it a try use the bracket and use the jet 4.0 ok

actaully that "Numero Operation" refers to one lable so i have a statement so when users will click on the command button "report" followning code will be executed...

Load DataEnvironment2

DataEnvironment2.Command1 Form1.lblnumeroperation.Caption

datareport2.show

is this ok...or there is any problem here....

i tried using brackets but it didnt help...

what happend after the query has been executed?

it gives error on the statement....

DataEnvironment2.Command1 Form1.lblnumeroperation.Caption

can i see example of your label caption?

it is a string like..

av0401001
av0401002
av0401003

here av means arrieved...04 is year...01 is month and the rest is counter....

it is a string like..

av0401001
av0401002
av0401003

here av means arrieved...04 is year...01 is month and the rest is counter....

and your query in your data environment?

command1

SELECT DISTINCT `Numero Operation`, `date`, Societe, Client, Description, Destinataire, FROM recevoi WHERE (`Numero Operation`= ?)

command2

SELECT recevoi.`Numero Operation`, recevoi.Societe,
recevoi.`date`, fileinfo_recevoi.liste,
fileinfo_recevoi.programme, fileinfo_recevoi.type,
recevoi.Client, recevoi.Description, recevoi.Destinataire,
FROM recevoi, fileinfo_recevoi
WHERE recevoi.`Numero Operation` = fileinfo_recevoi.`Numero Operation`


it works perfectly when i use "numeroperation"...i mean without space...

i m gettng error "type incompitible" ....pls tell what is wrong in these sql...


thanx.

right now you're using single quote instead of bracket?

and if you use the bracket you got an error are you sure you're only using bracket with out the single qoute?

ya i tried by replacing quote with brackets but i got the same kind of error...and another thing...i just tested by creating new field name "product name" in addition to the previous one "numero operation" with space in between...and tempororly i changed "numero operation" to "numeroperatio" and it worked fine.....so i think may be problem is in statement

DataEnvironment2.Command1 Form1.lblnumeroperation.Caption

well that sounds strange can you attach your program i want to see it my self if it is ok with you? I'll try what i can do.

well duane thanx for all the support...actually i want to use space bcos report will be given in ms excel and there if i represent "numeroperation" rather than "Numero Operation" it looks very bad.....but now as its not coming i will manually change above name...

well to be honest i don't see any problem using the bracket.