Using an Option Group to fill a text box on a report

[Guest]

Hey,

I have a report with a text box named txtGridDate in the report header.
It’s Control Source is =DateSerial(Year(Date()),Month(Date()),1)
and is formatted as mmmm/yyyy which gives me July/2005.

I would like to have a popup form with an Option Group to
enable a user to select one of the following.
Option1 = Previous Month
Option2 = Current Month
Option3 = Next Month
I know I can change the DateSerial by using a –1 month or a
+1 month, but can I do it via code so when they open the report
it will open to June/2005, July/2005 or August/2005.
Of course this would change each month but I only need to see
the current, previous or next regardless of what month is current.

Thanks,

 

[fredg]

Hey,

I have a report with a text box named txtGridDate in the report header.
It¡¦s Control Source is =DateSerial(Year(Date()),Month(Date()),1)
and is formatted as mmmm/yyyy which gives me July/2005.

I would like to have a popup form with an Option Group to
enable a user to select one of the following.
Option1 = Previous Month
Option2 = Current Month
Option3 = Next Month
I know I can change the DateSerial by using a ¡V1 month or a
+1 month, but can I do it via code so when they open the report
it will open to June/2005, July/2005 or August/2005.
Of course this would change each month but I only need to see
the current, previous or next regardless of what month is current.

Thanks,

Here is one way.
I assume your option values are currently either 1, 2, or 3.

=IIf([OptionGroup] = 1,DateSerial(Year(Date()),Month(Date())-1,1),
IIf([OptionGroup] = 2, DateSerial(Year(Date()),Month(Date()),1),
DateSerial(Year(Date()),Month(Date())+1,1)))

Alternatively, you can set the option values to
-1 (for previous month)
0 (for current month)
+1 (for next month)

Then simply code:
=DateSerial(Year(Date()),Month(Date()) + [OptionGroup],1)

 

[Guest]

Sorry and Thanks,
If the DateSerial goes in the text box on the report, how and on what event
in the form do I let the text box know which value has been selected?

Hey,

I have a report with a text box named txtGridDate in the report header.
It’s Control Source is =DateSerial(Year(Date()),Month(Date()),1)
and is formatted as mmmm/yyyy which gives me July/2005.

I would like to have a popup form with an Option Group to
enable a user to select one of the following.
Option1 = Previous Month
Option2 = Current Month
Option3 = Next Month
I know I can change the DateSerial by using a –1 month or a
+1 month, but can I do it via code so when they open the report
it will open to June/2005, July/2005 or August/2005.
Of course this would change each month but I only need to see
the current, previous or next regardless of what month is current.

Thanks,

Here is one way.
I assume your option values are currently either 1, 2, or 3.

=IIf([OptionGroup] = 1,DateSerial(Year(Date()),Month(Date())-1,1),
IIf([OptionGroup] = 2, DateSerial(Year(Date()),Month(Date()),1),
DateSerial(Year(Date()),Month(Date())+1,1)))

Alternatively, you can set the option values to
-1 (for previous month)
0 (for current month)
+1 (for next month)

Then simply code:
=DateSerial(Year(Date()),Month(Date()) + [OptionGroup],1)

 

[fredg]

Sorry and Thanks,
If the DateSerial goes in the text box on the report, how and on what event
in the form do I let the text box know which value has been selected?

Hey,

I have a report with a text box named txtGridDate in the report header.
It¡¦s Control Source is =DateSerial(Year(Date()),Month(Date()),1)
and is formatted as mmmm/yyyy which gives me July/2005.

I would like to have a popup form with an Option Group to
enable a user to select one of the following.
Option1 = Previous Month
Option2 = Current Month
Option3 = Next Month
I know I can change the DateSerial by using a ¡V1 month or a
+1 month, but can I do it via code so when they open the report
it will open to June/2005, July/2005 or August/2005.
Of course this would change each month but I only need to see
the current, previous or next regardless of what month is current.

Thanks,

Here is one way.
I assume your option values are currently either 1, 2, or 3.

=IIf([OptionGroup] = 1,DateSerial(Year(Date()),Month(Date())-1,1),
IIf([OptionGroup] = 2, DateSerial(Year(Date()),Month(Date()),1),
DateSerial(Year(Date()),Month(Date())+1,1)))

Alternatively, you can set the option values to
-1 (for previous month)
0 (for current month)
+1 (for next month)

Then simply code:
=DateSerial(Year(Date()),Month(Date()) + [OptionGroup],1)

Depending upon which expression you decide to use, change the
expression to:

=IIf(forms!FormName![OptionGroup] = 1, DateSerial(Year(Date()),
Month(Date())-1, 1), IIf(forms!FormName![OptionGroup] = 2,
DateSerial(Year(Date()),Month(Date()),1),
DateSerial(Year(Date()),Month(Date())+1,1)))

Or..

=DateSerial(Year(Date()),Month(Date()) + forms!FormName![OptionGroup],
1)


Note that there are no quote marks around the FormName in the above
syntax. Substitute the actual name of your form.
Where I have written [OptionGroup] substitute your actual option group
name, i.e. forms!frmMonthSelection!Frame1

The form must be open when the report is run.
You can close the form from the Report's Close event:
DoCmd.Close acForm, "FormName"

 

[Guest]

Thanks Fred,
Both methods will work, but I used the second method because the first
messed up some references to the text box in my report.
Thanks again, (Two good answers)
Vernon

Sorry and Thanks,
If the DateSerial goes in the text box on the report, how and on what event
in the form do I let the text box know which value has been selected?

On Sun, 3 Jul 2005 20:00:01 -0700, Vernon wrote:

Hey,

I have a report with a text box named txtGridDate in the report header.
It’s Control Source is =DateSerial(Year(Date()),Month(Date()),1)
and is formatted as mmmm/yyyy which gives me July/2005.

I would like to have a popup form with an Option Group to
enable a user to select one of the following.
Option1 = Previous Month
Option2 = Current Month
Option3 = Next Month
I know I can change the DateSerial by using a –1 month or a
+1 month, but can I do it via code so when they open the report
it will open to June/2005, July/2005 or August/2005.
Of course this would change each month but I only need to see
the current, previous or next regardless of what month is current.

Thanks,

Here is one way.
I assume your option values are currently either 1, 2, or 3.

=IIf([OptionGroup] = 1,DateSerial(Year(Date()),Month(Date())-1,1),
IIf([OptionGroup] = 2, DateSerial(Year(Date()),Month(Date()),1),
DateSerial(Year(Date()),Month(Date())+1,1)))

Alternatively, you can set the option values to
-1 (for previous month)
0 (for current month)
+1 (for next month)

Then simply code:
=DateSerial(Year(Date()),Month(Date()) + [OptionGroup],1)

Depending upon which expression you decide to use, change the
expression to:

=IIf(forms!FormName![OptionGroup] = 1, DateSerial(Year(Date()),
Month(Date())-1, 1), IIf(forms!FormName![OptionGroup] = 2,
DateSerial(Year(Date()),Month(Date()),1),
DateSerial(Year(Date()),Month(Date())+1,1)))

Or..

=DateSerial(Year(Date()),Month(Date()) + forms!FormName![OptionGroup],
1)


Note that there are no quote marks around the FormName in the above
syntax. Substitute the actual name of your form.
Where I have written [OptionGroup] substitute your actual option group
name, i.e. forms!frmMonthSelection!Frame1

The form must be open when the report is run.
You can close the form from the Report's Close event:
DoCmd.Close acForm, "FormName"

 

[Guest]

I'm still a beginner to Access and my question is very much related to this.
How do I make it so that the date is displayed like Vernon's? Specifically,
I want it to be formatted as MMMMM YYYY. I also need it to be displayed as
next month's MMMMM YYYY. For example, when I run the report in October, I
want the text box to read "November 2005".

Any help is much appreciated.

Thanks Fred,
Both methods will work, but I used the second method because the first
messed up some references to the text box in my report.
Thanks again, (Two good answers)
Vernon

Sorry and Thanks,
If the DateSerial goes in the text box on the report, how and on what event
in the form do I let the text box know which value has been selected?

:

On Sun, 3 Jul 2005 20:00:01 -0700, Vernon wrote:

Hey,

I have a report with a text box named txtGridDate in the report header.
It’s Control Source is =DateSerial(Year(Date()),Month(Date()),1)
and is formatted as mmmm/yyyy which gives me July/2005.

I would like to have a popup form with an Option Group to
enable a user to select one of the following.
Option1 = Previous Month
Option2 = Current Month
Option3 = Next Month
I know I can change the DateSerial by using a –1 month or a
+1 month, but can I do it via code so when they open the report
it will open to June/2005, July/2005 or August/2005.
Of course this would change each month but I only need to see
the current, previous or next regardless of what month is current.

Thanks,

Here is one way.
I assume your option values are currently either 1, 2, or 3.

=IIf([OptionGroup] = 1,DateSerial(Year(Date()),Month(Date())-1,1),
IIf([OptionGroup] = 2, DateSerial(Year(Date()),Month(Date()),1),
DateSerial(Year(Date()),Month(Date())+1,1)))

Alternatively, you can set the option values to
-1 (for previous month)
0 (for current month)
+1 (for next month)

Then simply code:
=DateSerial(Year(Date()),Month(Date()) + [OptionGroup],1)

Depending upon which expression you decide to use, change the
expression to:

=IIf(forms!FormName![OptionGroup] = 1, DateSerial(Year(Date()),
Month(Date())-1, 1), IIf(forms!FormName![OptionGroup] = 2,
DateSerial(Year(Date()),Month(Date()),1),
DateSerial(Year(Date()),Month(Date())+1,1)))

Or..

=DateSerial(Year(Date()),Month(Date()) + forms!FormName![OptionGroup],
1)


Note that there are no quote marks around the FormName in the above
syntax. Substitute the actual name of your form.
Where I have written [OptionGroup] substitute your actual option group
name, i.e. forms!frmMonthSelection!Frame1

The form must be open when the report is run.
You can close the form from the Report's Close event:
DoCmd.Close acForm, "FormName"