Option group in a form as criteria for report

[Guest]

Option group in a form as criteria for report

I have created a dada base that stores information regarding continuing
education of employees. I have created a form with two option groups that are
intended to serve as criteria for a single report. One option group has two
choices “All States†and the second option is a combo box where the user can
select a State. The intention for this option group is that when the combo
box option is selected and a state is selected the report will filtered based
on the state selected.

The second option group requires user to select date ranges. Option one is
YTD, the second option is “Beginning Date†and “End Date†and the Third
option all dates.

Just to clarify I have only one report that is supposed to be filtered based
on the option groups in the form.

I was trying to write a code (based on some examples i have in from
downloaded databases from the Microsoft site) but it doesn’t seem to work. I
am relatively new user less then 2 months but I’ve been successful in
modifying examples from other databases to be used in this one.

I understand that what I’m trying to do may be more complex then the usual
questions in site. I would appreciate any help on this matter and any
suggestions regarding making the task possibly more simple.

Thanks much in advance for thinking about this problem.

 

[Allen Browne]

The option groups are not really needed. Just build the WhereCondition from
only the boxes where the user entered something.

This example shows how to test if the date boxes are null, and show all
records if they are:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

You can do the same with the State box, using AND between the 2 conditions.

For an example of how to use many optional criteria, and build the
WhereCondition from those the user chooses, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Guest]

Thank you very much for the valuable infomation. I will consider revising the
form all together, although i am still not clear about what you ment when you
worte "You can do the same with the State box, using AND between the 2
conditions".

I was also woundering if you/or anyone else can address the option group
selection and in particular how to assigne each option it's criteria. i
looked for additional help on your impressive website however i could not
find an example that show how to work with option groups for report criteria
purposes.

I appreciate much your help thus far and i look forward for anything else
regarding this matter.

 

[Guest]

Option group in a form as criteria for report

I have created a dada base that stores information regarding continuing
education of employees. I have created a form with two option groups that are
intended to serve as criteria for a single report. One option group has two
choices “All States†and the second option is a combo box where the user can
select a State. The intention for this option group is that when the combo
box option is selected and a state is selected the report will filtered based
on the state selected.

The second option group requires user to select date ranges. Option one is
YTD, the second option is “Beginning Date†and “End Date†and the Third
option all dates.

Just to clarify I have only one report that is supposed to be filtered based
on the option groups in the form.

I was trying to write a code (based on some examples i have in from
downloaded databases from the Microsoft site) but it doesn’t seem to work. I
am relatively new user less then 2 months but I’ve been successful in
modifying examples from other databases to be used in this one.

I understand that what I’m trying to do may be more complex then the usual
questions in site. I would appreciate any help on this matter and any
suggestions regarding making the task possibly more simple.

Thanks much in advance for thinking about this problem.

 

[Pieter Wijnen]

A hint:
I Always Create a Table Called DUAL (Oracle people will recognize it) with
one field & one record

CREATE TABLE DUAL (X Char(1));

INSERT INTO DUAL (X) VALUES ('X');

Then you can combine Individual states with an All option

SELECT STATE FROM STATES
UNION ALL
SELECT '<All>' FROM DUAL
Order By 1

Now for Your actual question

Sub CmdReport_Click()
Dim thW As String
If Me.cboStates <> "<All>" Then
thW = "STATE='" & Me.cboStates & "'"
End If

Select Case Me.optDates
Case 0: ' YTD
thW = IIF(Len(thW)," AND ",VbNullString) & "DATEField <=Date()"
Case 1:
thW = IIF(Len(thW)," AND ",VbNullString) & "DATEField Between #" & _
Format(Nz(Me.StartDate,0),"mm/dd/yyyy") & "# AND #" &
Format(Nz(Me.EndDate,Date()),"mm/dd/yyyy")
Case 2:
' Nothing
End Select

If Len(thW) Then
DoCmd.OpenReport "RepName", AcViewPreview,WhereCondition:=thW
Else
DoCmd.OpenReport "RepName", AcViewPreview
End If
End Sub

HTH

Pieter

 

[Pieter Wijnen]

A hint:
I Always Create a Table Called DUAL (Oracle people will recognize it) with
one field & one record

CREATE TABLE DUAL (X Char(1));

INSERT INTO DUAL (X) VALUES ('X');

Then you can combine Individual states with an All option

SELECT STATE FROM STATES
UNION ALL
SELECT '<All>' FROM DUAL
Order By 1

Now for Your actual question

Sub CmdReport_Click()
Dim thW As String
If Me.cboStates <> "<All>" Then
thW = "STATE='" & Me.cboStates & "'"
End If

Select Case Me.optDates
Case 0: ' YTD
thW = IIF(Len(thW)," AND ",VbNullString) & "DATEField <=Date()"
Case 1:
thW = IIF(Len(thW)," AND ",VbNullString) & "DATEField Between #" & _
Format(Nz(Me.StartDate,0),"mm/dd/yyyy") & "# AND #" &
Format(Nz(Me.EndDate,Date()),"mm/dd/yyyy")
Case 2:
' Nothing
End Select

If Len(thW) Then
DoCmd.OpenReport "RepName", AcViewPreview,WhereCondition:=thW
Else
DoCmd.OpenReport "RepName", AcViewPreview
End If
End Sub

HTH

Pieter




--

 

[Guest]

Sorry Pieter, i don't seem to follow your "hint". I apologize, I don’t
understand how to create a "TABLE DUAL". It is somewhat frustrating to see a
possible solution to my issue yet not being able to implement it due to lack
of knowledge.
If there is an example of a TABLE DUAL you can direct me too that would be
great. Hopefully then I would be able to implement the rest of the code.

I greatly appreciate you taking the time to respond.

 

[Pieter Wijnen]

Just paste the Text into the SQL pane of a Query & Execute
or Create a Table Using the GUI with the one field
it doesn't really matter what type of field as you will never want to read
it's value

The same applies to statement number 2
ie Paste it into the SQL pane
or open the newly created table and add the value

Pieter

 

[Pieter Wijnen]

Just paste the Text into the SQL pane of a Query & Execute
or Create a Table Using the GUI with the one field
it doesn't really matter what type of field as you will never want to read
it's value

The same applies to statement number 2
ie Paste it into the SQL pane
or open the newly created table and add the value

Pieter

Sorry Pieter, i don't seem to follow your "hint". I apologize, I don't
understand how to create a "TABLE DUAL". It is somewhat frustrating to
see a
possible solution to my issue yet not being able to implement it due to
lack
of knowledge.
If there is an example of a TABLE DUAL you can direct me too that would be
great. Hopefully then I would be able to implement the rest of the code.

I greatly appreciate you taking the time to respond.

--

 

[Allen Browne]

Well, I take it you want an option groupwith 3 options that looks something
like this:
[o] Begin date [ ]
[o] End Date [ ]
[o] All Dates

The text boxes for entering the dates are not part of the option group, so
presumably you want to write code in the AfterUpdate event of the option
group, so that if they choose the first option, it enables the text box
beside that, and disables the text box on the 2nd row and perhaps sets that
one to Null. This would involve a Select Case statement.

My suggestion was that this interface only makes it harder for the user to
enter their limiting dates:
- Now they have to choose the option group as well as entering the dates
(why?).
- There is still no choice for entring a range of dates, so perhaps you want
a 4th option:
[o] From [ ] to [ ]
Now it's worse: the user can enter a range, but you have more spurious
controls to deal with: 2 for the starting date, and 2 for the ending date,
and you *still* have to deal with what to do if the user chooses the option
but leaves the text box(es) blank.

Why not just provide the 2 text boxes:
From [ ] to [ ]
and interpret them like this:
From date only All records from that date onwards
To date only All record up to and including that date
Both dates Only records between the dates (both inclusive)
Neither No filter applied on this field

Hopefully I have given you enough detail that if you want to do it with the
option group you can, but the extra complexity doesn't end there. Now you
have to interpret the Value of the option group as well as the values of the
controls (which still have to be texted for Null), in your
WhereCondition/Filter string.

The suggestion to use AND between 2 condition means that ultimate your
Where string will contain:
"([State] = ""NY"") AND ([EventDate] Between #1/1/2006# And
#12/31/2006#)"
You see that this string is made up of 2 parts, with AND in the middle.

You can mock up a query, switch it to SQL View (View menu in query design),
and find the WHERE clause for an example. The Where string you build must be
just like the WHERE clause of a query.

 

[Guest]

Allen AND Pieter:
I was able to come up with a workable code based on all the information you
and pieter provided me.

it is a mishmash of both of your codes (and some other codes from sample
microsoft databases) in what i thought was the appropriate places and
supprizingly it seems to work (see the code for the form below). Although i
don't know if it would continue to work with no problems... if not then i'll
resort back to test your and pieter suggestions in full....

i would like to take this oppertunity and thank you and pieter for taking
the time. i am rather new to access and any code work i do is rather
intuitive modifications of existing code. I think it's wonderful that both of
you share the knowledge with novice users like me, so thanks again.

The form has two option groups:
grpState
[] all state
[] Select state [cmbState]

grpDateRange
[] YTD
[] Date Range
Beggining Date [StartDate]
End Date [EndDate]
[] all Dates
here is the code for the criteria form. i would welcome any other
suggestions:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Me!State.Enabled = False
Me!StartDate.Enabled = False
Me!EndDate.Enabled = False

End Sub

Private Sub grpDateRange_AfterUpdate()
' Enable Begging Date and End Date text boxs if user selected Date Range
Option '

Const conDateRange = 2

If Me!grpDateRange.Value = conDateRange Then
Me!StartDate.Enabled = True
Me!EndDate.Enabled = True
Else
Me!StartDate.Enabled = False
Me!EndDate.Enabled = False
End If

End Sub

Private Sub grpState_AfterUpdate()
' Enable State combo box if user selected Select State Option '


Const conSelectState = 2

If Me!grpState.Value = conSelectState Then
Me!State.Enabled = True
Else
Me!State.Enabled = False
End If

End Sub

Private Sub PrintReport_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Dim datDateFrom As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptMACEC"
strField = "CEDate"

'State filter option
Select Case Me.grpState
'All States
Case 1
'no filter
strWhere = "1 = 1"
'State Filter
Case 2
strWhere = "State='" & Me.State & "'"
End Select

'Date range filter
Select Case Me.grpDateRange
'YTD
Case 1
datDateFrom = DateSerial(Year(Date), 1, 1)
strWhere = strWhere & " AND ([CEDate] >= #" & datDateFrom & "#)"
& _
" AND ([CEDate] < #" & Date + 1 & "#)"

'Date Range
Case 2
If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.EndDate,
conDateFormat)
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.StartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.StartDate,
conDateFormat) _
& " And " & Format(Me.EndDate, conDateFormat)
End If
End If
End Select

' Hide me so reports can do their thing
Me.Visible = False
' Set an error trap in case the report NoData event fires and cancels
On Error Resume Next

DoCmd.OpenReport strReport, acViewPreview, , strWhere

DoCmd.Close acForm, Me.Name
End Sub

Well, I take it you want an option groupwith 3 options that looks something
like this:
[o] Begin date [ ]
[o] End Date [ ]
[o] All Dates

The text boxes for entering the dates are not part of the option group, so
presumably you want to write code in the AfterUpdate event of the option
group, so that if they choose the first option, it enables the text box
beside that, and disables the text box on the 2nd row and perhaps sets that
one to Null. This would involve a Select Case statement.

My suggestion was that this interface only makes it harder for the user to
enter their limiting dates:
- Now they have to choose the option group as well as entering the dates
(why?).
- There is still no choice for entring a range of dates, so perhaps you want
a 4th option:
[o] From [ ] to [ ]
Now it's worse: the user can enter a range, but you have more spurious
controls to deal with: 2 for the starting date, and 2 for the ending date,
and you *still* have to deal with what to do if the user chooses the option
but leaves the text box(es) blank.

Why not just provide the 2 text boxes:
From [ ] to [ ]
and interpret them like this:
From date only All records from that date onwards
To date only All record up to and including that date
Both dates Only records between the dates (both inclusive)
Neither No filter applied on this field

Hopefully I have given you enough detail that if you want to do it with the
option group you can, but the extra complexity doesn't end there. Now you
have to interpret the Value of the option group as well as the values of the
controls (which still have to be texted for Null), in your
WhereCondition/Filter string.

The suggestion to use AND between 2 condition means that ultimate your
Where string will contain:
"([State] = ""NY"") AND ([EventDate] Between #1/1/2006# And
#12/31/2006#)"
You see that this string is made up of 2 parts, with AND in the middle.

You can mock up a query, switch it to SQL View (View menu in query design),
and find the WHERE clause for an example. The Where string you build must be
just like the WHERE clause of a query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you very much for the valuable infomation. I will consider revising
the
form all together, although i am still not clear about what you ment when
you
worte "You can do the same with the State box, using AND between the 2
conditions".

I was also woundering if you/or anyone else can address the option group
selection and in particular how to assigne each option it's criteria. i
looked for additional help on your impressive website however i could not
find an example that show how to work with option groups for report
criteria
purposes.

I appreciate much your help thus far and i look forward for anything else
regarding this matter.

 

[Guest]

Allen AND Pieter:
I was able to come up with a workable code based on all the information you
and pieter provided me.

it is a mishmash of both of your codes (and some other codes from sample
microsoft databases) in what i thought was the appropriate places and
supprizingly it seems to work (see the code for the form below). Although i
don't know if it would continue to work with no problems... if not then i'll
resort back to test your and pieter suggestions in full....

i would like to take this oppertunity and thank you and pieter for taking
the time. i am rather new to access and any code work i do is rather
intuitive modifications of existing code. I think it's wonderful that both of
you share the knowledge with novice users like me, so thanks again.

The form has two option groups:
grpState
[] all state
[] Select state [cmbState]

grpDateRange
[] YTD
[] Date Range
Beggining Date [StartDate]
End Date [EndDate]
[] all Dates
here is the code for the criteria form. i would welcome any other
suggestions:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Me!State.Enabled = False
Me!StartDate.Enabled = False
Me!EndDate.Enabled = False

End Sub

Private Sub grpDateRange_AfterUpdate()
' Enable Begging Date and End Date text boxs if user selected Date Range
Option '

Const conDateRange = 2

If Me!grpDateRange.Value = conDateRange Then
Me!StartDate.Enabled = True
Me!EndDate.Enabled = True
Else
Me!StartDate.Enabled = False
Me!EndDate.Enabled = False
End If

End Sub

Private Sub grpState_AfterUpdate()
' Enable State combo box if user selected Select State Option '


Const conSelectState = 2

If Me!grpState.Value = conSelectState Then
Me!State.Enabled = True
Else
Me!State.Enabled = False
End If

End Sub

Private Sub PrintReport_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Dim datDateFrom As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptMACEC"
strField = "CEDate"

'State filter option
Select Case Me.grpState
'All States
Case 1
'no filter
strWhere = "1 = 1"
'State Filter
Case 2
strWhere = "State='" & Me.State & "'"
End Select

'Date range filter
Select Case Me.grpDateRange
'YTD
Case 1
datDateFrom = DateSerial(Year(Date), 1, 1)
strWhere = strWhere & " AND ([CEDate] >= #" & datDateFrom & "#)"
& _
" AND ([CEDate] < #" & Date + 1 & "#)"

'Date Range
Case 2
If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.EndDate,
conDateFormat)
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.StartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.StartDate,
conDateFormat) _
& " And " & Format(Me.EndDate, conDateFormat)
End If
End If
End Select

' Hide me so reports can do their thing
Me.Visible = False
' Set an error trap in case the report NoData event fires and cancels
On Error Resume Next

DoCmd.OpenReport strReport, acViewPreview, , strWhere

DoCmd.Close acForm, Me.Name
End Sub

 

[Pieter Wijnen]

no problemo.
As you have discovered there are several ways to skin a cat.

Pieter

Allen AND Pieter:
I was able to come up with a workable code based on all the information
you
and pieter provided me.

it is a mishmash of both of your codes (and some other codes from sample
microsoft databases) in what i thought was the appropriate places and
supprizingly it seems to work (see the code for the form below). Although
i
don't know if it would continue to work with no problems... if not then
i'll
resort back to test your and pieter suggestions in full....

i would like to take this oppertunity and thank you and pieter for taking
the time. i am rather new to access and any code work i do is rather
intuitive modifications of existing code. I think it's wonderful that both
of
you share the knowledge with novice users like me, so thanks again.

The form has two option groups:
grpState
[] all state
[] Select state [cmbState]

grpDateRange
[] YTD
[] Date Range
Beggining Date [StartDate]
End Date [EndDate]
[] all Dates
here is the code for the criteria form. i would welcome any other
suggestions:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Me!State.Enabled = False
Me!StartDate.Enabled = False
Me!EndDate.Enabled = False

End Sub

Private Sub grpDateRange_AfterUpdate()
' Enable Begging Date and End Date text boxs if user selected Date Range
Option '

Const conDateRange = 2

If Me!grpDateRange.Value = conDateRange Then
Me!StartDate.Enabled = True
Me!EndDate.Enabled = True
Else
Me!StartDate.Enabled = False
Me!EndDate.Enabled = False
End If

End Sub

Private Sub grpState_AfterUpdate()
' Enable State combo box if user selected Select State Option '


Const conSelectState = 2

If Me!grpState.Value = conSelectState Then
Me!State.Enabled = True
Else
Me!State.Enabled = False
End If

End Sub

Private Sub PrintReport_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Dim datDateFrom As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptMACEC"
strField = "CEDate"

'State filter option
Select Case Me.grpState
'All States
Case 1
'no filter
strWhere = "1 = 1"
'State Filter
Case 2
strWhere = "State='" & Me.State & "'"
End Select

'Date range filter
Select Case Me.grpDateRange
'YTD
Case 1
datDateFrom = DateSerial(Year(Date), 1, 1)
strWhere = strWhere & " AND ([CEDate] >= #" & datDateFrom &
"#)"
& _
" AND ([CEDate] < #" & Date + 1 & "#)"

'Date Range
Case 2
If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.EndDate,
conDateFormat)
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.StartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " &
Format(Me.StartDate,
conDateFormat) _
& " And " & Format(Me.EndDate, conDateFormat)
End If
End If
End Select

' Hide me so reports can do their thing
Me.Visible = False
' Set an error trap in case the report NoData event fires and cancels
On Error Resume Next

DoCmd.OpenReport strReport, acViewPreview, , strWhere

DoCmd.Close acForm, Me.Name
End Sub

Just paste the Text into the SQL pane of a Query & Execute
or Create a Table Using the GUI with the one field
it doesn't really matter what type of field as you will never want to
read
it's value

The same applies to statement number 2
ie Paste it into the SQL pane
or open the newly created table and add the value

Pieter





--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4231 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

 

[Pieter Wijnen]

no problemo.
As you have discovered there are several ways to skin a cat.

Pieter

Allen AND Pieter:
I was able to come up with a workable code based on all the information
you
and pieter provided me.

it is a mishmash of both of your codes (and some other codes from sample
microsoft databases) in what i thought was the appropriate places and
supprizingly it seems to work (see the code for the form below). Although
i
don't know if it would continue to work with no problems... if not then
i'll
resort back to test your and pieter suggestions in full....

i would like to take this oppertunity and thank you and pieter for taking
the time. i am rather new to access and any code work i do is rather
intuitive modifications of existing code. I think it's wonderful that both
of
you share the knowledge with novice users like me, so thanks again.

The form has two option groups:
grpState
[] all state
[] Select state [cmbState]

grpDateRange
[] YTD
[] Date Range
Beggining Date [StartDate]
End Date [EndDate]
[] all Dates
here is the code for the criteria form. i would welcome any other
suggestions:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Me!State.Enabled = False
Me!StartDate.Enabled = False
Me!EndDate.Enabled = False

End Sub

Private Sub grpDateRange_AfterUpdate()
' Enable Begging Date and End Date text boxs if user selected Date Range
Option '

Const conDateRange = 2

If Me!grpDateRange.Value = conDateRange Then
Me!StartDate.Enabled = True
Me!EndDate.Enabled = True
Else
Me!StartDate.Enabled = False
Me!EndDate.Enabled = False
End If

End Sub

Private Sub grpState_AfterUpdate()
' Enable State combo box if user selected Select State Option '


Const conSelectState = 2

If Me!grpState.Value = conSelectState Then
Me!State.Enabled = True
Else
Me!State.Enabled = False
End If

End Sub

Private Sub PrintReport_Click()
Dim strReport As String
Dim strField As String
Dim strWhere As String
Dim datDateFrom As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "rptMACEC"
strField = "CEDate"

'State filter option
Select Case Me.grpState
'All States
Case 1
'no filter
strWhere = "1 = 1"
'State Filter
Case 2
strWhere = "State='" & Me.State & "'"
End Select

'Date range filter
Select Case Me.grpDateRange
'YTD
Case 1
datDateFrom = DateSerial(Year(Date), 1, 1)
strWhere = strWhere & " AND ([CEDate] >= #" & datDateFrom &
"#)"
& _
" AND ([CEDate] < #" & Date + 1 & "#)"

'Date Range
Case 2
If IsNull(Me.StartDate) Then
If Not IsNull(Me.EndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.EndDate,
conDateFormat)
End If
Else
If IsNull(Me.EndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.StartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " &
Format(Me.StartDate,
conDateFormat) _
& " And " & Format(Me.EndDate, conDateFormat)
End If
End If
End Select

' Hide me so reports can do their thing
Me.Visible = False
' Set an error trap in case the report NoData event fires and cancels
On Error Resume Next

DoCmd.OpenReport strReport, acViewPreview, , strWhere

DoCmd.Close acForm, Me.Name
End Sub

Just paste the Text into the SQL pane of a Query & Execute
or Create a Table Using the GUI with the one field
it doesn't really matter what type of field as you will never want to
read
it's value

The same applies to statement number 2
ie Paste it into the SQL pane
or open the newly created table and add the value

Pieter





--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4231 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len

--