querying VFP table on DATE datatype in SQL

[ralphcarter]

We are developing a VB app and are trying to create a recordset from a VFP table by filtering on a field with a DATE datatype. We continue to get the following errors.

[ODBC VISUAL FOXPRO DRIVER]MISSING OPERAND
or
OPERATOR/OPERAND TYPE MISMATCH

Below is an example of the code we are using. This particular code gets the missing operand error.

All help is appreciated

sSQL = "SELECT * From mbw_sale WHERE mbw_sale.rundate = #07/27/2007#;"

tblsale.Open sSQL, dbase1, adOpenStatic, adLockBatchOptimistic, adCmdText


Thanks
Ralph

 

[Zografski]

Try
sSQL = "SELECT * From mbw_sale WHERE mbw_sale.rundate = DATE(2007,07,27)

 

[Mike Lewis]

Hi Ralph,

Zografski has given you a good answer. Your problem was that we don't use # to delimit a date, as with other back ends.

Note also that you might need to remove the semi-colon at the end of the command. VFP uses semi-colons as a line continuation character, not a terminator. But I'm not sure about this. It could be that your client software expects the semi-colon.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

You could try:

sSQL = "SELECT * From mbw_sale WHERE DTOS(mbw_sale.rundate) = '20070727'"

BUT all depends what indexes you have, If you have an index that uses JUST rundate as key, then use Zografski's suggestion, that query will be optimizable. If you have index on DTOS(rundate) then use mine. If you didn't have any index by this field then there is no difference what you will use. Maybe mine is a little easier to build, but not much

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ralphcarter]

Thanks everyone but we have the problem solved. The problem was in the CONNECT string. We were using "DSN = VISUAL FOXPRO DATABASE" when we should have ben using "DSN = MICROSOFT VISUAL FOXPRO DRIVER".

Thanks again for the help

Ralph

 

[bborissov]

Ralph,
try to switch from ODBC to VFPOLEDB. ODBC driver for VFP wasn't updated since VFP6, if you or any other developer by any chance use new features of VFP (8 or 9) like AUTOINCREMENTing fields, BLOB, varchar, varbinary etc your application will crashed.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP