Create SQL View no records returned

[Puebles]

Hello,

I am trying to replicate a program I had used from memory. As I recall I had this issue when I first wrote it. The problem is that I am accessing remote data that has a datetime field and I want only the records for today. So, I used CREATED>date(). But, I get no records. Yes, there is data in the remote view for today. I run the same query (different SQL) in Access and get the proper data returned.

Here is the test code:
CLOSE DATABASES all
CLOSE TABLES all
OPEN DATABASE data2

CREATE SQL VIEW MyView REMOTE CONNECTION Connect1 as ;
SELECT RemTable.TRACKING_NUM, ;
RemTable.STATUS, ;
RemTable.CREATED, ;
RemTable.MODIFIED ;
FROM Schema.RemTable RemTable ;
where RemTable.CREATED > DATE()

=MESSAGEBOX("Done")

DELETE VIEW MyView

What did I forget?

Thank you in advance.

 

[Mike Lewis]

I want only the records for today.

Then you need to test for RemTable.CREATED = DATE(), not RemTable.CREATED > DATE().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You can't use a VFP function DATE() on a remote backend. You could run it with a parameter:

where RemTable.CREATED > ?m.refdatetime

Then set [tt]refdatetime = DATE()[/tt] and USE or REQUERY() the view.

Bye, Olaf.





Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Also, if RemTable.CREATED is a DateTime field (as opposed to a date), then you will need to do this:
[tt]
SELECT .... WHERE TTOD(RemTable.CREATED) = DATE()[/tt]

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Forget what I said about TTOD(). Olaf is right. But you will still need to use = rather than >.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Puebles]

Thank you all. Olaf's method worked.

Question answered.