Report Question

[Karen P.]

Hi. I'm fairly novice with Access and feel very fortunate to have found you! I have a report that generates invoices. The report has expressions that add the current yar. I need to change it for this month for invoices due in February (January's went out incorrectly). They are:

1.) =Val(Right$(Year(Now()),4) & Format(Str([Dues Month]),"00"))*1000+[Page]
This is the Invoice number in date form, i.e., 200202001 for first page.

2.) ="PAYMENT DUE ON OR BEFORE " & Str([Dues Month]) & "/1/" & Year(Now())
This should be the due date of 2/1/2003 for February invoices.

3.) =Str([Dues Month]) & "/1/" & Right$(Year(Now()),2) & " - " & DateAdd("d",-1,DateAdd("m",12,DateValue(Str([Dues Month]) & "/1/" & Year(Now()))))
This should be 2/1/03 - 1/31/2004.

Obviously the NOW needs to change but to what? Please advise.

 

[shay]

Wouldn't it be easier to print the reports after 31 December? Then you wouldn't need to change anything as Year(Now) would then be 2003. You'll still have a problem printing the Jan 2004 invoices, however.

For a proper solution, if you are printing the reports from a form (eg frmPrintInvoices), include a text box for the invoice date (eg txtInvDate). Then your calculations would be:

Val(Right$(Year(forms!frmPrintInvoices.txtInvDate),4) & Format(Str([Dues Month]),"00"))*1000+[Page]

"PAYMENT DUE ON OR BEFORE " & Str([Dues Month]) & "/1/" & Year(forms!frmPrintInvoices.txtInvDate))

Format([Forms]![frmPrintInvoices].[txtInvDate],"m/d/yyyy") & " - " & Format(DateAdd("d",-1,DateAdd("m",12,[Forms]![frmPrintInvoices].[txtInvDate])),"m/d/yyyy")

Not sure what [Dues Month] is but you could get rid of it altogether and get the month from Month(forms!frmPrintInvoices.txtInvDate)

hth

shay

 

[Karen P.]

It might be easier but I wouldn't learn the answer to the question. I inherited this database when I assumed this position in September. The REPORT is not a form, even if it should be, I'm not prepared to change it yet. I would really like to know how to change the syntax so that the correct year would appear on these REPORTS I want to generate. Anybody?

 

[shay]

I wasn't suggesting that you change your reports to forms!

From your post I wasn't sure how you print your invoices - you might be printing by opening the report directly or by clicking a button on a form.

If you are printing from a form, I suggest you include a new text box for the invoice date and use the amended formulae I gave in my last post. If you don't currently have a form and are opening the reports directly, you can easily create a form and put a button on it to print the report for you.

How does the report get [Dues Month] and is that the same as invoice month? If so, could you not change [Dues Month] to [Dues Date] and use the Month() and Year() functions to get what you need for your calculations? If you can do this, you could ignore all the above about using forms.

shay