Solved

Access - Include date range in procedure

Posted on 2011-03-03
2
308 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 119

Accepted Solution

by:
Rey Obrero 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now