Hi ,
I have 2 seperate reports, one works off a drop down menu where you select a
client and it brings up all the jobs to the selected clients name. The 2nd is
a date report where you enter 2 dates and it brings up all the jobs between
them.
What I want to do is make them now work together, so 1st you select the
client then the dates and it brings up all the jobs for that client between
the dates selected.

The client report uses a "where" command, of client name=clientcombo for
example.

The date is written in code, now is there anywhere I can add a where command
or similar to the date code so that it filters the date results by the client
name on the combo...

The date code is ...

Private Sub OK_Click()
Dim strReport As String     'Name of report to open.
   Dim strField As String      'Name of your date field.
   Dim strWhere As String      'Where condition for OpenReport.
   Const conDateFormat = "\#mm\/dd\/yyyy\#"

   strReport = "DateReport"
   strField = "DateJobReceived"

   If IsNull(Me.txtStartDate) Then
       If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
           strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
       End If
   Else
       If IsNull(Me.txtEndDate) Then       'Start date, but no End.
           strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
       Else                                'Both start and end dates.
           strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
               & " And " & Format(Me.txtEndDate, conDateFormat)
       End If
   End If

    Debug.Print strWhere                  'For debugging purposes only.

   DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Thanks to anyone who helps :o)

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1

building OpenReport Where parameter
---

here is an example that tests criteria and builds a filter string to use 
as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...

'~~~~~~~~~~~~~~~~~~

    'tell Access you are going to create a variable to hold text
    dim mFilter as string

    'initialize the variable
    mFilter = ""

    'substitute YOUR controlname in here after "Me."
    'we are testing to see if it is filled out
    'if it is, we are going to make mFilter hold the criteria
    If not IsNull(me.text_controlname ) Then
       mfilter = "[TextFieldname]= '" & me.text_controlname  & "'"
    end if

    'test the next control
    If not IsNull(me.date_controlname ) Then
        'if we alread have criteria, add AND to specify that and more
       if mfilter  <> "" then mFilter = mFilter & " AND "
       mFilter = mFilter & "[DateFieldname]= #" _
          & me.date_controlname  & "#"
    end if

    'test the next control
    If not IsNull(me.numeric_controlname ) Then
        'if we alread have criteria, add AND to specify that and more
       if mfilter  <> "" then mFilter = mFilter & " AND "
       mfilter = mfilter  &  "[NumericFieldname]= " _
         & me.numeric_controlname
    end if

    '----------------------- listbox
    dim varItem as Variant _
       , mListWhere as string
    mListWhere = ""

    For Each varItem In me.listbox_controlname.ItemsSelected

      'delete the line that doesn't apply

      'for text
      mListWhere = mListWhere & "'" _
        &  me.listbox_controlname.ItemData(varItem) & "', "

      'for numbers
      mListWhere = mListWhere _
        & me.listbox_controlname.ItemData(varItem) & ", "

    Next varItem

    if len(mListWhere) > 0 then
      mListWhere = "[Field_Name] IN (" & mListWhere

     'remove comma and space from the end and add a parenthesis
      mListWhere = left(mListWhere,len(mListWhere)-2)) & ")"

     'if we alread have criteria, add AND to specify that and more
     if mfilter  <> "" then mFilter = mFilter & " AND "

     mfilter = mfilter  &  mListWhere
    end if
    '-----------------------

    DoCmd.OpenReport "ReportName", acViewPreview, , mfilter

'~~~~~~~~~~~~~~~~~~

I have used
TextFieldname to show how text needs to be delimited - with single quote 
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

for listbox criteria, a loop is done through the selected items and the 
criteria is listed and seperated by commas

'~~~~~~~~~~~~~~~~~~
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter 
string already says something)
Then we are going to add the criteria for that filter
the first condition tested, obviously, does not have anything in the 
filter string yet <smile>

make sure that the referenced fields are in the underlying recordset for 
the report.
Since a filter is applied on the recordset, they do not have to be on 
the report object (unlike a form)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~

    If not IsNull(me.date1_controlname ) Then
       mFilter = mFilter & "[DateFieldname]>= #" _
          & me.date1_controlname & "#"
    end if

    If not IsNull(me.date2_controlname ) Then
        'if we alread have criteria, add AND to specify that and more
       if mfilter  <> "" then mFilter = mFilter & " AND "
       mFilter = mFilter & "[DateFieldname] <= #" _
         & me.date2_controlname & "#"
    end if
'~~~~~~~~~~~~~~~~~~

If you are not using the American Date Format, then you will need to do 
something like this:

Format(me.date_controlname, "\#m\/d\/yyyy\#")
instead of
"#" & me.date_controlname & "#"

hopefully, you can adapt this logic to your question -- if not, we can 
help you further

Warm Regards,
Crystal
  *
      (:  have an awesome day  :)
   *
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
   *



evilcowstare via AccessMonster.com wrote:
> Hi ,
> I have 2 seperate reports, one works off a drop down menu where you select a
> client and it brings up all the jobs to the selected clients name. The 2nd is
> a date report where you enter 2 dates and it brings up all the jobs between
> them.
> What I want to do is make them now work together, so 1st you select the
> client then the dates and it brings up all the jobs for that client between
> the dates selected.
> 
> The client report uses a "where" command, of client name=clientcombo for
> example.
> 
> The date is written in code, now is there anywhere I can add a where command
> or similar to the date code so that it filters the date results by the client
> name on the combo...
> 
> The date code is ...
> 
> Private Sub OK_Click()
> Dim strReport As String     'Name of report to open.
>    Dim strField As String      'Name of your date field.
>    Dim strWhere As String      'Where condition for OpenReport.
>    Const conDateFormat = "\#mm\/dd\/yyyy\#"
> 
>    strReport = "DateReport"
>    strField = "DateJobReceived"
> 
>    If IsNull(Me.txtStartDate) Then
>        If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
>            strWhere = strField & " <= " & Format(Me.txtEndDate,
> conDateFormat)
>        End If
>    Else
>        If IsNull(Me.txtEndDate) Then       'Start date, but no End.
>            strWhere = strField & " >= " & Format(Me.txtStartDate,
> conDateFormat)
>        Else                                'Both start and end dates.
>            strWhere = strField & " Between " & Format(Me.txtStartDate,
> conDateFormat) _
>                & " And " & Format(Me.txtEndDate, conDateFormat)
>        End If
>    End If
> 
>     Debug.Print strWhere                  'For debugging purposes only.
> 
>    DoCmd.OpenReport strReport, acViewPreview, , strWhere
> End Sub
> 
> Thanks to anyone who helps :o)
> 

Hi Crystal, thanks very much for replying. To be honest I feel I am totally
over my head on this one, Im a bit of a novice when it comes to code, even
the date code i showed in my thread was written for me so Im really
struggling at the moment to apply all the code you put and know what to do
with it, maybe if I give you all my details you could apply them for me?
Sorry I know its a bit much I really need this function to work I just feel
this is quite a few steps abovre where I am at the moment.

Form where the report is run from is called "clientanddate"
It contains 1 drop-down box for the clients name, this is collected from a
query called "Clients Query"
the drop-down is called "clientdatecombo"
I also have 2 text boxes for the date, one for the from date and one for the
too date.
these are named..
txtStartDate and txtEndDate
next to them is an OK button which at the moment just runs the report based
on the dates entered, it does this with this code....

Private Sub OK_Click()
Dim strReport As String     'Name of report to open.
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"

    strReport = "DateReport"
    strField = "DateJobReceived"

    If IsNull(Me.txtStartDate) Then
        If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
            strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
        End If
    Else
        If IsNull(Me.txtEndDate) Then       'Start date, but no End.
            strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
        Else                                'Both start and end dates.
            strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
                & " And " & Format(Me.txtEndDate, conDateFormat)
        End If
    End If

     Debug.Print strWhere                  'For debugging purposes only.

    DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

What I need it to do is also filter in the date report using the clients name
only from "clientdatecombo" so it brings up all the jobs between the dates
for that client.

Im really sorry if I am being really stupid on this and I know I should just
learn it and I am trying it is just really difficult if you miss a big chunk
out the middle.

Thank You for any help you or anyone else can give, I really REALLY!
appreciate it !!!






strive4peace wrote:
>building OpenReport Where parameter
>---
>
>here is an example that tests criteria and builds a filter string to use 
>as a parameter in OpenReport
>
>assuming you are in the code behind the ReportMenu form...
>
>'~~~~~~~~~~~~~~~~~~
>
>    'tell Access you are going to create a variable to hold text
>    dim mFilter as string
>
>    'initialize the variable
>    mFilter = ""
>
>    'substitute YOUR controlname in here after "Me."
>    'we are testing to see if it is filled out
>    'if it is, we are going to make mFilter hold the criteria
>    If not IsNull(me.text_controlname ) Then
>       mfilter = "[TextFieldname]= '" & me.text_controlname  & "'"
>    end if
>
>    'test the next control
>    If not IsNull(me.date_controlname ) Then
>        'if we alread have criteria, add AND to specify that and more
>       if mfilter  <> "" then mFilter = mFilter & " AND "
>       mFilter = mFilter & "[DateFieldname]= #" _
>          & me.date_controlname  & "#"
>    end if
>
>    'test the next control
>    If not IsNull(me.numeric_controlname ) Then
>        'if we alread have criteria, add AND to specify that and more
>       if mfilter  <> "" then mFilter = mFilter & " AND "
>       mfilter = mfilter  &  "[NumericFieldname]= " _
>         & me.numeric_controlname
>    end if
>
>    '----------------------- listbox
>    dim varItem as Variant _
>       , mListWhere as string
>    mListWhere = ""
>
>    For Each varItem In me.listbox_controlname.ItemsSelected
>
>      'delete the line that doesn't apply
>
>      'for text
>      mListWhere = mListWhere & "'" _
>        &  me.listbox_controlname.ItemData(varItem) & "', "
>
>      'for numbers
>      mListWhere = mListWhere _
>        & me.listbox_controlname.ItemData(varItem) & ", "
>
>    Next varItem
>
>    if len(mListWhere) > 0 then
>      mListWhere = "[Field_Name] IN (" & mListWhere
>
>     'remove comma and space from the end and add a parenthesis
>      mListWhere = left(mListWhere,len(mListWhere)-2)) & ")"
>
>     'if we alread have criteria, add AND to specify that and more
>     if mfilter  <> "" then mFilter = mFilter & " AND "
>
>     mfilter = mfilter  &  mListWhere
>    end if
>    '-----------------------
>
>    DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
>
>'~~~~~~~~~~~~~~~~~~
>
>I have used
>TextFieldname to show how text needs to be delimited - with single quote 
>marks (you can also use double quote marks
>
>DateFieldname to show that dates need to be delimited with #
>
>NumericFieldname to show that numbers are not delimited
>
>for listbox criteria, a loop is done through the selected items and the 
>criteria is listed and seperated by commas
>
>'~~~~~~~~~~~~~~~~~~
>each time, we are testing to see if a filter control is filled out.
>If it is, we are going to see if we first need to add AND (if the filter 
>string already says something)
>Then we are going to add the criteria for that filter
>the first condition tested, obviously, does not have anything in the 
>filter string yet <smile>
>
>make sure that the referenced fields are in the underlying recordset for 
>the report.
>Since a filter is applied on the recordset, they do not have to be on 
>the report object (unlike a form)
>
>For a Date Range, you would do:
>
>'~~~~~~~~~~~~~~~~~~
>
>    If not IsNull(me.date1_controlname ) Then
>       mFilter = mFilter & "[DateFieldname]>= #" _
>          & me.date1_controlname & "#"
>    end if
>
>    If not IsNull(me.date2_controlname ) Then
>        'if we alread have criteria, add AND to specify that and more
>       if mfilter  <> "" then mFilter = mFilter & " AND "
>       mFilter = mFilter & "[DateFieldname] <= #" _
>         & me.date2_controlname & "#"
>    end if
>'~~~~~~~~~~~~~~~~~~
>
>If you are not using the American Date Format, then you will need to do 
>something like this:
>
>Format(me.date_controlname, "\#m\/d\/yyyy\#")
>instead of
>"#" & me.date_controlname & "#"
>
>hopefully, you can adapt this logic to your question -- if not, we can 
>help you further
>
>Warm Regards,
>Crystal
>  *
>      (:  have an awesome day  :)
>   *
>MVP Access
>Remote Programming and Training
>strive4peace2006 at yahoo.com
>   *
>
>> Hi ,
>> I have 2 seperate reports, one works off a drop down menu where you select a
>[quoted text clipped - 45 lines]
>> 
>> Thanks to anyone who helps :o)

-- 
Message posted via http://www.accessmonster.com

Hi (what is your name),

In order to explain things to you, you need to get some basics under 
your belt.  To help you understand Access a bit better, send me an email 
and request my 30-page Word document on Access Basics (for Programming) 
-- it doesn't cover VBA, but prepares you for it because it covers 
essentials in Access.   I do also send out the first 3 chapters of a 
book I am writing on VBA to all who request it.

Be sure to put "Access Basics" in the subject line so that I see your 
message...

whenever someone gives you code, you need to take the time to understand 
each line -- and ask questions if you don't

then, after you read the document, more will make sense -- ask what you 
are still confused about.


Warm Regards,
Crystal
  *
      (:  have an awesome day  :)
   *
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
   *



evilcowstare via AccessMonster.com wrote:
> Hi Crystal, thanks very much for replying. To be honest I feel I am totally
> over my head on this one, Im a bit of a novice when it comes to code, even
> the date code i showed in my thread was written for me so Im really
> struggling at the moment to apply all the code you put and know what to do
> with it, maybe if I give you all my details you could apply them for me?
> Sorry I know its a bit much I really need this function to work I just feel
> this is quite a few steps abovre where I am at the moment.
> 
> Form where the report is run from is called "clientanddate"
> It contains 1 drop-down box for the clients name, this is collected from a
> query called "Clients Query"
> the drop-down is called "clientdatecombo"
> I also have 2 text boxes for the date, one for the from date and one for the
> too date.
> these are named..
> txtStartDate and txtEndDate
> next to them is an OK button which at the moment just runs the report based
> on the dates entered, it does this with this code....
> 
> Private Sub OK_Click()
> Dim strReport As String     'Name of report to open.
>     Dim strField As String      'Name of your date field.
>     Dim strWhere As String      'Where condition for OpenReport.
>     Const conDateFormat = "\#mm\/dd\/yyyy\#"
> 
>     strReport = "DateReport"
>     strField = "DateJobReceived"
> 
>     If IsNull(Me.txtStartDate) Then
>         If Not IsNull(Me.txtEndDate) Then   'End date, but no start.
>             strWhere = strField & " <= " & Format(Me.txtEndDate,
> conDateFormat)
>         End If
>     Else
>         If IsNull(Me.txtEndDate) Then       'Start date, but no End.
>             strWhere = strField & " >= " & Format(Me.txtStartDate,
> conDateFormat)
>         Else                                'Both start and end dates.
>             strWhere = strField & " Between " & Format(Me.txtStartDate,
> conDateFormat) _
>                 & " And " & Format(Me.txtEndDate, conDateFormat)
>         End If
>     End If
> 
>      Debug.Print strWhere                  'For debugging purposes only.
> 
>     DoCmd.OpenReport strReport, acViewPreview, , strWhere
> End Sub
> 
> What I need it to do is also filter in the date report using the clients name
> only from "clientdatecombo" so it brings up all the jobs between the dates
> for that client.
> 
> Im really sorry if I am being really stupid on this and I know I should just
> learn it and I am trying it is just really difficult if you miss a big chunk
> out the middle.
> 
> Thank You for any help you or anyone else can give, I really REALLY!
> appreciate it !!!
> 
> 
> 
> 
> 
> 
> strive4peace wrote:
>> building OpenReport Where parameter
>> ---
>>
>> here is an example that tests criteria and builds a filter string to use 
>> as a parameter in OpenReport
>>
>> assuming you are in the code behind the ReportMenu form...
>>
>> '~~~~~~~~~~~~~~~~~~
>>
>>    'tell Access you are going to create a variable to hold text
>>    dim mFilter as string
>>
>>    'initialize the variable
>>    mFilter = ""
>>
>>    'substitute YOUR controlname in here after "Me."
>>    'we are testing to see if it is filled out
>>    'if it is, we are going to make mFilter hold the criteria
>>    If not IsNull(me.text_controlname ) Then
>>       mfilter = "[TextFieldname]= '" & me.text_controlname  & "'"
>>    end if
>>
>>    'test the next control
>>    If not IsNull(me.date_controlname ) Then
>>        'if we alread have criteria, add AND to specify that and more
>>       if mfilter  <> "" then mFilter = mFilter & " AND "
>>       mFilter = mFilter & "[DateFieldname]= #" _
>>          & me.date_controlname  & "#"
>>    end if
>>
>>    'test the next control
>>    If not IsNull(me.numeric_controlname ) Then
>>        'if we alread have criteria, add AND to specify that and more
>>       if mfilter  <> "" then mFilter = mFilter & " AND "
>>       mfilter = mfilter  &  "[NumericFieldname]= " _
>>         & me.numeric_controlname
>>    end if
>>
>>    '----------------------- listbox
>>    dim varItem as Variant _
>>       , mListWhere as string
>>    mListWhere = ""
>>
>>    For Each varItem In me.listbox_controlname.ItemsSelected
>>
>>      'delete the line that doesn't apply
>>
>>      'for text
>>      mListWhere = mListWhere & "'" _
>>        &  me.listbox_controlname.ItemData(varItem) & "', "
>>
>>      'for numbers
>>      mListWhere = mListWhere _
>>        & me.listbox_controlname.ItemData(varItem) & ", "
>>
>>    Next varItem
>>
>>    if len(mListWhere) > 0 then
>>      mListWhere = "[Field_Name] IN (" & mListWhere
>>
>>     'remove comma and space from the end and add a parenthesis
>>      mListWhere = left(mListWhere,len(mListWhere)-2)) & ")"
>>
>>     'if we alread have criteria, add AND to specify that and more
>>     if mfilter  <> "" then mFilter = mFilter & " AND "
>>
>>     mfilter = mfilter  &  mListWhere
>>    end if
>>    '-----------------------
>>
>>    DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
>>
>> '~~~~~~~~~~~~~~~~~~
>>
>> I have used
>> TextFieldname to show how text needs to be delimited - with single quote 
>> marks (you can also use double quote marks
>>
>> DateFieldname to show that dates need to be delimited with #
>>
>> NumericFieldname to show that numbers are not delimited
>>
>> for listbox criteria, a loop is done through the selected items and the 
>> criteria is listed and seperated by commas
>>
>> '~~~~~~~~~~~~~~~~~~
>> each time, we are testing to see if a filter control is filled out.
>> If it is, we are going to see if we first need to add AND (if the filter 
>> string already says something)
>> Then we are going to add the criteria for that filter
>> the first condition tested, obviously, does not have anything in the 
>> filter string yet <smile>
>>
>> make sure that the referenced fields are in the underlying recordset for 
>> the report.
>> Since a filter is applied on the recordset, they do not have to be on 
>> the report object (unlike a form)
>>
>> For a Date Range, you would do:
>>
>> '~~~~~~~~~~~~~~~~~~
>>
>>    If not IsNull(me.date1_controlname ) Then
>>       mFilter = mFilter & "[DateFieldname]>= #" _
>>          & me.date1_controlname & "#"
>>    end if
>>
>>    If not IsNull(me.date2_controlname ) Then
>>        'if we alread have criteria, add AND to specify that and more
>>       if mfilter  <> "" then mFilter = mFilter & " AND "
>>       mFilter = mFilter & "[DateFieldname] <= #" _
>>         & me.date2_controlname & "#"
>>    end if
>> '~~~~~~~~~~~~~~~~~~
>>
>> If you are not using the American Date Format, then you will need to do 
>> something like this:
>>
>> Format(me.date_controlname, "\#m\/d\/yyyy\#")
>> instead of
>> "#" & me.date_controlname & "#"
>>
>> hopefully, you can adapt this logic to your question -- if not, we can 
>> help you further
>>
>> Warm Regards,
>> Crystal
>>  *
>>      (:  have an awesome day  :)
>>   *
>> MVP Access
>> Remote Programming and Training
>> strive4peace2006 at yahoo.com
>>   *
>>
>>> Hi ,
>>> I have 2 seperate reports, one works off a drop down menu where you select a
>> [quoted text clipped - 45 lines]
>>> Thanks to anyone who helps :o)
>