×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Create SQL View no records returned

Create SQL View no records returned

Create SQL View no records returned

(OP)
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.

RE: Create SQL View no records returned

Quote:

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

RE: Create SQL View no records returned

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

CODE -->

where RemTable.CREATED > ?m.refdatetime 

Then set refdatetime = DATE() and USE or REQUERY() the view.

Bye, Olaf.



Olaf Doschke Software Engineering
https://www.doschke.name

RE: Create SQL View no records returned

Also, if RemTable.CREATED is a DateTime field (as opposed to a date), then you will need to do this:

SELECT .... WHERE TTOD(RemTable.CREATED) = DATE()


Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Create SQL View no records returned

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

RE: Create SQL View no records returned

(OP)
Thank you all. Olaf's method worked.

Question answered.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close