Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

angiew (TechnicalUser) (OP)
28 Jul 00 14:49
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!

Angie
cmmrfrds (Programmer)
28 Jul 00 19:54
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.

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!

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