Calculate next month.

[Danden]

Hello,
I have an birthday report for the current month (august) and would like to make a button on the report that can calculate next month (september) and shows birthdays for that month.
Can somebody help me how to calculate next month?

 

[CJ_London]

you would need a query which is something like the following:

SELECT * FROM tblBirthdays WHERE month(BirthDate)=month(dateadd("m",1,date()))

Dateadd adds a month to the current date (Date() means today) and determines the month which it can then compare with the month of the birthdate.

This assumes that BirthDate is a date and not some other value.

 

[Danden]

Thank you. Now, I would like to have a "next" button on my report, which can switch to the next month (eg. August-September). How can I update data in an report with the new data by clicking on the button with your sql behind?

 

[CJ_London]

Assuming your data is displayed in thr form your button is on you would use:

me.recordsource="SELECT * FROM tblBirthdays WHERE month(BirthDate)=month(dateadd("m",1,date()))"

If your data is in a subform on a main form with your button then you would use

subformname.form.recordsource="SELECT * FROM tblBirthdays WHERE month(BirthDate)=month(dateadd("m",1,date()))"

where subformname is the name of your subform control (not the sourceobject)

 

[Danden]

I want a button on the report, not on the form. A button that lists up all birthdays from next months. Is it possible to do on the report? Maybe with an macro!
Something like this procedure:
-update birthdays on the report view with next months birthdays.
-change months name on the reports head.

 

[DavidAtWork]

what version of Access are you using as I didn't think that Access was capable of creating interactive reports.
You can certainly have the recordsource query based on user input which would give you the flexibility to run it for any month's birthdays.

David

 

[Danden]

I have Access 2007.

 

[CJ_London]

I want a button on the report, not on the form

Same thing applies, just on a report.

I don't use macro's so am unable to advise

change months name on the reports head.

I think you are asking too much of a report, and suggest what you should be using is a form.