[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

Access - Include date range in procedure

I have an Access form that allows selection of fields from a list box.  After the fields are selected, a button is clicked and it displays the fields.  What I need is for the date field to filter by date, as in the "Between [Begin Date] and [End Date]" even if they don't select the date field.  I have included a screen shot and the procedure code.  

Any help is appreciated. Example
Private Sub cmdRunQuery_Click()
   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As Object
   Dim SQL As String
   Dim vItem As Variant
   
   ' loop through selected field names
   For Each vItem In Me.lstFieldList.ItemsSelected
      SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
   Next vItem
   
   ' build new SQL statement
   SQL = "Select " & Mid(SQL, 2) & " from [Employees]"
   
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("qryEmployees")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' run query
   DoCmd.OpenQuery "qryEmployees"

End Sub

Open in new window

0
JohnMac328
Asked:
JohnMac328
1 Solution
 
Rey Obrero (Capricorn1)Commented:
add a where clause to your Sql statement

   ' build new SQL statement
   SQL = "Select " & Mid(SQL, 2) & " from [Employees]"
   SQL=sql & " Where [HireDate] Between [Begin Date] and [End Date]"

   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("qryEmployees")
   qDef.SQL = SQL
0
 
JohnMac328Author Commented:
That's what I needed - Thanks
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now