Results 1 to 2 of 2
  1. #1
    sukhjinder's Avatar
    sukhjinder is offline Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    43

    Query based upon dates

    Sir,
    In my organisation the employee goes on vacations. There actual vacation starts from monday till the completion of date. The previous any holiday(if comes)+saturday+ sunday (counted as previous leave pl) and later holiday(if comes)+saturday+sunday are counted as later (later leave ie ll). I have made few columns in table. First i linked the employee ID,type of leave,pldatefrm,pldateupto,leavefrm,leaveupto,llfr m.llupto, then i count pl (in days)=pldateupto-pldatefrm+1,leave=leaveupto-leavefrm+1,ll=llupto-llfrm total leave =pl+leave+ll

    The problem i am getting here is while query if a person goes on monday without previous leave then the pl should shows 0 (zero) but it value is blank. same case is with later leave



    I don't want this in form. is it possible to perform such a calculation in sql which take the value ie if plfrm= blank or then the value is equal to one . something like this

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    I do not follow your post. However there are often questions related to this topic. I will contribute this:

    * when one uses a Date Range there is only 2 fields; i.e. the start date (Jan 1 2016) and end date (Jan 7 2016). The dates in-between these 2 fields do not explicitly exist in the database. One can calculate/count the date range but it becomes extremely complicated to deal with holidays or weekends that are not to be included in the count. Also one cannot query precisely on a non existing date between the Start Date and End Date.

    * the alternative to a Date Range is to instead use a Date List i.e.
    Jan 1 2016
    Jan 2 2016
    Jan 3 2016
    Jan 4 2016
    Jan 5 2016
    Jan 6 2016
    Jan 7 2016

    This is much easier to work with in terms of queries and counts - one is able to not enter dates for holidays or weekends (if you are only recording vacation/leave days). But of course the data entry of a Date List is more work.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Based on Form Dates
    By weswilson88 in forum Forms
    Replies: 5
    Last Post: 03-25-2016, 04:27 PM
  2. Replies: 4
    Last Post: 11-03-2015, 01:51 PM
  3. Replies: 1
    Last Post: 10-18-2013, 03:14 PM
  4. Replies: 7
    Last Post: 07-11-2013, 10:45 AM
  5. Query based on dates ish
    By BigMac4 in forum Queries
    Replies: 4
    Last Post: 08-25-2012, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums