Solved

Access - Include date range in procedure

Posted on 2011-03-03
2
317 Views
Last Modified: 2012-05-11
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
Comment
Question by:JohnMac328
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35026762
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
 

Author Closing Comment

by:JohnMac328
ID: 35027008
That's what I needed - Thanks
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question