Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Steven Rebello
  Where is Steven Rebello?
 Bangalore
 India
 Steven Rebello
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: View with empty field
Thread ID: 345186 Message ID: 345216 # Views: 29 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Sunday, May 20, 2012 5:58:49 PM         
   


> >
> > Sorry, Anders, with the code (simplified to match yours below), I still get null values for the fields linked to the empty date..
> >
> >
SELECT  Workallocation.copr,oprwage.copr,Workallocation.ddate, ;
> >    Oprwage.dfromdate, oprwage.dtodate, EVL(Oprwage.dtodate,DATE()), Oprwage.namt;
> >  FROM C:\PANORAMA\WORKALLOCATION.DBF ;
> >    LEFT JOIN guinty!oprwage ;
> >    on Workallocation.ddate BETWEEN Oprwage.dfromdate AND EVL(Oprwage.dtodate,DATE()) ;
> > and Workallocation.copr = Oprwage.copr
> > 

> >
> > Regards,
> >
> > Steve
>
> As you see I didn't use LEFT JOIN. That's what's forcing NULL.
> In my sample data it makes no difference if I use INNER JOIN or LEFT JOIN.
>
> Could you post some of your data showing copr, ddate, dfromdate, and dtodate.
> With _VFP.DataToClip it's eas to create sopme ample data and paste it into Foxite.
>
> -Anders

Hi Anders, sorry, I did try using only the Join, but the record with the empty date was completely left out. Then I tried a left join, and managed to capture the 'left' part of the relationship in a record, but the values on the right showed only null values. Could you please let me know how to use datatoclip, or an easy way to display my tables on this forum? Thanks

ENTIRE THREAD

View with empty field Posted by Steven Rebello @ 5/19/2012 8:03:44 PM
RE: View with empty field Posted by tushar @ 5/20/2012 5:53:52 AM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 5:37:14 PM
RE: View with empty field Posted by Mustapha Bihmouten @ 5/20/2012 9:06:30 AM
RE: View with empty field Posted by Anders Altberg @ 5/20/2012 1:12:30 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 5:36:37 PM
RE: View with empty field Posted by Anders Altberg @ 5/20/2012 1:15:49 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 3:34:30 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 5:35:03 PM
RE: View with empty field Posted by Anders Altberg @ 5/20/2012 5:49:34 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 5:58:49 PM
RE: View with empty field Posted by Steven Rebello @ 5/20/2012 6:42:55 PM
RE: View with empty field Posted by Anders Altberg @ 5/20/2012 8:39:02 PM
RE: View with empty field Posted by Steven Rebello @ 5/24/2012 5:24:13 AM
RE: View with empty field Posted by Steven Rebello @ 5/24/2012 5:50:34 AM