calculate month to date 1

[DLynnTX]

I am trying to set up a report that sums month to date data. I have the user enter the report date and the dates sales data is reported. The I was using Format(Now(),'yyyymm') to get a total for the month. It works as long as the report is run the day after (i.e. run on 10/29 for 10/28 data gives totals for 10/1 - 10/28). But if you run the 10/29 report on 10/31 it gives you the totals all the way through 10/30 rather than 10/29. Does that make sense? I really don't know where to go from here. Any help would be GREATLY appreciated.

 

[dhookom]

What do you want to happen? Do you want to simply specify a date range for your report?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DLynnTX]

Yes, but I want it to change based on what the user types as the report date (i.e. they type 10-28-06, then data would be from 10-1-06 through 10-28-06; they type 10-31-06 and the data is from 10-1-06 through 10-31-06).

 

[dhookom]

Set the criteria to something like:

Between DateAdd("D",-Day([Forms]![frmDates]![YourDate])+1,[Forms]![frmDates]![YourDate]) And [Forms]![frmDates]![YourDate]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DLynnTX]

That worked (I replaced the forms info with [Enter date]) But I also read what you put in the post before mine about using a form to get data from the user - I will check that post again to see how to do that - I've never done that before. Thank you for your help!

 

[DLynnTX]

Was hoping the other thread would get updated by the time I finished I have created the form and have changed all of the queries that I'm using for the report to use the date in the form. However, I don't know how to get the form to open and wait for data to be entered and then continue with the report. I'm using a macro that opens the queries, then the report. So I changed the macro to start by opening the form. That doesn't work. What am I missing? Should I start a new thread for this?

 

[DLynnTX]

I think I'll just add a button to the form that runs the already created macro - that should work. Thank you again for your help. I have struggled with this for hours upon end before coming to this forum!!!

 

[dhookom]

Never struggle for hours when you have a great resource like tek-tips and others. A query can't "open" a form. The form should be open first and then run your reports/forms.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]