How to filter by date

[ccudlip]

Hello I am making a database that stores info on clients' training sessions and each data point has a date attached to it....I want to add a button to a form that will return the number of entries for a specific date....how do I script this? and if I wanted to total the number of entries based upon a range of dates?

-Chris

 

[nim180]

Hi ccudlip,

Try the following, Create a textbox, listbox and a command button on a form. On the on click event of the button field place the following code, replacing "TEXTBOX NAME and LISTBOX NAME with the name of yor textbox and listbox.

***********************************************************

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'The statement below refers your table you are gathering information for and the required fields, replace "tblSaveLogon" with you table name and the information after "." with all the required fields you need. In my example below i have 4 fields i want to appear in the list box.

strSQL = "SELECT tblSaveLogon.Username, tblSaveLogon.Details, tblSaveLogon.Date, tblSaveLogon.Time FROM tblSaveLogon"

strWhere = "WHERE"

'The statement below is to "ORDER" the results by, in your case im gathering it would be by the date.

strOrder = "ORDER BY tblSaveLogon.Time;"

If Not IsNull(Me.TEXTBOX NAME) Then

'replace "tblSaveLogon.Username" with you TABLENAME.DATE

strWhere = strWhere & " (tblSaveLogon.Username) Like '*" & TEXTBOX NAME & "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Me.LISTBOX NAME.RowSource = strSQL & " " & strWhere & "" & strOrder

**********************************************************

Then go back to your form and go to the listbox control.
Change the COLUMN COUNT, if you need to 4 fields to appear in the listbox then change it to 4, in you case it may be just the date you want to appear so it would be 1.

COLUMN COUNT - 1
COLUMN HEADS - NO (unless you want to show the field header)

This is the width of each field you require, so if its 4 fields it would be something like

COLUMN WIDTH - 1";1";1";1" (you can make the field larger if you wish)

BOUND COLUMN - refers to your "DATE" field, so if you only have the date which appears on the listbox then it would be set to 1. If you have 4 field for example and date is field number 3 then BOUND COLUMN - 3

That should do it.

If you want to count how many fields came up on your search, create another textbox and put this code at the bottom of your onclick event on your button.

Me.TEXTBOX NAME = Me.LISTBOX NAME.ListCount - 1
If (Me.LISTBOX NAME.ListCount = 0) Then
Me.TEXTBOX NAME = 0
End If


Hope that helps,
Nim

 

[nim180]

sorry small error. Use this code if you are using a column header.

Me.TEXTBOX NAME = Me.LISTBOX NAME.ListCount - 1
If (Me.LISTBOX NAME.ListCount = 0) Then
Me.TEXTBOX NAME = 0
End If

If you are not using a column header just get rid of the - 1.

nim