Results 1 to 9 of 9

Thread: Filter by Form

  1. #1
    Join Date
    Nov 2009
    Posts
    29

    Unanswered: Filter by Form

    How do I filter the inital contact date if they want it before Jan 1, 2010?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Select * from MyTable where MyDateField < #" & 01/01/2010 & "#"


    How you use it, depends on if you can give us more information.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2009
    Posts
    29
    They want to use Filter By Form with the frmCompaniesAndProducts form to select all records which the city is Grand Rapids or Kalamazoo and the initial contact date is before Jan 1, 2010.
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Check the code bank. There's some neat examples on reporting criteria by city, date, etc.... Look for the Reporting examples (I'm not going to paste all the links.) I wouldn't recommend the filter by form. Too many problems. (and I think there's a reporting example which does EXACTLY what you want.)
    Last edited by pkstormy; 11-25-09 at 20:17.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2009
    Posts
    29
    So I would use the Query design for it? If so, how would I do it?

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you were only finding all the records based on 1 or 2 specific value in a table, you can go the route of creating a SQL statement (or a simple query) and utilize that as your recordsource for the report. But it kind of sounds like you want the ability to select multiple cities and then return all records matching all those cities.

    Usually what I do when I want to query many 'different' values from the same field (for example, the user wants to enter 10 cities to query off of), is I create a 'temp' type table where I populate the city values into that temp table. I then link this temp table to the main data table by the city field. It get's a little more complicated if there are other fields involved but it can still work - you just need a second temp table if you decide to go this route.

    ** If you're only dealing with a few criteria selections, this may not be the route for you though.

    For example (what I do):
    1. Design a 'Criteria' type form (make sure to delete all the records in your temp table first before re-populating it.)
    2. The criteria form can be designed many different ways but the goal is to allow users to enter the different values for the Cities they want into the temp table. (ie. each record would have 1 city name.)
    3. After the user is done, they then open the report or query.
    4. Within that query/report, is the 'temp' table linked to the main data table by the city field so it only returns matching records for the city.

    Another way is to create a 'long' SQL statement where you keep appending to it (ie. strSQL = StrSQL & " AND City = 'SomeValue'), etc...etc...
    and then repeat the procedure until you get one long strSQL statement. There are limitations to how many characters you can have in a strSQL statement so if you think there's going to be a lot of selected values, you may want to re-consider this approach.

    You can do the same procedure for other fields (ie. another temp type table) and link that table (along with your city temp table) into your final query/reporting recordset (or you could do a combination of both.)

    There are a couple of good examples in the code bank which show this. I believe Nckdryr posted a good one (called: Search and Search2) but look for other Search/reporting examples. I think I have one which does the 'temp' table trick.
    Last edited by pkstormy; 11-27-09 at 02:21.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2009
    Posts
    29
    that didn't really help me

  8. #8
    Join Date
    Nov 2009
    Posts
    29
    I think I got it

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Hopefully you didn't hard-code the criteria right into the SQL. Another option I forgot to mention was to simply create 2 comboboxes on the form for the city and then base your criteria off those.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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