Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2005
    Posts
    79

    Red face Unanswered: Date Range Query Report

    I have really been fighting with this. I have a form titled frmDateRange. This form has two text boxes. One text box is titled txtStart, and the other text box is titled txtEnd. I would like for a user to enter start and end dates in the frmDateRange form. Then I would like for the dates to query database called "Computer Information" and then automatically show the report with the new information in the "Report Date Range" Report.

  2. #2
    Join Date
    Nov 2005
    Posts
    2
    Do you have the form tied to a query? You'll have to create a query with inputs tied to the textboxes, so that the form inputs to that query. Then you can put a button on the form to run the query.

    Is that what you're looking for?

  3. #3
    Join Date
    Nov 2005
    Posts
    79

    Date Range Query

    Yes, the record source is tied to a query with the following criteria SQL statement.
    SELECT ComputerInformation.*
    FROM ComputerInformation
    WHERE (((ComputerInformation.PurchaseDate)>=forms![Report Date Range]!txtStart And (ComputerInformation.PurchaseDate)<=forms![Report Date Range]!txtEnd));

    What should the coding be on the button?

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    It's not generally a good idea to reference form fields in a query - it's labor intensive and prone to errors. But, as ewomack suggests, use a form.

    The query selects the specific fields you want to group (in this case by date range). You have 2 text boxes (use an input mask to ensure proper date format) and then a "go" button.

    The code for the go button will set a form filter using the between operator and the two dates on the form.

    When writing that filter, be careful of a common pitfall:
    The data type in the text boxes will be Date/Time. The Rowsource property is looking for a Text String. But, the Database itself will be looking for a Date/Time value to query against. So, you need to convert the value in the text boxes to a string, then assemble the string for the Rowsource property, but tell Access that the values are to be treated as dates. To do this, use the Format command and a # on either side of the date.
    Code:
    strSQL = "[DateField] Between #" & Format([txtDate1]) & "# And #" & Format([txtDate2]) & "#"
    Me.Filter = strSQL
    Me.FilterOn = True
    Warning: this is air code - use with the knowledge that you may need to debug it!

    Good luck
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Nov 2005
    Posts
    79

    Working

    I finally got it working with 2 text boxes from a previous message dated Creating a Criteria Form on 02/02/05, 10:53. However, with one problem. When I run the report, the values reflect all records in a database even though it is suppose to be running the query that prompts the user to enter start and end date.

  6. #6
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Here's something I put together that may give you some ideas about making time periods and other criteria dynamic.

    The free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

    And here is how the query by form concept can work.

    On the invoices dialog there are the following controls:
    InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
    InvDateMax with DefaultValue of =Date()
    InvRepNum with DefaultValue of *
    InvCustNum with DefaultValue of *

    Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
    DoCmd.OpenForm "frmInv"

    And of course there could be a button to open a report the same way:
    DoCmd.OpenReport "rptInv", acViewPreview

    The invoices form (frmInv) has RecordSource property of qryInv.

    And the qryInv query's criteria for the InvDate field has:
    Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

    And the qryInv query's criteria for the RepNum field has:
    Like [Forms]![frmInvDialog]![InvRepNum]

    And the qryInv query's criteria for the CustNum field has:
    Like [Forms]![frmInvDialog]![CustNum]

    One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown.

    For example (based on what is entered into a last name search field):

    Like 'Smith' would show Smith records

    Like '' would show no records (probably not what one would want)

    Like '*' would show all records

    And to counter that I like to have the search fields have a DefaultValue of * and not allow the search fields to be blank.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi miracleblake and "Welcome to the Forum"

    Let me know if you got your entire situation taken care of. If not post again and I will upload my version of what you are trying to do. I have a form that uses two fields to input the dates desired, then it runs the query and opens the report to view the records within the date range selected. Also, it shows the date range at the top of the report. I'll check back later tonight.

    enjoy the Forum and have a nice one,
    BUD

  8. #8
    Join Date
    Nov 2005
    Posts
    79

    Filter

    I notice that when running the filter, records are filtered, however, when the report comes up, it comes up with all records.

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    The report and the form are two seperate objects - hence a filter on one doesn't mean the other is filtered.

    Assuming that the form is open and the query has values in the sort fields, and the report is based on the same query, it should work.

    Is your report based on the same query?

    Have you also considered printing the form, since it is showing you what you want? You can set headers that shown on the screen but do not print and vice versa.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  10. #10
    Join Date
    Nov 2005
    Posts
    79
    The form contains two txt boxes. Once the button is selected, records are filtered. When the Report comes up, it has all records. How do I make the report list information based off of the date criteria from my form? Thank you so much for your assistance.


    Quote Originally Posted by tcace
    The report and the form are two seperate objects - hence a filter on one doesn't mean the other is filtered.

    Assuming that the form is open and the query has values in the sort fields, and the report is based on the same query, it should work.

    Is your report based on the same query?

    Have you also considered printing the form, since it is showing you what you want? You can set headers that shown on the screen but do not print and vice versa.

  11. #11
    Join Date
    Jun 2005
    Posts
    102
    Quote Originally Posted by Bullschmidt
    Here's something I put together that may give you some ideas about making time periods and other criteria dynamic.

    The free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

    And here is how the query by form concept can work.

    On the invoices dialog there are the following controls:
    InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
    InvDateMax with DefaultValue of =Date()
    InvRepNum with DefaultValue of *
    InvCustNum with DefaultValue of *

    Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
    DoCmd.OpenForm "frmInv"

    And of course there could be a button to open a report the same way:
    DoCmd.OpenReport "rptInv", acViewPreview

    The invoices form (frmInv) has RecordSource property of qryInv.

    And the qryInv query's criteria for the InvDate field has:
    Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

    And the qryInv query's criteria for the RepNum field has:
    Like [Forms]![frmInvDialog]![InvRepNum]

    And the qryInv query's criteria for the CustNum field has:
    Like [Forms]![frmInvDialog]![CustNum]

    One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown.

    For example (based on what is entered into a last name search field):

    Like 'Smith' would show Smith records

    Like '' would show no records (probably not what one would want)

    Like '*' would show all records

    And to counter that I like to have the search fields have a DefaultValue of * and not allow the search fields to be blank.

    Hi Bulls, the sample database you provided in your link, contains to many compilation errors.

  12. #12
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by miracleblake
    The form contains two txt boxes. Once the button is selected, records are filtered. When the Report comes up, it has all records. How do I make the report list information based off of the date criteria from my form? Thank you so much for your assistance.
    Hi miracleblake,

    Did you in fact base your Form and Report on the same Query? If so then setting that same Query as the RecordSource for the Report will yield the same results as in the form. So make sure you base your filter on a Query. Then set both the Form and Report on that Query and all should work well.

    BUD

  13. #13
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi once more miracleblake,

    Look at the attached sample db. I stripped it from my big program. It does all of what you said you want it to, so go behind and see how it's all done. Trust me, from me it is very basic simple. Hope it works for you. You know you'll have to modify it so suit your needs.

    have a nice one,
    BUD
    Attached Files Attached Files

  14. #14
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Hi Bulls, the sample database you provided in your link, contains to many compilation errors.
    Rod, Thanks a lot for the feedback. Now I've stripped down the references in my sample Access database at www.bullschmidt.com/access as it no longer has the Word mail merge incorporated into it...
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  15. #15
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    hey miracleblake,

    haven't heard and just wondered how you came out on your situation??

    BUD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •