Invoice Date

[Guest]

Hi, I hope this question has a simple enough answer.

I'm trying to create a database that will do the invoicing for my company
(currently done in Excel).

Every day I create invoices for the previous days work, but I have to
invoice it under the previous days date. I know that =Date()-1 does a rough
job, but the tricky part is on Monday's, where the previous work was done on
the Friday.

My access / coding skills are very rusty and I'm doing this project because
my company has no controls for there invoicing system and employees in the
past have managed to screw it up. Any help would be greatly appreciated.

 

[Douglas J Steele]

=DateAdd("d", IIf(Weekday(Date) = vbMonday, -3, -1), Date)

 

[Guest]

I added that line (exactly) as the default value in the Text Box in my form.
It's bringing up the error #Error. I removed an "I" from the "If" in that
statement to check if that was a typo and it gave me #Name? instead of the
#Error.

Would that be where I put that code and is there anything I have to change
in the code to customize it?

Thanks

 

[Douglas J Steele]

AFAIK, you can't use the intrinsic constant vbMonday in the Default
property. Replace it with the value of vbMonday (2):

=DateAdd("d",IIf(Weekday(Date())=2,-3,-1),Date())

 

[Brendan Reynolds]

Two things ...

1) You can only use intrinsic constants such as vbMonday in VBA code. In
expressions like this, or in queries, you have to use the literal value of
the constant, in this case, 2 ...

=DateAdd("d", IIf(Weekday(Date) = 2, -3, -1), Date)

2) Access will "helpfully" place square brackets around the word "Date" in
the above expression, then throw a wobbly because it can't find a field
called "Date". To prevent this , add empty parentheses after the word "Date"
to tell Access you mean the Date function and not a field named "Date" ...

=DateAdd("d", IIf(Weekday(Date()) = 2, -3, -1), Date())

 

[Guest]

Works now, Thank you very much

AFAIK, you can't use the intrinsic constant vbMonday in the Default
property. Replace it with the value of vbMonday (2):

=DateAdd("d",IIf(Weekday(Date())=2,-3,-1),Date())