Modules & VBA :: Creating A Filter By Date Range
Oct 2, 2013
I am trying to use VBA to create a filter by date range. the user inputs 2 dates and the database filters all records by dates in between the 2 dates,
Code below
Dim var_CustDate1 As String
Dim var_CustDate2 As String
var_CustDate1 = InputBox("Please enter start date in format DD/MM/YYYY", "Enter Date", Date)
If Not IsDate(var_CustDate1) Then
MsgBox ("not a valid Date")
[Code] ....
I've tried every combination of format for the dates but this is the closest ive got it to work,
if i enter dates 01/09/2013 and 12/09/2013 the filter works for the days in the month but also displays previous years, but if i change the dates to 01/09/2013 and 13/09/2013 it starts displaying all dates for all years in the months September, October, November and December.
View Replies
ADVERTISEMENT
Jul 26, 2015
So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.
I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.
Code:
Private Sub btnDatumUpis_Click()
Dim SQL As String
SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _
& "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _
& "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _
[Code] ....
View 5 Replies
View Related
May 11, 2007
Hi all, Please help me correct this criteria for a filter in my query.
I want to show all records between todays date and the past 14 days.
I've tried,
Between Date() and "Date - 14"
And it dont work. Thanks for any reply.
View 1 Replies
View Related
Jun 19, 2007
I have a database with several clients who have a series of appointments. I want to be able to create a report of all the clients to be seen during a range of dates to be inputted by the user. All help would be greatly appreciated.
Andrew
View 1 Replies
View Related
May 27, 2015
The data the query pulls is employee name, course ID, course name and course completed date. I have added criteria in the query that asks for the Course ID to filter on a given course and a dynamic field in the query AnnualReqDate: DateAdd("d",+365,[TrainingCourseCompleted]). What this returns is a list of employees that have completed course X the date they completed and the date (12 months) when the course is due. This works great, have created a report that reflects this very well.
The issue comes in when I try to add the ability to filter by date range on the AnnualReqDate dynamic field. the AnnualReqDate dynamic field does not exist until the query is run the Between [Start Date] And [End Date] criteria add to the AnnualReqDate dynamic field wont work.The query fires off but returns zero records.
View 14 Replies
View Related
Jan 3, 2006
I have a query form that allows user to search by two criteria (in which, user can select "And" or "Or" clause for the two criteria). I also allow user to filter the results by date range. On the form, I have two command button, one will prompt the results in a query table, and the other will prompt a report.
Problem 1
I manage to prompt report with a date range (i.e. two unbound text boxes for start date and end date). But, I dont' manage to disable the filter if the date range is null. Below is the code for this report command button. Should I use a toggle button to make a select case?
Private Sub cmdReport_Click()
Dim varItem As Variant
Dim strDocName As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str2MainCateCondition As String
Dim strDate As String
Dim strSQL As String
Dim strFilter As String
' Build criteria string for 1st ComboBox
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2nd Combo Box
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1toggle button condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE NewsClips.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & ";"
' Build criteria string for Date
If Not IsNull(Me![dateTo]) Then
strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#"
'Format(Me.dateTo, "mm/dd/yy")
Else
strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#"
End If
' filter string
strFilter = strDate
' Open report
strDocName = "RptCateDateQry"
DoCmd.OpenReport strDocName, acViewDesign, , strFilter
With Reports(strDocName)
.RecordSource = strSQL
.Filter = strFilter
.FilterOn = True
End With
DoCmd.Save acReport, strDocName
DoCmd.OpenReport strDocName, acViewPreview
Exit_cmdReport_Click:
End Sub
Problem 2
I have no idea how to filter the query results by date range in the query table. Below is the code of the query table button, which do not offer the filter feature. Would really appreciate it if you can give me some advice.
Private Sub cmdOK_Click()
On Error GoTo cmdOK_Click_Err
Dim blnQueryExists As Boolean
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strDate As String
Dim str1MainCate As String
Dim str2MainCate As String
Dim str1MainCateCondition As String
Dim str2MainCateCondition As String
Dim strSQL As String
' Check for the existence of the stored query
blnQueryExists = False
Set cat.ActiveConnection = CurrentProject.Connection
For Each qry In cat.Views
If qry.Name = "QryCateDateForm" Then
blnQueryExists = True
Exit For
End If
Next qry
' Create the query if it does not already exist
If blnQueryExists = False Then
cmd.CommandText = "SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips"
cat.Views.Append "QryCateDateForm", cmd
End If
Application.RefreshDatabaseWindow
' Turn off screen updating
DoCmd.Echo False
' Close the query if it is already open
If SysCmd(acSysCmdGetObjectState, acQuery, "QryCateDateForm") = acObjStateOpen Then
DoCmd.Close acQuery, "QryCateDateForm"
End If
' Build criteria string for Date
If Not IsNull(Me![dateTo]) Then
strDate = strDate & " NewsClips.IssueDate Between #" + Format(Me![datefrom], "mm/dd/yyyy") + "# AND #" & Format(Me![dateTo], "mm/dd/yyyy") & "#"
'Format(Me.textStartDate, "mm/dd/yy")
Else
strDate = strDate & " NewsClips.IssueDate >= #" + Format(Me![datefrom], "mm/dd/yyyy") + "#"
End If
' Build criteria string for 1MainCate
For Each varItem In Me.fstMainCate.ItemsSelected
str1MainCate = str1MainCate & ",'" & Me.fstMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str1MainCate) = 0 Then
str1MainCate = "Like '*'"
Else
str1MainCate = Right(str1MainCate, Len(str1MainCate) - 1)
str1MainCate = "IN(" & str1MainCate & ")"
End If
' Build criteria string for 2MainCate
For Each varItem In Me.SecMainCate.ItemsSelected
str2MainCate = str2MainCate & ",'" & Me.SecMainCate.ItemData(varItem) & "'"
Next varItem
If Len(str2MainCate) = 0 Then
str2MainCate = "Like '*'"
Else
str2MainCate = Right(str2MainCate, Len(str2MainCate) - 1)
str2MainCate = "IN(" & str2MainCate & ")"
End If
' Get 1MainCate condition
If Me.optAnd1MainCate.Value = True Then
str1MainCateCondition = " AND "
Else
str1MainCateCondition = " OR "
End If
' Get 2MainCate condition
If Me.optAnd2MainCate.Value = True Then
str2MainCateCondition = " AND "
Else
str2MainCateCondition = " OR "
End If
' Build SQL statement
strSQL = " SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE NewsClips.[1CategoryMain] " & str1MainCate & _
str2MainCateCondition & "NewsClips.[2CategoryMain] " & str2MainCate & _
str1MainCateCondition & strDate & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("QryCateDateForm").Command
cmd.CommandText = strSQL
Set cat.Views("QryCateDateForm").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "QryCateDateForm"
' If required the dialog can be closed at this point
' DoCmd.Close acForm, Me.Name
' Restore screen updating
cmdOK_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOK_Click_Err:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Procedure: cmdOK_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_Exit
End Sub
Sorry for posting this question again, as I thought it's better to make it a seperate posting, rather than a reply to my early post. Your advice will be greatly appreicated.
View 1 Replies
View Related
Nov 18, 2014
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
View 4 Replies
View Related
Jan 13, 2014
I m trying to make form which filters my records and generates a report..here's where I am
Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Code:
Like "*" & [Forms]![Form1]![Text6] & "*"
the code is in report record source
View 9 Replies
View Related
Jan 22, 2014
I have some code that filters job raised I have 2 text box's txtdatestart and txtenddate after entering. date range between the too text boxs it shows me all job raised with in the period.i have entered what I would like is filter it again by client field using combo box cboclient so if the user enter's client name in cboclient combo box and date range in txtdatestart and txtenddate it will only show jobs raised with in the date range of the client enter in the combo box but if the combo box is empty show.
Code:
Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.
[code]....
View 6 Replies
View Related
Feb 10, 2014
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code:
'Purpose: This module illustrates how to create a search form, _
where the user can enter as many or few criteria as they wish, _
and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
View 2 Replies
View Related
Oct 7, 2013
I have a form which I am using child/master fields to create a filter (see attached pic)
For the date ranges I need these to be
>= Start Date and <= Actual Finish Date
How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.
View 14 Replies
View Related
Sep 16, 2014
I have a subform in datasheet view for tracking calls from clients. The Client Name is a combination of the first and last name. I want to create a text box that allows me to start typing in the client name and it will filter for only those Clients that match. Currently, I have the following code (below) for the textbox, however when I start typing in a client name all the clients disappear from the table. The client name is in a combo box so I don't know if that is making a difference.
Private Sub Text3_Change()
Dim strFilter As String
Me.Refresh
[Code].....
View 3 Replies
View Related
Aug 8, 2013
I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.
This is my code:
Code:
Option Compare Database
Private Sub cmdCal_Click()
Dim sql As String
Dim rs As Recordset
Dim qdef As DAO.QueryDef
Dim hours, waste, produced As Integer
[code]....
But it returns nothing, When i remove the errorHandler, it says that no records were found.
View 2 Replies
View Related
Jun 17, 2015
I have a multiselect listbox and two date fields (StartDate & EndDate) in an Access form.I am trying to add records to the Table through the form on a button click.I select multiple items from the list box and the date range between the start date and end date will be equal to the items selected from listbox.For each item selected from the list box I need to add a separate record with a date.So the first record will have List box item selected1 and the start date.Next record will have item 2 from list box and date as dateadd("d",startdate,1)And final record will have last item selected from the listbox and date as enddate.
View 5 Replies
View Related
Nov 30, 2014
I am trying to create a form that will select records by date range. The form contains 2 textboxes, with pop-up calendars to select dates. I managed to come up with this code, however, it's not working. Whenever I run the code, my database just goes blank, as though it's selecting something that is not in the records.
Code:
Private Sub Command16_Click()
Dim Task As String
Dim startDate As Date
Dim endDate As Date
startDate = Me.Text12
endDate = Me.Text14
Task = "SELECT * FROM Final WHERE Final.Timestamp BETWEEN #" & startDate & "# AND #" & endDate & "#;"
Me.RecordSource = Task
End Sub
How does one actually do the date range search? Could it be because my timestamp actually has both date and time, that's why the date search is not working?
View 5 Replies
View Related
Jul 16, 2015
I have a form that has several drop down menus on it. I use the form to filter the database to only the options that are put into the drop down windows. I have it set up so that every window does not need to be filled in for the filter to work. Now I am trying to figure out how to put the option of a date range on my form as well. The code that I am using to make everything up to the date range work is.
Code:
Private Sub Search_Button_Click()
DoCmd.Close acForm, "Admin_CompletedPartSearch"
str_Form = "Admin_CompletedPartSearch"
str_Filter = "(1=1)"
[Code] ....
I can't figure out what I would need to do to make a date range also fit into this code but not to be required.
View 1 Replies
View Related
Aug 29, 2013
I have recently set up a button that exports multiple queries to one Excel file, using TransferSpreadsheet code. This works great and saves a lot of time, the only thing is on many of the queries I have a date range set, so you have to enter a date range to get the results.
Is there away that once I press the button I enter the date range once and than it exports the rest of the data based on this range, rather than entering the same dates multiple times.
View 1 Replies
View Related
Mar 17, 2014
I want a string of code that allows me to import a series of delimited text files into access using VBA. I have the first bit of code down:
Code:
DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "msfs3109data1shareeveryoneprorep ranhistDelivery" & currentdate & ".txt"
The text files hold data for a specific days work. Each day has its own text file. I would like to be able to import the text files within a specific date range, specified by the user. Below is the total code i have for my form button:
rivate Sub Command0_Click()
Dim startdate As String
Dim enddate As String
Dim currentdatex
Dim count As Integer
count = 0
[Code] .....
View 12 Replies
View Related
Jan 19, 2015
Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb
[code]....
View 4 Replies
View Related
Jul 31, 2014
I work for a company that manufactures home appliances & electronics. When those products fail within the warranty period, we are obligated to repair the units for the customers.
We have around 200 factory technicians who make those repairs. Each tech is able to make approximately 8 repairs each day. We have a dispatching system that assigns the repairs to the techs based on their availability each day. But the problem is that we have to manually enter and adjust the schedule for all 200 techs every single day, and this takes a lot of time. And of course technicians get sick, take vacation, etc, so we have to adjust the schedule so no techs will be assigned calls when they're off.
The problem: For example, currently when a technician takes off for 2 weeks, the user has to enter 14 individual records for the tech, which is somewhat time consuming. What I need is to program a button click event to determine the two dates (startDate & endDate) and append multiple records from one single entry in the form for each date in between and including the two date fields.
I know I'll probably need to create a loop that will loop through the two dates on the form and append a record for each day, so I can then cross reference the dates to the master schedule dates to make sure that no availability is opened for the techs taking time off.
View 3 Replies
View Related
Jul 16, 2014
I am trying to set a date filter that filters between two dates (Start/End) after I have selected filters from other combo boxes. This is what I Have so far and is a bit of a mess.
I have two text boxes - txtStartDate and txtEndDate
If Nz(Me.txtStartDate.Text) = "" Then
Me.Form.Filter = ""
Me.FilterOn = False
ElseIf Me.cboCity.ListIndex <> -1 Then
Me.Form.Filter = Me.Form.Filter = "NextCallDate
[Code] .....
So the idea is I will filter all records by my name then by status then by city and then by start date and end date.
View 2 Replies
View Related
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Jan 10, 2015
I am trying to filter a recordset with a variable q. The field in the table associated with the record set is of data type Date/time. I assigned data types String and Date to q but both cases generates the error message " Data type mismatched in expression".
View 10 Replies
View Related
Mar 2, 2015
I am a bit of a novice when it comes to Access, but have managed to create a form with a subform embedded and various filters to show different data within the subform, including a date range filter. The code I have used for these filters is as follows:
Private Sub Command40_Click()
Dim strCriteria As String
strCriteria = createCriteria("[Introductions].Town", "List78")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Ownership", "List52")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Company", "List54")
[Code] ....
This all works fine, but I'm wondering what I need to add to this code to make it so that if the date boxes are left blank, records from all dates are displayed. At the moment I have to enter dates in order for it to work properly.
View 5 Replies
View Related
Sep 23, 2014
I'm new to programming with Access but am attempting to create a new field in a table with an new date based on existing fields in the table.
The current fields are [Frequency], integer, [Risk], text, [Last Audit Date], date/time, and the new field is [Next Audit Date]. [Frequency] is a calculated field based only on [Risk] and is equal to "5" if [Risk] is "Low" and is "3" is [Risk] is "Medium" or "High", and [Frequency] is blank if [Risk] is (thus far it has never been empty).
What I need the new calculated field to do is return "N/A" (or blank, or anything easily separated really) if [Frequency] is blank, or if [Risk] is "Low" or "Medium". If [Risk] is "High", [Next Audit Date] should be equal to [Last Audit Date] plus 3 years. When I try to save the code, I get this message: "The expression could not be saved because its result type, such as binary or NULL, is not supported by the server."
This is my code now:
IIf(IsNull([Frequency]),"",IIf([Risk]="Low","N/A",IIf([Risk]="Medium","N/A",IIf([Last Audit Date]="N/A","N/A",[Last Audit Date]+Year(3)))))
View 4 Replies
View Related
Aug 7, 2005
Is there a way to show the earliest and latest dates of a report generated by a non-date field?
E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)
View 2 Replies
View Related
