28 days from end of week?

[antheana]

Grrr I just lost the post so have to start again, I hope I don't miss anything!

Hello!!!

Ok, so I have a database (MS Access 2003), which has a Jobs table and has information about the client, client contact, job descriptions and most importantly:

InvoiceDate
InvoiceNumber
JobRate
JobExpenses
JobPayment
PaymentTerms
PaymentDueDate
InvoiceSent (checkbox)

Invoice sent will always be the same day as InvoiceDate, so no need for a text field, all others are.

So what I am trying to work out is how to calculate the PaymentDueDate, which has now been changed to 28 days from the Friday of the week the job finishes. So for example, if I finished a job on Monday 4 December 2006, my payment due date would be 28 days from Friday 8 December 2006.

I can no longer use [InvoiceDate}+28 to get this due date.

Any ideas?

[NeoPa]

Try :

[PaymentDueDate] = [InvoiceDate] + 35 - WeekDay([InvoiceDate])

InvDate + (7-WeekDay(InvDate)) + 28.

[antheana]

Thank you NeoPa,

Do you have a 'writing code for dummies version'?

I'm not sure where I am supposed to be putting this code. I initially placed it within the OnOpen routine for the form and that didn't work. I ied to insert it as the Control Source of the textfield and that didn't work either.

Should I even be trying to place this on the form? Or should it go on the table etc?

[NeoPa]

Thank you NeoPa,

Do you have a 'writing code for dummies version'?

I'm not sure where I am supposed to be putting this code. I initially placed it within the OnOpen routine for the form and that didn't work. I ied to insert it as the Control Source of the textfield and that didn't work either.

Should I even be trying to place this on the form? Or should it go on the table etc?

I would put it in the AfterUpdate event of the control for the InvoiceDate.
You should use the form controls in your calculation rather than the record fields though.

[antheana]

Hi there NeoPa,

Geeeenius! Thanks!!!! That worked, but like this:

[JobPaymentDate] = [JobInvoiceDate] + 35 - Weekday([JobInvoiceDate])
'JobInvoiceDate (7 - Weekday(JobInvoiceDate)) + 28

The second line didn't seem to work, so I had to leave it as a comment - what is it supposed to do?

Also, is there a way to set a if JobInvoiceDate = Null then set JobPaymentDate = "" and do nothing (i.e. if I remove the invoice date, it should clear the payment due date)

As I inserted dates to test it, when I removed the date, the jobpaymentdate still had a date in there and I got an error message asking me to debug because the JobInvoiceDate field was empty (onAfterUpdate)

Does that make sense?

[NeoPa]

[JobPaymentDate] = [JobInvoiceDate] + 35 - Weekday([JobInvoiceDate])
'JobInvoiceDate (7 - Weekday(JobInvoiceDate)) + 28

The second line didn't seem to work, so I had to leave it as a comment - what is it supposed to do?

The second line (being outside the code tags) was meant as a comment :).

Also, is there a way to set a if JobInvoiceDate = Null then set JobPaymentDate = "" and do nothing (i.e. if I remove the invoice date, it should clear the payment due date)

[JobPaymentDate] = IIf(IsNull([JobInvoiceDate]), "", [JobInvoiceDate] + 35 - Weekday([JobInvoiceDate]))

Although if [JobPaymentDate] is a date field then a Null would probably fit better than an empty string. The original version would return a null in that case as any calculation involving a Null always resolves to Null.

As I inserted dates to test it, when I removed the date, the jobpaymentdate still had a date in there and I got an error message asking me to debug because the JobInvoiceDate field was empty (onAfterUpdate)

Does that make sense?

Check which fields in your table will accept a null value (Required property=No).
If you want to be able to have an empty field in some records then that's the way to do it.

[antheana]

Thank you very much! I definitely need to read up on Access event procedures as I tried that several times and it still didn't work. I did a small bit of VB in college, so came up with this:

If [JobInvoiceDate] <> "" Then
[JobPaymentDate] = [JobInvoiceDate] + 35 - Weekday([JobInvoiceDate])
Else
[JobPaymentDate] = ""
End If

And that somehow worked! Basic I know, but I can't seem to get my head around some of the other coding methods.

Thank you for all of your help, it was very useful!

[NeoPa]

What you have there is roughly equivalent to what I had anyway and should work fine. Careful of putting empty strings (strings whether empty or not) in a date field though. This may throw up errors for you.
Lastly, the IIf() function I used is not absolutely necessary, but it is one of THE most useful functions ever invented (and essentially simpler in concept even than the If...Then...Else...End If construct. I cannot recommend strongly enough that you learn about it.

IIf Function

Returns one of two parts, depending on the evaluation of an expression.

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

Remarks

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.