Date Range with Wild Card Query

D

DLAFKO

New member
Local time
Yesterday, 16:12
Joined
Apr 28, 2014
Messages
7
Hello,
I can't figure this out. I have a query with a date. I want users to select the start date and if they want to see any thing from the start date forward just hit enter at the end date box.
If they want to enter an end date they can do that as well.

Also is there a way to query just the year as well.

Thanks
 
Code: 
...
WHERE (YourDateField >= [txtDateStart] OR [txtDateStart] IS NULL)
  AND (YourDateField < ([txtDateEnd] + 1) OR [txtDateEnd] IS NULL)
 
cheekybuddha said:
Code: 
...
WHERE (YourDateField >= [txtDateStart] OR [txtDateStart] IS NULL)
  AND (YourDateField < ([txtDateEnd] + 1) OR [txtDateEnd] IS NULL)
Click to expand...
OK thank you so i put this as the onclick command for the button and it did not work sure i'm missing

Private Sub cmdOpenRPT_Click()
DoCmd.OpenReport rptAllDiscipline, acViewNormal

WHERE (DisciplineDate >= [txtDateStart] Or [txtDateStart] Is Null)
AND (DisciplineDate < [txtDateEnd] + 1) OR [txtDateEnd] IS NULL)
End Sub
 
Last edited:
DLAFKO said:
OK thank you so this is VBA Then right in VBA where does this get put.. On the button to access the report?
Click to expand...
No, this is just SQL.

I have no idea how your form(?) is set up.

Are you using it to open a report?
 
cheekybuddha said:
Yes, but you need to share how you are implementing your search
Click to expand...
Search starts with a button that opens the report. A query is tied to the report so when the report access the query it runs the SQL of the qurey to get the data for the report. The date is 01/01/2023 (Layout) so if there a search just by 2023 in the same query or would i have to make another report and another qurey?
 
The year is just part of the date Like 01/01/2023 Can we just do a search on 2023?
Click to expand...
NO. Dates are not stored as strings. They are stored as double precision numbers with the integer portion representing the number of days since 12/30/1899 and the decimal being the part of a day. ALWAYS use the date functions when working with dates to avoid strange results.

If you want to select just the year part? Use the Year() function to pull the Year out of a date field.

Where Year(SomeDate) = Forms!yourform!yourYearField

or

Where Year(SomeDate) = Year(Date()) -- if you want to use the current year as criteria.
 
Since you have a WHERE option as part of the OpenReport method, there is no reason to create a separate report or separate query. Just use the Where option to change the criteria.

The one thing to avoid at all costs, is prompts. Alway make your criteria reference a function or a form control.
 
Pat Hartman said:
Since you have a WHERE option as part of the OpenReport method, there is no reason to create a separate report or separate query. Just use the Where option to change the criteria.

The one thing to avoid at all costs, is prompts. Alway make your criteria reference a function or a form control.
Click to expand...
Not sure what all that means

Here is the SQL I currently have but if i leave end date blank it returns nothing
Code: 
SELECT DISTINCT tblDisciplineIssues.DisciplineID, tblDOHLocations.BureauName, tblDOHLocations.OrgCodes, tblDisciplineYears.DisciplineYearID, [Firstname] & " " & [Lastname] AS Employee, tblDisciplineIssues.EmployeeNumber, tblDisciplineIssues.DisciplineDate, tblDisciplineTypes.DisciplineType, tblDisciplineIssues.Discipline, tblLevel.Level, tblUnionList.UnionName, tblBargainingUnits.Unit, tblBargainingUnits.UnitName, tblJobCodes.JobCode, tblJobCodes.JobTitle
FROM tblRace RIGHT JOIN (tblLevel RIGHT JOIN (tblGenders RIGHT JOIN (tblDOHLocations RIGHT JOIN (tblDisciplineYears RIGHT JOIN ((tblGrievants RIGHT JOIN (tblJobCodes RIGHT JOIN ((((tblDisciplineIssues LEFT JOIN tblDisciplineTypes ON tblDisciplineIssues.DisciplineTypeID = tblDisciplineTypes.DisciplineTypeID) LEFT JOIN tblUnionList ON tblDisciplineIssues.UnionID = tblUnionList.UnionID) LEFT JOIN tblUnionArticles ON tblUnionList.UnionID = tblUnionArticles.UnionID) LEFT JOIN tblBargainingUnits ON tblDisciplineIssues.BargainingUnitID = tblBargainingUnits.BargainingUnitID) ON tblJobCodes.JobCodeID = tblDisciplineIssues.ClassCodeID) ON tblGrievants.GrievantID = tblDisciplineIssues.EmployeeName) LEFT JOIN (tblIssueList RIGHT JOIN tblIssues ON tblIssueList.IssueListID = tblIssues.IssueID) ON tblDisciplineIssues.DisciplineID = tblIssues.DisciplineID) ON tblDisciplineYears.YearID = tblDisciplineIssues.YearID) ON tblDOHLocations.DOHLocationID = tblDisciplineIssues.ProgramOfficeID) ON tblGenders.GenderID = tblDisciplineIssues.GenderID) ON tblLevel.LevelID = tblDisciplineIssues.LevelID) ON tblRace.RaceID = tblDisciplineIssues.RaceID
WHERE (((tblDisciplineIssues.DisciplineDate)>=[Enter Start Day dd/mm/yyyy] And (tblDisciplineIssues.DisciplineDate)<=[txtDateEnd])) OR (((tblDisciplineIssues.DisciplineDate) Is Null) AND ((([tblDisciplineIssues].[DisciplineDate])<=[txtDateEnd]) AND (([Enter Start Day dd/mm/yyyy]) Is Null)));
 
Search starts with a button that opens the report
Click to expand...
I SAID - you should avoid at all costs, prompting for a variable. Use a form. Create unbound controls if you are not going to be using existing controls. That gives you the advantage of being able to validate the variables AND avoid being prompted multiple times for the same variable. That translates to changing the RecordSource of the report to include only the fixed criteria (assuming there is some). So, you have a report that selects items that are late. That is the fixed part of the criteria. Then using the WHERE argument of the OpenReport method, you can add date ranges to filter the criteria to a specified period or add a customerID to show only late items for a specific customer, etc.

The second thing I SAID was to use the WHERE argument of the OpenReport or OpenForm method to provide the criteria. That allows you to use ONE version of the form or report but open it using different criteria.
 
Pat Hartman said:
I SAID - you should avoid at all costs, prompting for a variable. Use a form. Create unbound controls if you are not going to be using existing controls. That gives you the advantage of being able to validate the variables AND avoid being prompted multiple times for the same variable. That translates to changing the RecordSource of the report to include only the fixed criteria (assuming there is some). So, you have a report that selects items that are late. That is the fixed part of the criteria. Then using the WHERE argument of the OpenReport method, you can add date ranges to filter the criteria to a specified period or add a customerID to show only late items for a specific customer, etc.

The second thing I SAID was to use the WHERE argument of the OpenReport or OpenForm method to provide the criteria. That allows you to use ONE version of the form or report but open it using different criteria.
Click to expand...
Pat Ok sorry I'm learning here and really dont follow alot of what you said. I get the unbound control but what code goes in that then for the search parements to be set. maybe this question is just too advanced for me
 
The query becomes:
Code: 
SELECT DISTINCT tblDisciplineIssues.DisciplineID, tblDOHLocations.BureauName, tblDOHLocations.OrgCodes, tblDisciplineYears.DisciplineYearID, [Firstname] & " " & [Lastname] AS Employee, tblDisciplineIssues.EmployeeNumber, tblDisciplineIssues.DisciplineDate, tblDisciplineTypes.DisciplineType, tblDisciplineIssues.Discipline, tblLevel.Level, tblUnionList.UnionName, tblBargainingUnits.Unit, tblBargainingUnits.UnitName, tblJobCodes.JobCode, tblJobCodes.JobTitle
FROM tblRace RIGHT JOIN (tblLevel RIGHT JOIN (tblGenders RIGHT JOIN (tblDOHLocations RIGHT JOIN (tblDisciplineYears RIGHT JOIN ((tblGrievants RIGHT JOIN (tblJobCodes RIGHT JOIN ((((tblDisciplineIssues LEFT JOIN tblDisciplineTypes ON tblDisciplineIssues.DisciplineTypeID = tblDisciplineTypes.DisciplineTypeID) LEFT JOIN tblUnionList ON tblDisciplineIssues.UnionID = tblUnionList.UnionID) LEFT JOIN tblUnionArticles ON tblUnionList.UnionID = tblUnionArticles.UnionID) LEFT JOIN tblBargainingUnits ON tblDisciplineIssues.BargainingUnitID = tblBargainingUnits.BargainingUnitID) ON tblJobCodes.JobCodeID = tblDisciplineIssues.ClassCodeID) ON tblGrievants.GrievantID = tblDisciplineIssues.EmployeeName) LEFT JOIN (tblIssueList RIGHT JOIN tblIssues ON tblIssueList.IssueListID = tblIssues.IssueID) ON tblDisciplineIssues.DisciplineID = tblIssues.DisciplineID) ON tblDisciplineYears.YearID = tblDisciplineIssues.YearID) ON tblDOHLocations.DOHLocationID = tblDisciplineIssues.ProgramOfficeID) ON tblGenders.GenderID = tblDisciplineIssues.GenderID) ON tblLevel.LevelID = tblDisciplineIssues.LevelID) ON tblRace.RaceID = tblDisciplineIssues.RaceID
With NO criteria.

Then the WHERE part Becomes:
Code: 
Dim strWhere as String
strWhere = "(tblDisciplineIssues.DisciplineDate >= #" & Me.txtStartDay
strwhere = stwWhere & strWhere & " # And tblDisciplineIssues.DisciplineDate <= # "
strWhere = strWhere & strWhere & Me.[txtDateEnd]) OR tblDisciplineIssues.DisciplineDate Is Null OR tblDisciplineIssues.DisciplineDate <= #"
strWhere = strWhere & strWhere & Me.txtDateEnd & "# AND Me.txtStartDay Is Null)

DoCmd.OpenReport "yourreportname",acviewPreview,, strWhere

I can't guarantee that the Where is correct but that is how you structure it.

That lets you build multiple Where versions and open the report with any of them.
 
Pat Hartman said:
The query becomes:
Code: 
SELECT DISTINCT tblDisciplineIssues.DisciplineID, tblDOHLocations.BureauName, tblDOHLocations.OrgCodes, tblDisciplineYears.DisciplineYearID, [Firstname] & " " & [Lastname] AS Employee, tblDisciplineIssues.EmployeeNumber, tblDisciplineIssues.DisciplineDate, tblDisciplineTypes.DisciplineType, tblDisciplineIssues.Discipline, tblLevel.Level, tblUnionList.UnionName, tblBargainingUnits.Unit, tblBargainingUnits.UnitName, tblJobCodes.JobCode, tblJobCodes.JobTitle
FROM tblRace RIGHT JOIN (tblLevel RIGHT JOIN (tblGenders RIGHT JOIN (tblDOHLocations RIGHT JOIN (tblDisciplineYears RIGHT JOIN ((tblGrievants RIGHT JOIN (tblJobCodes RIGHT JOIN ((((tblDisciplineIssues LEFT JOIN tblDisciplineTypes ON tblDisciplineIssues.DisciplineTypeID = tblDisciplineTypes.DisciplineTypeID) LEFT JOIN tblUnionList ON tblDisciplineIssues.UnionID = tblUnionList.UnionID) LEFT JOIN tblUnionArticles ON tblUnionList.UnionID = tblUnionArticles.UnionID) LEFT JOIN tblBargainingUnits ON tblDisciplineIssues.BargainingUnitID = tblBargainingUnits.BargainingUnitID) ON tblJobCodes.JobCodeID = tblDisciplineIssues.ClassCodeID) ON tblGrievants.GrievantID = tblDisciplineIssues.EmployeeName) LEFT JOIN (tblIssueList RIGHT JOIN tblIssues ON tblIssueList.IssueListID = tblIssues.IssueID) ON tblDisciplineIssues.DisciplineID = tblIssues.DisciplineID) ON tblDisciplineYears.YearID = tblDisciplineIssues.YearID) ON tblDOHLocations.DOHLocationID = tblDisciplineIssues.ProgramOfficeID) ON tblGenders.GenderID = tblDisciplineIssues.GenderID) ON tblLevel.LevelID = tblDisciplineIssues.LevelID) ON tblRace.RaceID = tblDisciplineIssues.RaceID
With NO criteria.

Then the WHERE part Becomes:
Code: 
Dim strWhere as String
strWhere = "(tblDisciplineIssues.DisciplineDate >= #" & Me.txtStartDay
strwhere = stwWhere & strWhere & " # And tblDisciplineIssues.DisciplineDate <= # "
strWhere = strWhere & strWhere & Me.[txtDateEnd]) OR tblDisciplineIssues.DisciplineDate Is Null OR tblDisciplineIssues.DisciplineDate <= #"
strWhere = strWhere & strWhere & Me.txtDateEnd & "# AND Me.txtStartDay Is Null)

DoCmd.OpenReport "yourreportname",acviewPreview,, strWhere

I can't guarantee that the Where is correct but that is how you structure it.

That lets you build multiple Where versions and open the report with any of them.
Click to expand...
Pat Hartman said:
The query becomes:
Code: 
SELECT DISTINCT tblDisciplineIssues.DisciplineID, tblDOHLocations.BureauName, tblDOHLocations.OrgCodes, tblDisciplineYears.DisciplineYearID, [Firstname] & " " & [Lastname] AS Employee, tblDisciplineIssues.EmployeeNumber, tblDisciplineIssues.DisciplineDate, tblDisciplineTypes.DisciplineType, tblDisciplineIssues.Discipline, tblLevel.Level, tblUnionList.UnionName, tblBargainingUnits.Unit, tblBargainingUnits.UnitName, tblJobCodes.JobCode, tblJobCodes.JobTitle
FROM tblRace RIGHT JOIN (tblLevel RIGHT JOIN (tblGenders RIGHT JOIN (tblDOHLocations RIGHT JOIN (tblDisciplineYears RIGHT JOIN ((tblGrievants RIGHT JOIN (tblJobCodes RIGHT JOIN ((((tblDisciplineIssues LEFT JOIN tblDisciplineTypes ON tblDisciplineIssues.DisciplineTypeID = tblDisciplineTypes.DisciplineTypeID) LEFT JOIN tblUnionList ON tblDisciplineIssues.UnionID = tblUnionList.UnionID) LEFT JOIN tblUnionArticles ON tblUnionList.UnionID = tblUnionArticles.UnionID) LEFT JOIN tblBargainingUnits ON tblDisciplineIssues.BargainingUnitID = tblBargainingUnits.BargainingUnitID) ON tblJobCodes.JobCodeID = tblDisciplineIssues.ClassCodeID) ON tblGrievants.GrievantID = tblDisciplineIssues.EmployeeName) LEFT JOIN (tblIssueList RIGHT JOIN tblIssues ON tblIssueList.IssueListID = tblIssues.IssueID) ON tblDisciplineIssues.DisciplineID = tblIssues.DisciplineID) ON tblDisciplineYears.YearID = tblDisciplineIssues.YearID) ON tblDOHLocations.DOHLocationID = tblDisciplineIssues.ProgramOfficeID) ON tblGenders.GenderID = tblDisciplineIssues.GenderID) ON tblLevel.LevelID = tblDisciplineIssues.LevelID) ON tblRace.RaceID = tblDisciplineIssues.RaceID
With NO criteria.

Then the WHERE part Becomes:
Code: 
Dim strWhere as String
strWhere = "(tblDisciplineIssues.DisciplineDate >= #" & Me.txtStartDay
strwhere = stwWhere & strWhere & " # And tblDisciplineIssues.DisciplineDate <= # "
strWhere = strWhere & strWhere & Me.[txtDateEnd]) OR tblDisciplineIssues.DisciplineDate Is Null OR tblDisciplineIssues.DisciplineDate <= #"
strWhere = strWhere & strWhere & Me.txtDateEnd & "# AND Me.txtStartDay Is Null)

DoCmd.OpenReport "yourreportname",acviewPreview,, strWhere

I can't guarantee that the Where is correct but that is how you structure it.

That lets you build multiple Where versions and open the report with any of them.
Click to expand...
Ok thank you and this goes into the code of the unbound control or do you need an unbound control for each WHERE statement?
 
The thing that's difficult is designing your query

If you want to select 2 variables for a date range, then you may have

1st Feb 2023 and 30th June 2023
(I am not showing as dates, because 1/2/23 and 30/6/23 is UK, and 2/1/23 and 6/30/23 is US), and it's confusing where the dates are ambiguous.

Anyway if you want a date range you are OK
If you want a year range, you need to make the dates 1st Jan 2022 and 31st Dec 2022, in order for your query to work for both a date range and a year range - and it still doesn't work 100% if the dates in the database include times.

So that's the issue - avoiding the need for multiple queries throughout your database for every type of date range you might use. - so you get full flexibility with the minimum effort.
 
The code goes in the click event of the button that runs the report. If you have multiple options for a report, you might want to use an Option Group. That way, the code that opens the report would start with a case statement to see which option was selected. It would build the Where clause for which ever option was selected.
Code: 
Select Case Me.fraCriteriaType
    Case 1
        ''' code to build strWhere for option 1
    Case 2
        ''' code to build strWhere for option 2
    Case Else
        ''' code to build default strShere
End Select

DoCmd.OpenReport "yourreportname",acviewPreview,, strWhere
 
Also, since your standard date format is dmy, you MUST format your dates to mm/dd/yyyy or yyyy/mm/dd format. the dd/mm/yyyy format WILL NOT WORK. SQL ASSUMES US format dates so it can't tell if 2/5/23 is Feb 5 or May 2. Therefore, YOU need to proactively format all STRING dates, which is what you are working with when you build criteria in VBA to be mm/dd/yyyy.

I told you earlier that dates are not internally stored as strings they are converted to strings for human consumption and humans type dates as strings for data entry but that isn't how they are stored. Nor do you want them to be stored as strings so stop even thinking about how you might do that:)

Dates are stored as numeric values to make it possible to do simple arithmetic on them WITHOUT having to convert them.
 
You must log in or register to reply here.

Similar threads

L
Solved Transform Columns in multiple rows
2
Replies
20
Views
477
June7
June7
T
Query Dilemma
Replies
11
Views
356
Pat Hartman
P
S
Penalties applied according to a table
Replies
6
Views
250
arnelgp
arnelgp
S
Link records between two tables based on range?
Replies
3
Views
287
Mark_
M
nicolareina
Single row data query
Replies
13
Views
368
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom