Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access - Include date range in procedure

Posted on 2011-03-03
2
Medium Priority
?
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

688 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