Queries :: Filter Using Date Range But Show All Records If Text Box Null
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
I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?
This is what I have currently in my query
CO_resp_rcvd (date field)
Criteria: Between [Start Date] And [End Date]
(I need null values as well because there will be some if the CO has not responded yet but needs to)
Formula:
This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?
Not sure how to combine it to work - the wrapper is a bus day function
This is what I have so far in the query
CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())
I have what I think is a simple query returning the names of students that have been dismissed since September 2012 using a "WithdrawnDate" field. The query pulls a lot of information from other related tables (about 6 different ones), and has two expressions.
When the criteria is set to either "Is Not Null" or a date range (which is all I need), it does not return the complete set of records based on the data that fits the criteria in the main table?
Could there be some sort of join preventing all records from being returned?
I have a monthly system data report I wish to manipulate in Access.
I have created a table to match the 3 fields in the data report; the date, widget ID and number of widgets made.
Widgets are made every day (including weekends and bank holidays) but there will only be a data entry on days where the number of widgets made changes from the previous day ie if Widget ID 33 was made 5 times on 05 Oct, 06 Oct, 07 Oct but on 8 Oct there were 7 made, the data would look like…
Date.................Widget ID..................Number Made 05 Oct ..................33.............................. .5 08 Oct ..................33.............................. .7
I would like to write a query that shows the data for every day, duplicating the data for the previous day in the instance the same number is made. The output should look like..
Date.................Widget ID..................Number Made 05 Oct ..................33.............................. .5 06 Oct ..................33.............................. .5 07 Oct ..................33.............................. .5 08 Oct ..................33.............................. .7
The data is monthly. There are so many different widgets on the report that it’s pretty much guaranteed there will be at least one entry on the first date and the last date in order for a query to find the full date range – as opposed to a user having to manually enter the date range.
Using the example above where the first entry is on 05 Oct it should not be assumed that 01-04 Oct was zero, the query should then look for last entry in the previous month’s data stored in the database (I realise the first time I do this some form of manual adjustment would have to be made as no previous data would exist).
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.
I have form that user can filter the records and generate a report but I have difficult trying filter null date.
If I have check box called filter null if it has a tick in I would like it only show records that have no value (is null) in field "date start" but if unticked I would like it to only show records with a date in field "date start" ...
I am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.
I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?
Code:
SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses] FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;
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"
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
I am having trouble getting a query or report to show only the most recent data.
We have salesmen that use a handheld data collector scanners to count inventory in stores. The scanner data is imported to a Access table. Each record line is one scanned item. I have a query with totals that counts the records and gives me a total count of each item at the store on that date.
I then need to filter the data to only show the most recent date. Using Max Date I get the most recent date but the count fields are showing totals for all dates. I am also getting the unique item from the earlier date in this query which I do not want.
Here is my data table: Inventory Scans from stores.
Scan Date Item Scanned location
1/1/2014 item123 Store ABC
1/1/2014 item123 Store ABC
......
Here is my Query with Totals that counts the item records:
Scan Date Item Scanned location (Item Scanned) count
1/1/2014 item123 Store ABC 2
1/1/2014 item 456 Store ABC 3
1/1/2014 item 789 Store ABC 1
2/1/2014 item123 Store ABC 2
2/1/2014 item 456 Store ABC 1
This is what I am trying to get - only the most recent date of counted items:
Scan Date Item Scanned location (Item Scanned) count
I have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
I have a form where someone enters a 'Job' to be done which in turn creates a record in a table with all this information
On the form I have a field called 'Repeat Frequency' where the user can choose how often they would like this 'Job' to repeat. e.g. Monthly
Then, on another form someone else can check what 'Jobs' should be done on that day
I have managed to get this to work fine using the DatePart function and it displays all jobs to be done that fall on the current day (e.g. if they choose to repeat it weekly, it will look for all entries where the datePart "d" [DateToBeDone] = Date()-7, -14, -21 and -28)
The problem is that i have been limited to the options of 'repeatability'
What i would like to do is change the options from 'Weekly', 'monthly' etc and instead have a field makrked "Repeat every so many days"
Is there a criteria i can use in a query where i can show records every so many days? i.e. if there is a job in the system that should repeat every 7 days and the [DateToBeDone] is set to 31/10/13, it would return this record on 07/11/13, 14/11/13, 21/11/13 and so on?
I Have tried DatePart ("d", [DateToBeDone]=DatePart ("d", (Date()-[RepeatDays] but what happens is it only shows it if the day is that many number of days in the past and not multiples as i said above.
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,
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.
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.
When using the Format:'yyyy mm dd' in access queries. When trying to show a week is there a way to show the date range instead of week number.
Week 01 = Jan1-7. If the results are only week numbers that makes it hard for people to understand when it actually is. Bottom line is: I would like to convert Week number to actual date range. Can anyone help with this?
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.
I have a report, based on a query, where the field name is "Date of Work". The criteria in this query field is >=[Start Date] And <=[End Date], which works fine. However I need to have the start and end dates to show in the resulting report header based on the query.
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, " _
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.
Both have the same source list but I would like the ref 1 list to exclude ref 2 and the ref 2 list to exclude ref 1 but I am struggling when either value is Null gives me no list.
Ref 1 row source
SELECT [Reference Probes].Ref, IIf([Forms]![frmCalibration]![Ref 2]=Null,([Reference Probes].[Ref]) Is Not Null,[Reference Probes].[Ref]) AS Expr1 FROM [Reference Probes] WHERE (((IIf([Forms]![frmCalibration]![Ref 2]=Null,([Reference Probes].[Ref]) Is Not Null,[Reference Probes].[Ref]))<>[Forms]![frmCalibration]![Ref 2]));
Ref 2 row source
SELECT [Reference Probes].Ref FROM [Reference Probes] WHERE ((IIf([Forms]![frmCalibration]![Ref 1]=Null,([Reference Probes].Ref) Is Not Null,[Reference Probes].[Ref])<>[Forms]![frmCalibration]![Ref 1]));
Ref 1 after update has Me.Ref_2.Requery Ref 2 after update has Me.Ref_1.Requery
As you can see I have tried two different ways but both don't work.
After hours of playing around i finally have my IIF statement working the proplem is now is that when the Value is null it gives a blank value on my Report. How do i get it to show the value of 0 when it is Null. can i use an isNull withen my IIf statement maybe nested what is best practice?
I want a very basic report that shows:* all field reps (sales people),
* their quota in terms of new customer registrations
* their actual new customer registrations for a particular month
The report should include field reps with 0 sales. The query I've created works fine as long as there's no date filter. But adding <1/1/2013 removed all field reps with 0 sales. I want to make sure the filter concept was working before getting into date ranges.
Code: SELECT DISTINCTROW tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota, Count(tbl_customers.dateRegistration) AS NewRegistrations FROM tbl_ksFieldRep LEFT JOIN tbl_customers ON tbl_ksFieldRep.[salesRepName] = tbl_customers.[ksFieldRep] WHERE (((tbl_customers.dateRegistration)<#1/1/2013#)) GROUP BY tbl_ksFieldRep.salesRepName, tbl_ksFieldRep.quota;
