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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
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,
Click to expand...

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)
 
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?


fredg said:
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,
Click to expand...

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)
Click to expand...
 
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?

fredg said:
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,
Click to expand...

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)
Click to expand...
Click to expand...

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"
 
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

fredg said:
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?

fredg said:
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)
Click to expand...
Click to expand...

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"
Click to expand...
 
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.



Vernon said:
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

fredg said:
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)
Click to expand...

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"
Click to expand...
Click to expand...
 
Back
Top