Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Calculating 'due date' from drop-down box

Discussion in 'Information Technology' started by Someone, Nov 6, 2005.

  1. Someone

    Someone
    Expand Collapse
    Guest

    Hello all

    I have a database that includes a drop-down box called 'deadline', which
    includes four values: 1 hour, 24 hours, 1 working day and 2 working days.
    At this stage, I should point out the working week is Monday to Friday. In
    addition, each of the four values is assigned a unique number (1-4, funnily
    enough).

    The main form itself shows the current date and time of the record that is
    being logged. I would like, if possible, to get the due date (and time -
    this is critically important) to automatically populate in, for example, a
    text box, based on whichever of the four options was chosen from the
    drop-down box. What I'm finding most difficult is how to omit weekends.

    Bearing in mind that I'm not an expert at VBA, I did find a link to this
    page http://www.mvps.org/access/datetime/date0012.htm, but I got confused.

    Could anyone be so kind to point me in the right direction to help me
    resolve this problem?

    Many thanks for your time
    M
     
  2. Loading...

    Similar Threads Forum Date
    SciTech Calculating the Rarity of a Fingerprint Breaking News Dec 13, 2010

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Just copy all of that code into a module, then use the following to add,
    say, 1 working day to a given date MyDate:

    dhAddWorkDaysA(1, MyDate)

    You can also take a look at the Access Answers column I wrote for Pinnacle
    Publication's Smart Access in September, 2004. You can download the column
    (and sample database) for free at
    http://www.accessmvp.com/DJSteele/SmartAccess.html

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Someone" <someone@somewhere.com> wrote in message
    news:%23ahsEmf4FHA.2640@TK2MSFTNGP09.phx.gbl...
    > Hello all
    >
    > I have a database that includes a drop-down box called 'deadline', which
    > includes four values: 1 hour, 24 hours, 1 working day and 2 working days.
    > At this stage, I should point out the working week is Monday to Friday.
    > In addition, each of the four values is assigned a unique number (1-4,
    > funnily enough).
    >
    > The main form itself shows the current date and time of the record that is
    > being logged. I would like, if possible, to get the due date (and time -
    > this is critically important) to automatically populate in, for example, a
    > text box, based on whichever of the four options was chosen from the
    > drop-down box. What I'm finding most difficult is how to omit weekends.
    >
    > Bearing in mind that I'm not an expert at VBA, I did find a link to this
    > page http://www.mvps.org/access/datetime/date0012.htm, but I got confused.
    >
    > Could anyone be so kind to point me in the right direction to help me
    > resolve this problem?
    >
    > Many thanks for your time
    > M
    >
     
  4. Mr M

    Mr M
    Expand Collapse
    Guest

    Hi Douglas

    Thank you very much for helping me. This did the trick. Where you have put
    '1', I replaced this with the name of the field, deadline (and MyDate with
    LogDate).

    Thing is, it appears the date returned is based on the Deadline ID, which is
    an autonumber (1-4 as described in my original post). So, for example, if
    the LogDate is 4 Nov 05 and the deadline is 2 working days, the date
    returned is 10 Nov 05 (4 days plus the weekend).

    How can I get the output to base its results on a figure that represents the
    actual deadline duration rather than the ID?

    Thanks
    M

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:u0lrwQg4FHA.1416@TK2MSFTNGP09.phx.gbl...
    > Just copy all of that code into a module, then use the following to add,
    > say, 1 working day to a given date MyDate:
    >
    > dhAddWorkDaysA(1, MyDate)
    >
    > You can also take a look at the Access Answers column I wrote for Pinnacle
    > Publication's Smart Access in September, 2004. You can download the column
    > (and sample database) for free at
    > http://www.accessmvp.com/DJSteele/SmartAccess.html
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    >
    > "Someone" <someone@somewhere.com> wrote in message
    > news:%23ahsEmf4FHA.2640@TK2MSFTNGP09.phx.gbl...
    >> Hello all
    >>
    >> I have a database that includes a drop-down box called 'deadline', which
    >> includes four values: 1 hour, 24 hours, 1 working day and 2 working days.
    >> At this stage, I should point out the working week is Monday to Friday.
    >> In addition, each of the four values is assigned a unique number (1-4,
    >> funnily enough).
    >>
    >> The main form itself shows the current date and time of the record that
    >> is being logged. I would like, if possible, to get the due date (and
    >> time - this is critically important) to automatically populate in, for
    >> example, a text box, based on whichever of the four options was chosen
    >> from the drop-down box. What I'm finding most difficult is how to omit
    >> weekends.
    >>
    >> Bearing in mind that I'm not an expert at VBA, I did find a link to this
    >> page http://www.mvps.org/access/datetime/date0012.htm, but I got
    >> confused.
    >>
    >> Could anyone be so kind to point me in the right direction to help me
    >> resolve this problem?
    >>
    >> Many thanks for your time
    >> M
    >>

    >
    >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    You need to put logic in wherever you're calling the code from anyhow, since
    you wanted 1 hour and 24 hours as options as well.

    You indicated 1 was 1 hour, 2 was 24 hours, 3 was 1 business day and 4 was 2
    business days. Your code could be something like

    Select Case Me.cboDeadline
    Case 1
    dtmDueDate = DateAdd("h", 1, LogDate)
    Case 2
    dtmDueDate = DateAdd("h", 24, LogDate)
    Case 3
    dtmDueDate = dhAddWorkDaysA(1, LogDate)
    Case 4
    dtmDueDate = dhAddWorkDaysA(2, LogDate)
    Case Else
    ' Error!
    End Select
    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Mr M" <someone@somewhere.com> wrote in message
    news:eiS597h4FHA.744@TK2MSFTNGP10.phx.gbl...
    > Hi Douglas
    >
    > Thank you very much for helping me. This did the trick. Where you have
    > put '1', I replaced this with the name of the field, deadline (and MyDate
    > with LogDate).
    >
    > Thing is, it appears the date returned is based on the Deadline ID, which
    > is an autonumber (1-4 as described in my original post). So, for example,
    > if the LogDate is 4 Nov 05 and the deadline is 2 working days, the date
    > returned is 10 Nov 05 (4 days plus the weekend).
    >
    > How can I get the output to base its results on a figure that represents
    > the actual deadline duration rather than the ID?
    >
    > Thanks
    > M
    >
    > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:u0lrwQg4FHA.1416@TK2MSFTNGP09.phx.gbl...
    >> Just copy all of that code into a module, then use the following to add,
    >> say, 1 working day to a given date MyDate:
    >>
    >> dhAddWorkDaysA(1, MyDate)
    >>
    >> You can also take a look at the Access Answers column I wrote for
    >> Pinnacle
    >> Publication's Smart Access in September, 2004. You can download the
    >> column
    >> (and sample database) for free at
    >> http://www.accessmvp.com/DJSteele/SmartAccess.html
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >>
    >> "Someone" <someone@somewhere.com> wrote in message
    >> news:%23ahsEmf4FHA.2640@TK2MSFTNGP09.phx.gbl...
    >>> Hello all
    >>>
    >>> I have a database that includes a drop-down box called 'deadline', which
    >>> includes four values: 1 hour, 24 hours, 1 working day and 2 working
    >>> days.
    >>> At this stage, I should point out the working week is Monday to Friday.
    >>> In addition, each of the four values is assigned a unique number (1-4,
    >>> funnily enough).
    >>>
    >>> The main form itself shows the current date and time of the record that
    >>> is being logged. I would like, if possible, to get the due date (and
    >>> time - this is critically important) to automatically populate in, for
    >>> example, a text box, based on whichever of the four options was chosen
    >>> from the drop-down box. What I'm finding most difficult is how to omit
    >>> weekends.
    >>>
    >>> Bearing in mind that I'm not an expert at VBA, I did find a link to this
    >>> page http://www.mvps.org/access/datetime/date0012.htm, but I got
    >>> confused.
    >>>
    >>> Could anyone be so kind to point me in the right direction to help me
    >>> resolve this problem?
    >>>
    >>> Many thanks for your time
    >>> M
    >>>

    >>
    >>

    >
    >
    >
     
  6. Someone

    Someone
    Expand Collapse
    Guest

    Hi Douglas

    Thanks again for your help.

    M

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:Ohr7GGl4FHA.1188@TK2MSFTNGP12.phx.gbl...
    You need to put logic in wherever you're calling the code from anyhow, since
    you wanted 1 hour and 24 hours as options as well.

    You indicated 1 was 1 hour, 2 was 24 hours, 3 was 1 business day and 4 was 2
    business days. Your code could be something like

    Select Case Me.cboDeadline
    Case 1
    dtmDueDate = DateAdd("h", 1, LogDate)
    Case 2
    dtmDueDate = DateAdd("h", 24, LogDate)
    Case 3
    dtmDueDate = dhAddWorkDaysA(1, LogDate)
    Case 4
    dtmDueDate = dhAddWorkDaysA(2, LogDate)
    Case Else
    ' Error!
    End Select
    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Mr M" <someone@somewhere.com> wrote in message
    news:eiS597h4FHA.744@TK2MSFTNGP10.phx.gbl...
    > Hi Douglas
    >
    > Thank you very much for helping me. This did the trick. Where you have
    > put '1', I replaced this with the name of the field, deadline (and MyDate
    > with LogDate).
    >
    > Thing is, it appears the date returned is based on the Deadline ID, which
    > is an autonumber (1-4 as described in my original post). So, for example,
    > if the LogDate is 4 Nov 05 and the deadline is 2 working days, the date
    > returned is 10 Nov 05 (4 days plus the weekend).
    >
    > How can I get the output to base its results on a figure that represents
    > the actual deadline duration rather than the ID?
    >
    > Thanks
    > M
    >
    > "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    > news:u0lrwQg4FHA.1416@TK2MSFTNGP09.phx.gbl...
    >> Just copy all of that code into a module, then use the following to add,
    >> say, 1 working day to a given date MyDate:
    >>
    >> dhAddWorkDaysA(1, MyDate)
    >>
    >> You can also take a look at the Access Answers column I wrote for
    >> Pinnacle
    >> Publication's Smart Access in September, 2004. You can download the
    >> column
    >> (and sample database) for free at
    >> http://www.accessmvp.com/DJSteele/SmartAccess.html
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >>
    >> "Someone" <someone@somewhere.com> wrote in message
    >> news:%23ahsEmf4FHA.2640@TK2MSFTNGP09.phx.gbl...
    >>> Hello all
    >>>
    >>> I have a database that includes a drop-down box called 'deadline', which
    >>> includes four values: 1 hour, 24 hours, 1 working day and 2 working
    >>> days.
    >>> At this stage, I should point out the working week is Monday to Friday.
    >>> In addition, each of the four values is assigned a unique number (1-4,
    >>> funnily enough).
    >>>
    >>> The main form itself shows the current date and time of the record that
    >>> is being logged. I would like, if possible, to get the due date (and
    >>> time - this is critically important) to automatically populate in, for
    >>> example, a text box, based on whichever of the four options was chosen
    >>> from the drop-down box. What I'm finding most difficult is how to omit
    >>> weekends.
    >>>
    >>> Bearing in mind that I'm not an expert at VBA, I did find a link to this
    >>> page http://www.mvps.org/access/datetime/date0012.htm, but I got
    >>> confused.
    >>>
    >>> Could anyone be so kind to point me in the right direction to help me
    >>> resolve this problem?
    >>>
    >>> Many thanks for your time
    >>> M
    >>>

    >>
    >>

    >
    >
    >
     

Share This Page