Automatically call up YTD date range

[bravo6]

I have produced a financial report which allows the user to put in a monthly date range such as 06/01/07-06/30/07. They also put in YTD date range information so that the report shows the data for the month (June for example) in one column and YTD information (Jan 1-June30 for example) in another column.
Can anyone think of a way, in the query or form, for access to automatically determine the YTD date range from the monthly date range put in by the user. Currently the user must put both date ranges into the form. I'd like for them only to have to put in the monthly range and have access determine the YTD range from that.
Thanks for your help.

 

[RuralGuy]

Here's a link that should give you some ideas:

http://www.pacificdb.com.au/MVP/Code/Dates.htm

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[SkipVought]

Hi,

Something to date means one thing to me: You enter ONE value. Actually, all the user need enter is a MONTH and a YEAR, becauet the To-Date is TODAY.

From that one value, you could derive he year to data, quarter to date, month to date, week to date.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[SkipVought]

Actually do you need the user to input ANYTHING???

Year to date: DateSerial(Year(Date()),1,1)

Month to data: DateSerial(Year(Date()),Month(Date()),1)

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[bravo6]

Thanks Skip, That would work except that the YTD or MTD is not alway in this year or this month. In other words today 7/5/07 I may want to runthe report for 6/1/07--6/30/07 the Coresponding YTD would be 1/1/07-6/30/07.

Dave

 

[RuralGuy]

I believe the point was that the DateSerial() function will give you what you are asking for.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[bravo6]

How about if I used

=DatePart("m", [MonthEndDate])

So the user puts in the YTD 01/01/07-06/30/07 and the monthly column would only include data from June. In fact, if I put this in the report, then only one query (for YTD)would need to run and in the report it would populate the appropriate columns.

 

[RuralGuy]

It would seem to me that all the user needs to give you is the ending date and you could determine the other two dates. ie: Beginning of the month and beginning of the year.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[SkipVought]

6/1/07--6/30/07 is NOT month to date. It is MONTH Of report. Still only need Month and Year.

You've got to get your REQUIREMENTS straight. Exactly WHAT kind of reports are required?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}