Calculating dates using option buttons

[eup310]

I'm trying to create a form that will allow the user to automatically fill in the beginning and ending date as search criteria using option buttons.

Example, the user would select option buttons 1) Month To Day or 2) Last Month or 3) Year To Day or 4) Last Year, etc and the dates would populate the corresponding fields on the form. The user would then select the report they want to create based on those dates.

It "seems" easy but I can't wrap my head around it. Any help or direction would be greatly appreciated.

 

[Joe4]

I would probably recommend creating a User Defined Function (UDF) that does the calculation.
The UDF would probably take in one paramter, the option selected, and do the calculation and return the date.

If you need help, you need to tell us the different options, and what dates they would correlate to (i.e. when you say "Last Month", do you want the first or last day of the month)?

 

[eup310]

Thanks for the quick response Joe4.
I'm looking to summarize data between two dates. In this case, "Last Month" would populate two fields - Beginning date = the first day of the previous month and Ending date = last day of the previous month. And those date would, of course, depend on what the current date is. An easier way may be to just select a month or just enter the beginning and ending dates but I thought I might make it easier for the end user by just giving them radio buttons to select.

This is why I'm having difficulty finding a solution. I can't seem to explain what I'm looking for in simple terms.

Thanks again!

 

[Joe4]

What are all the different options you would like to present them with (in addition to "Last Month")?
How would you like the information being returned to be presented to them? In a Query, Form, or Report?

 

[eup310]

All I'm wanting to do is populate the beginning date and ending date into two fields on a menu form. Then I will use those dates as filters to run reports based on button selections on the form.
I'm not sure which options I will end up using but probably something like Week To Date, Month to Date, Year To Date, Previous Week, Previous Month, Previous Year.
I was hoping I could use an example and figure out how to add more selections as I work through the build.

 

[Joe4]

OK, let's say that on your form, you have the following fields:
optLastMonth - Option Button for last month
txtBegin - Text Field for beginning date
txtEnd - Text Field for ending date

Then we can add VBA code to the Click event of the Option Button to populate our two Text Fields.
The VBA code would look something like this:

Private Sub optLastMonth_Click()
    Me.txtBegin = DateSerial(Year(Date), Month(Date) - 1, 1)
    Me.txtEnd = DateSerial(Year(Date), Month(Date), 0)
End Sub

Here is a list of date calculations in Access VBA that you might find helpful for your other options too: https://support.microsoft.com/en-us...-and-for-displaying-date-time-values-in-acces

 

[eup310]

I can work with that! You're my hero for the day! Thanks Joe4!

 

[Joe4]

You are welcome.
Glad I was able to help!