Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Filtering between dates

Filtering between dates

Filtering between dates

I have a database that contains all of my company's sales information for the last two years. I am making a form that will be used by managers to filter the information and produce a corresponding report. The managers will filter the information by choosing criteria in combo boxes. The form is linked to a query that combines the tables containing the sales information. I need the managers to also be able to specify which beginning and ending dates for sales in the filter criteria. For example, one may want all sales by a certain person from May of 1999 to August of 1999. I would like them to type this date criteria into a box. I have tried to accomplish this by making unbound text boxes on the form and writing an SQL statement for the Sales Date field of the query, but this does not work as the query is run when the form is opened, resulting in a blank form due to the date boxes on the form having no entries. Does anyone have any ideas? Thanks!

Replies continue below

Recommended for you

RE: Filtering between dates

One way to do it is to have a form where the users enter the criteria. Then pass the criteria to the form that does the query. The form needs to allow filtering but the initial  query for the form does not need any criteria filled in.

Use this syntax in the "criteria selection form" behind a button click event.

    Dim WhereClause as String, DocName as String, QUOTE as String
    QUOTE = Chr$(34)
    DocName = "Your Form"
    WhereClause = " aNameinYouTable = " & QUOTE & aVariable & QUOTE
   '''-- Debug.Print "WhereClause = "; WhereClause
    DoCmd.OpenForm DocName, , , WhereClause

This will supply the filter to your form, but dates can be tricky. They need to be surrounded by #. You can put as many join conditions as you want in the WhereClasue. This example puts double quotes around the variable name which is text. Of course, a number would not need the quotes but dates probably need #.

When you run your form, you can check the filter criteria by looking in the Data properties of the form under Filter.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close