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
 Tags
Subject: View with empty field
Thread ID: 345186 Message ID: 345186 # Views: 44 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Saturday, May 19, 2012 8:03:44 PM         
   


Hi All,

I am trying to create a view where a wage value is fixed based on a relation from the work allocation table 'Opr' field and the 'Opr' stored in a wage table which has a wage period, defined by a dfromdate and a dtodate field. The view gets the wage period using the between clause, as the code below shows. Just one thing. The dtodate for the wage table is entered automatically through a program, when a new record is added. For example, when adding a dfromdate in the latest record, the previous record will get a dtodate which will be one day prior to the last record dfrom date. That way, there is date continuity so that any date in the wage allocation has a fixed associated period. Now the important part. The last record will always have an empty dtodate field, because unless the next date is defined, it will be left empty. Now the final problem. In the query below, it skips the record having the empty dtodate. I have copied the code. Could anyone help, please?

Thanks a lot!!

Regards,

Steve

SELECT Workallocation.ddate, Workallocation.copr,;
  Workallocation.nempno, Biodata.cname, Biodata.class, Biodata.gang,;
  Biodata.nempno, Workallocation.nindex, Oprwage.namt, Oprwage.dfromdate,;
  Oprwage.nserial;
 FROM ;
     C:\PANORAMA\WORKALLOCATION.DBF ;
    INNER JOIN GUINTY!BIODATA ;
   ON  Workallocation.nempno = Biodata.nempno ;
    INNER JOIN guinty!oprwage ;
   ON  Workallocation.copr = Oprwage.copr;
 WHERE  Workallocation.ddate BETWEEN Oprwage.dfromdate AND IIF (EMPTY(Oprwage.dtodate),DATE(),oprwage.dtodate);
ORDER BY Workallocation.ddate, Biodata.gang, Biodata.cname


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