FoxPro ODBC Null Date Problem

[cfdoogan]

I have an old FoxPro database that I am working with and I am trying to look for NULL dates. When using DBFViewer they appear as " / / ". When passed through ODBC they come out as 1899/12/30. I have not been able to search for these blank dates successfully using the normal means (NULL- " / / " - CreateODBCDateTime('1899/12/30')). Any ideas? Thanks for your assistance.

[philh]

Did you try the Fox NULL value (".null.") in your query? Or the Fox CTOD() function? These should work through the Fox driver.

[cfdoogan]

PhilH Thanks!

Searching for date = ctod("/ / ") works like a charm. Wasn't able to get (".null.") working, produced Operator/operand type mismatch error. Probably syntax error on my part. Could you demonstrate? Thanks a million, now I can get this stuff out of VFP!

[philh]

You can use the ODBC curly-bracket syntax to pass a date value to VFP. Or, as you've discovered, you can use many of the VFP functions because they're built into the driver.

Another neat trick is that you can write a VFP program, compile it, place in the system32 directory, and use it as a function in your queries. We have a fiendishly complex pricing algorithm that we packaged in this manner, and it works like a charm.

HTH,

[cfdoogan]

Thanks again! I got both of these to return the information I need:
date = { / / }
date = {".null."}

Thanks for the heads up on the VFP function!