Calculating Late Date in Access

[whiteboy]

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.

 

[Steve Schapel]

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.

 

[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.

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

 

[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.

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

 

[whiteboy]

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.

 

[whiteboy]

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

 

[Steve Schapel]

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.

 

[Guest]

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