Querying Fox Pro table in VB 2

[PeterOcean]

SELECT * FROM PolicyChanges WHERE PolicyID = '005C0UAP3E' AND FieldName = 'Cancel Date' AND DateTime > {^09/04/2002 12:00:00 AM}

The above gives me a record when I run it in Fox Pro, However, when i try via VB I get nothing back.

Any ideas??

 

[vb5prgrmr]

Try

"SELECT * FROM PolicyChanges WHERE PolicyID = '005C0UAP3E' AND FieldName = 'Cancel Date' AND DateTime > date(2002,09,04) AND DateTime > 12:00:00"

 

[CCLINT]

Because of this:

AND DateTime > {^09/04/2002 12:00:00 AM}


Is DateTime a field name?


[/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[CCLINT]

If so, then:

AND DateTime >=#09/04/2002 12:00:00 AM#" [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[PeterOcean]

DateTime is the field name.

I tried also tried vb5prgrmr suggestion and it doesn't work.
The frustrating thing about this is that it was working fine Friday.

 

[CCLINT]

You may also want to enclose the field name in brackets: [DateTime] [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[vb5prgrmr]

Whoops, Sorry Forgot the single ticks (')

"SELECT * FROM PolicyChanges WHERE PolicyID = '005C0UAP3E' AND FieldName = 'Cancel Date' AND DateTime > date(2002,09,04) AND DateTime > '12:00:00'"

placed around the time part. The date function passed in as a string is something built into VFP. I feel your pain as this was the only way I could access a system a few years back.

 

[CCLINT]

AND DateTime >='09/04/2002 12:00:00 AM'"

And using # is also not compatible... [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[PeterOcean]

I have got this working. My original SQL statement worked fine. the reason that it din't work was due to a slight oversight on my part. I don't want to go into details as it's a bit embarasing.

Thanks for everyones help.