filtering forms 2

[brh01]

how would i filter a form by date? Right now I use dlookup w/ a combobox to search date field, but this only grabs the first of what may be many records with this date? I have very limited experience with programming and would appreciate any help

Brian

 

[willir]

Brian

Create an unbound combo box. The wizard will guide you through the most of the process. Choose...
1st screen) Find a record on my form....
2nd screen) Select your date field from the list of fields
3rd screen) Contents should be displayed correctly
4th screen) Give the combo box a suitable label

Usually what happens with the combo box, is that the wizard will actually retrieve two columns - the field you selected in step 2, and the primary key.

Regardless, you will need to tweak the combo box setting.

I am assuming you have the form open in design mode. If the "Properties" window is not open it, menu item View -> Properties.

Select your combo box.

First things first. I find it more preferable to assign a meaningful name to the combo box image for easier referencing later on. Select the "Other" tab on the properties window. Under the Name field, you see "Comboxx" - change this to "cboDateQry", or "DateQrycbo", or something similar and meaningful.

Now select the "Data" tab in the properties window. Select the field for RowSource. When selected, you will see a an icon with "..." appear to the right of the field. Click on the icon to open up the query builder. You only want to have the date field visible. Delete the field for the primary key if present. You also want to sort this field, so select the "Sort" field and select ascending or descending. (Dscending usually makes most sense - most recent at the top)

Now get to the SQL view by selecting menu item "View" -> "SQL view". Add the word DISTINCT after the SELECT keyword. You should see something like the following...

SELECT [b]DISTINCT[/b] YourTable.YourDateField
FROM YourTable
ORDER BY YourTable.YourDateField DESC;

DISTINCT will tell Access to find only one unique record, specifically one unique date.

Close the query builder window and save when prompted.

Back in the Data tab for the properties window, make sure the Bound Column is 1.

Now select the "Format" tab for the properties window. ColumnCount field should be corrected to depict 1, ColumnWidth 0.7" or 1" and the ListWidth should match the ColumnWidth.

...Almost there...

Now select the "Event" tab in the property window. Select the field "AfterUpdate", and select [Event Procedure] from the drop down box. Select the "..." icon to the right of the field. This will take you to the Microsoft Visual Basic coding window. (Surprize - and don't panic)

You will see two lines - "Private Sub ..." and "End Sub". Between these two lines, type something similar to the following...

Private Sub [COLOR=blue]cboFindDateQry[/color]_AfterUpdate()
    ' Find the record that matches the control.
    
    If [b]IsDate(Me.cboFindDateQry)[/b] Then
        [COLOR=red]Me.Filter = "[YourDateField] = " & "#" & Me.cboFindDateQry & "#"[/color]
        Me.FilterOn = True

    End If
    
End Sub

IsDate(Me.cboFindDateQry) makes sure a date is returned before executing the code. Me.cboFindDateQry will be the value in your combo box - Me is your current form, cboFindDateQry will be the name of your combo box.

Me.Filter = "[YourDateField] = " & "#" & Me.cboFindDateQry & "#"
Applies the filter for your date field to match the selected date. Note that dates have to be encapsulated within "#" signs.

Me.FilterOn = True applies / False removes the filter.

Save your work.

Toggle back to your form and switch to "Form" view and test. (Yep, you can keep the coding window open while testing your code. An advantage of an interpretative language.)

The aforementioned should work just fine.

Richard