Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2006
    Posts
    11

    Question Unanswered: Pre-sort the database by Year.

    My search form (frmSearch) includes a list box (lbxSummary) based on a query (qrySearch). Users enter search criteria into a textbox (txtInput) that populates a hidden textbox (txtSearchString). Each keystroke updates the query results. The query’s Criteria has the following code:

    Like "*" & [Forms]![frmSearch]![txtSearchString] & "*"

    I want to add a “Choose a Year” dropdown to the form that filters the records by year prior to the above search. I have tried every combination I can think of with no success. All suggestions are welcome.

    Additional Info:

    The On Change event procedure for txtInput is:

    Private Sub txtInput_Change()
    Dim vSearchString As String
    vSearchString = txtInput.Text

    txtSearchString.Value = vSearchString
    Me![lbxSummary].Requery
    End Sub

    The On Change event procedure for txtSearchString is:

    Private Sub txtSearchString_Change()
    Dim vSearchString As String
    vSearchString = txtInput.Text

    txtSearchString.Value = vSearchString
    Me![lbxSummary].Requery
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Add a field to your query that contains the year, so you can then filter and sort by it. Format(fldDate,"YYYY") will work nicely for you.

    have fun,
    tc

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Since you want to sort by year before performing the rest of your search, place the year field first in your grid; sorts occur left to right.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Since you want to sort by year before performing the rest of your search, place the year field first in your grid; sorts occur left to right.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Sorry about that! Trigger finger acting up!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Apr 2006
    Posts
    11
    Thank you for your help. However, unless I'm missed something, these solutions don't enable the "Choose a Year" dropdown. I need to isolate records of a particular year before performing the rest of the search.

    --Kavan

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    For the drop down, you'll have to place a combo box on the form, populate it with year values, and code some filter operations to take place in the box's after update event.

    Missinglinq's idea will sort the data in the query by year first, then what ever else you put in, and my idea will give you a year field to sort and filter by.

    tc

  8. #8
    Join Date
    Apr 2006
    Posts
    11

    Still not quite there.

    Thank you for your help. I placed the combo box as you described.

    It seems to me that the missing code you refer to must first run a query by year, and the result could then be filtered by additional search text. Isn't there a way to have both the text box and the combo box update the query simultaneously? Or more specifically (since each keystroke updates the query), both adding to the query?

  9. #9
    Join Date
    Apr 2006
    Posts
    11
    Any other suggestions on how to finish this off?

Posting Permissions

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