Welcome to SPN

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

Sign Up Now!

Calculating Late Date in Access

Discussion in 'Information Technology' started by whiteboy, Jul 28, 2006.

  1. whiteboy

    whiteboy
    Expand Collapse
    Guest

    What's up Everyone?

    I really need some help. I've been pulling the hair out of my head
    trying to research how to calculate a late date. Here is what I'm
    trying to do:

    DATE VOUCHER SUBMITTED: 1 MAY 06
    VOUCHER LATE?: YES

    If the voucher is 5 days past the "DATE VOUCHER SUBMITTED" date, I'd
    like to have it autmatically update another field named "VOUCHER LATE"
    either with a yes/no answer. Is there a way to make this happen? If
    there is, I should be able to query this info and create a report
    right? Thanks in advance for your help.
     
  2. Loading...


  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Whiteboy,

    If you mean to have a 'Voucher Late' field in your table, that is
    unnecessary (and unwise too). I expect you can just do the calculation
    in a query whenever you need this information.

    Now, what I don't understand is how a voucher is 5 days late. Do you
    mean if the 'Date Voucher Submitted' is more that 5 days ago? If so,
    you can doa calculated field in a query like this...
    Voucher Late: (Date()-[DateVoucher Submitted])>5
    .... but I suspect I haven't quite grasped the concept - if you need more
    help, maybe you could post back with more details and an example.

    --
    Steve Schapel, Microsoft Access MVP


    whiteboy wrote:
    > What's up Everyone?
    >
    > I really need some help. I've been pulling the hair out of my head
    > trying to research how to calculate a late date. Here is what I'm
    > trying to do:
    >
    > DATE VOUCHER SUBMITTED: 1 MAY 06
    > VOUCHER LATE?: YES
    >
    > If the voucher is 5 days past the "DATE VOUCHER SUBMITTED" date, I'd
    > like to have it autmatically update another field named "VOUCHER LATE"
    > either with a yes/no answer. Is there a way to make this happen? If
    > there is, I should be able to query this info and create a report
    > right? Thanks in advance for your help.
    >
     
  4. Melba

    Melba
    Expand Collapse
    Guest

    "whiteboy" wrote:

    > What's up Everyone?
    >
    > I really need some help. I've been pulling the hair out of my head
    > trying to research how to calculate a late date. Here is what I'm
    > trying to do:
    >
    > DATE VOUCHER SUBMITTED: 1 MAY 06
    > VOUCHER LATE?: YES
    >
    > If the voucher is 5 days past the "DATE VOUCHER SUBMITTED" date, I'd
    > like to have it autmatically update another field named "VOUCHER LATE"
    > either with a yes/no answer. Is there a way to make this happen? If
    > there is, I should be able to query this info and create a report
    > right? Thanks in advance for your help.
    >
    >

    Public Function VouchertStatus(datRequired As Date, datRecvd As Date) As
    String

    If datRcvd> (datReq+ 5) Then
    VoucherStatus = "Overdue"
    Else
    VoucherStatus = "Ok"
    End If


    End Function


    The form calling the function would have at least the following three controls

    DatRequired, DatRecvd, Status

    In exit even of DatRecvd place the code

    Status = VoucherStatus(datRequired,DatRcvd)


    Get back if you r having further probs
     
  5. Melba

    Melba
    Expand Collapse
    Guest

    "whiteboy" wrote:

    > What's up Everyone?
    >
    > I really need some help. I've been pulling the hair out of my head
    > trying to research how to calculate a late date. Here is what I'm
    > trying to do:
    >
    > DATE VOUCHER SUBMITTED: 1 MAY 06
    > VOUCHER LATE?: YES
    >
    > If the voucher is 5 days past the "DATE VOUCHER SUBMITTED" date, I'd
    > like to have it autmatically update another field named "VOUCHER LATE"
    > either with a yes/no answer. Is there a way to make this happen? If
    > there is, I should be able to query this info and create a report
    > right? Thanks in advance for your help.
    >
    >


    Whiteboy

    PS

    You can of course replace the sting in the function with the words YES or NO

    The words DatRequired and DatRcvd are the control NAMES and they can of
    course be different from the Control Source in the properties of the control.

    Regards

    M
     
  6. whiteboy

    whiteboy
    Expand Collapse
    Guest

    Why is the Voucher Late field in my table unnecessary and unwise? I
    want to be able to see by viewing a record in a form if the voucher is
    late or not, how is that unnecessary or unwise? Thanks for your help
    though.
     
  7. whiteboy

    whiteboy
    Expand Collapse
    Guest

    Thank you very much Melba, I'm gonna give that a try.
     
  8. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Whiteboy,

    It is unnecessary because it is a calculated/derived value, so it can be
    calculated/derived whenever you need it, via a calculated field in the
    query that the form is based on, or via a calculated control on the
    form. This is very easy. It is unwise because you are using a
    database, and doing so flouts one of the core fundamental principles of
    database design.

    So, the point here is that it is perfectly valid for you to "want to be
    able to see by viewing a record in a form if the voucher is late or
    not", but it is not valid for this to be done via the storing of this
    information in your table.

    --
    Steve Schapel, Microsoft Access MVP


    whiteboy wrote:
    > Why is the Voucher Late field in my table unnecessary and unwise? I
    > want to be able to see by viewing a record in a form if the voucher is
    > late or not, how is that unnecessary or unwise? Thanks for your help
    > though.
    >
     
  9. Melba

    Melba
    Expand Collapse
    Guest

    "whiteboy" wrote:

    > Thank you very much Melba, I'm gonna give that a try.
    >
    >


    Whiteboy

    Hi

    I wondered if the function suggested in my previous post, did the trick

    Best Regards

    Melba
     

Share This Page