Completely lost with ExecuteSQL

[Peter Barfield]

Just trying to learn this executeSQL and can not seem to get sensible results.

I have a table which represents any cost prices changes that occur with a product.

If I use a simple statement eg ExecuteSQL ( 
"SELECT ProductDescription 
FROM  CostPriceChanges"
;"";",")

i get a nice list of products show up eg Sweet Potatoes, Limes, Limes, Lettuce, etc etc

However when i add where criteria I get the dreaded ?

ExecuteSQL ( 
"SELECT ProductDescription 
FROM  CostPriceChanges
WHERE Cost Price >= .01"
;"";",")

I am thinking, it is something in the syntax of the where that i am not getting into the grey matter but that is where i am having trouble.

Any Help would be appreciated.

[beverly]

You cannot have spaces in names

try \"Cost Price\"

 

[Peter Barfield]

Thanks beverly, that works only new to this SQL thingy. my next problem which i hope is as simple is, when I select

SELECT ProductDescription,\"Cost Price\",\"RRP Price\",Date
FROM CostPriceChanges

I get an error. So in other words using your above solutions it works for both Cost price and rrp price but as soon as i throw in the Date field it breaks.

Also I still get no joy whatsoever when using the WHERE clause if for eg I say WHERE ProductDescription = "Limes" it fails or Just Limes without quotes also fails. Is their a strict format that i am missing?  I thought that part was pretty straight forward but sadly no.

[comment]

16 minutes ago, Peter Barfield said:

 as soon as i throw in the Date field it breaks.

"Date" is a reserved word in SQL. Just get into the habit of quoting your field names.

 

20 minutes ago, Peter Barfield said:

when using the WHERE clause if for eg I say WHERE ProductDescription = "Limes" it fails or Just Limes without quotes also fails.

Use single quotes for text constants.

[Peter Barfield]

thanks comment, I have just finished playing around and did in fact just created another field for the purpose of testing called it datework and yes that worked. going to try and get the other where part working now. Quick question though is there any rules to using global fields in the where criteria. just purely for eg where productdescription = _global_productdescription field.?

[comment]

While it will work the way you wrote it, I would suggest you use the arguments parameter of the ExecuteSQL() function for this, i.e. write:

WHERE productdescription = ? 

and add YourTable::_global_productdescription as a parameter (see the help for an example). Among other things, this has the advantage of not having to  worry about the field name meeting SQL standards.

Note also that a field name inserted as text into a calculation formula will not be automatically updated if your rename the field. There are various techniques to get around the problem by using the GetFieldName() function.