Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Option group in a form as criteria for report

Discussion in 'Information Technology' started by Sol, Jul 28, 2006.

  1. Sol

    Sol
    Expand Collapse
    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.
     
  2. Loading...

    Similar Threads Forum Date
    Adoption: Why is it Taboo? Hard Talk Jun 21, 2013
    USA Florida Court Voids Adoption Of Millionaire’s Girlfriend As Sham Breaking News Mar 29, 2013
    Opinion Dog loving babas of Juna Akhada advocate adoption of street dogs Breaking News Jan 21, 2013
    Controversial Only option for fading churches is to again take up evangelization Hard Talk Dec 26, 2012
    Introducing New Functionality: Dislikes Option Announcements Apr 12, 2012

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Sol" <Sol@discussions.microsoft.com> wrote in message
    news:B178A8AE-69B1-470E-B9BF-E6A34C5A80F4@microsoft.com...
    > 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.
    >
     
  4. Sol

    Sol
    Expand Collapse
    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.



    "Allen Browne" wrote:

    > 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
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Sol" <Sol@discussions.microsoft.com> wrote in message
    > news:B178A8AE-69B1-470E-B9BF-E6A34C5A80F4@microsoft.com...
    > > 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.
    > >

    >
    >
    >
     
  5. Sol

    Sol
    Expand Collapse
    Guest

    "Sol" wrote:

    > 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.
    >
     
  6. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Sol" <Sol@discussions.microsoft.com> wrote in message
    news:024F91B7-3698-4DFB-A2F9-E68970DA210C@microsoft.com...
    >
    >
    > "Sol" wrote:
    >
    >> 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.
    >>
     
  7. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Sol" <Sol@discussions.microsoft.com> wrote in message
    news:024F91B7-3698-4DFB-A2F9-E68970DA210C@microsoft.com...
    >
    >
    > "Sol" wrote:
    >
    >> 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.
    >>




    --
    ----------------------------------------
    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
     
  8. Sol

    Sol
    Expand Collapse
    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" wrote:

    > 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
    >
    > "Sol" <Sol@discussions.microsoft.com> wrote in message
    > news:024F91B7-3698-4DFB-A2F9-E68970DA210C@microsoft.com...
    > >
    > >
    > > "Sol" wrote:
    > >
    > >> 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.
    > >>

    >
    >
    >
    > --
    > ----------------------------------------
    > 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
    >
    >
    >
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Sol" <Sol@discussions.microsoft.com> wrote in message
    news:86B141E7-C463-4EF7-8DF5-ABAABCFF0CF8@microsoft.com...
    > 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" wrote:
    >
    >> 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
    >>
    >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    >> news:024F91B7-3698-4DFB-A2F9-E68970DA210C@microsoft.com...
    >> >
    >> >
    >> > "Sol" wrote:
    >> >
    >> >> 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.
    >> >>

    >>
    >>
    >>
    >> --
    >> ----------------------------------------
    >> 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
    >>
    >>
    >>
     
  10. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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

    "Sol" <Sol@discussions.microsoft.com> wrote in message
    news:86B141E7-C463-4EF7-8DF5-ABAABCFF0CF8@microsoft.com...
    > 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" wrote:
    >
    >> 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
    >>
    >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    >> news:024F91B7-3698-4DFB-A2F9-E68970DA210C@microsoft.com...
    >> >
    >> >
    >> > "Sol" wrote:
    >> >
    >> >> 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.
    >> >>

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




    --
    ----------------------------------------
    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
     
  11. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Sol" <Sol@discussions.microsoft.com> wrote in message
    news:7AEDBB73-819A-4155-9871-3C2A608123F4@microsoft.com...
    > 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.
    >
    >
    >
    > "Allen Browne" wrote:
    >
    >> 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
    >>
    >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    >> news:B178A8AE-69B1-470E-B9BF-E6A34C5A80F4@microsoft.com...
    >> > 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.
     
  12. Sol

    Sol
    Expand Collapse
    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



    "Allen Browne" wrote:

    > 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.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Sol" <Sol@discussions.microsoft.com> wrote in message
    > news:7AEDBB73-819A-4155-9871-3C2A608123F4@microsoft.com...
    > > 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.
    > >
    > >
    > >
    > > "Allen Browne" wrote:
    > >
    > >> 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
    > >>
    > >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    > >> news:B178A8AE-69B1-470E-B9BF-E6A34C5A80F4@microsoft.com...
    > >> > 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.

    >
    >
    >
     
  13. Sol

    Sol
    Expand Collapse
    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" wrote:

    > 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
    >
    > "Sol" <Sol@discussions.microsoft.com> wrote in message
    > news:86B141E7-C463-4EF7-8DF5-ABAABCFF0CF8@microsoft.com...
    > > 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" wrote:
    > >
    > >> 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
    > >>
    > >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    > >> news:024F91B7-3698-4DFB-A2F9-E68970DA210C@microsoft.com...
    > >> >
    > >> >
    > >> > "Sol" wrote:
    > >> >
    > >> >> 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.
    > >> >>
    > >>
    > >>
    > >>
    > >> --
    > >> ----------------------------------------
    > >> 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
    > >>
    > >>
    > >>

    >
    >
    >
    > --
    > ----------------------------------------
    > 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
    >
    >
    >
     
  14. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter

    "Sol" <Sol@discussions.microsoft.com> wrote in message
    news:6A0CE0DF-B669-40A0-9CAF-A31B11248613@microsoft.com...
    > 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" wrote:
    >
    >> 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
    >>
    >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    >> news:86B141E7-C463-4EF7-8DF5-ABAABCFF0CF8@microsoft.com...
    >> > 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" wrote:
    >> >
    >> >> 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
    >> >>
    >> >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    >> >> news:024F91B7-3698-4DFB-A2F9-E68970DA210C@microsoft.com...
    >> >> >
    >> >> >
    >> >> > "Sol" wrote:
    >> >> >
    >> >> >> 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.
    >> >> >>
    >> >>
    >> >>
    >> >>
    >> >> --
    >> >> ----------------------------------------
    >> >> 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
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
    >> --
    >> ----------------------------------------
    >> 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
    >>
    >>
    >>
     
  15. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

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

    Pieter

    "Sol" <Sol@discussions.microsoft.com> wrote in message
    news:6A0CE0DF-B669-40A0-9CAF-A31B11248613@microsoft.com...
    > 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" wrote:
    >
    >> 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
    >>
    >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    >> news:86B141E7-C463-4EF7-8DF5-ABAABCFF0CF8@microsoft.com...
    >> > 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" wrote:
    >> >
    >> >> 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
    >> >>
    >> >> "Sol" <Sol@discussions.microsoft.com> wrote in message
    >> >> news:024F91B7-3698-4DFB-A2F9-E68970DA210C@microsoft.com...
    >> >> >
    >> >> >
    >> >> > "Sol" wrote:
    >> >> >
    >> >> >> 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.
    >> >> >>
    >> >>
    >> >>
    >> >>
    >> >> --
    >> >> ----------------------------------------
    >> >> 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
    >> >>
    >> >>
    >> >>

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




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

Share This Page