Date Range with Wild Card Query

[DLAFKO]

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

 

[cheekybuddha]

...
WHERE (YourDateField >= [txtDateStart] OR [txtDateStart] IS NULL)
  AND (YourDateField < ([txtDateEnd] + 1) OR [txtDateEnd] IS NULL)

 

[cheekybuddha]

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

Where will you specify the year? In the start and end textboxes too?

 

[DLAFKO]

...
WHERE (YourDateField >= [txtDateStart] OR [txtDateStart] IS NULL)
  AND (YourDateField < ([txtDateEnd] + 1) OR [txtDateEnd] IS NULL)

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

 

[DLAFKO]

Where will you specify the year? In the start and end textboxes too?

The year is just part of the date Like 01/01/2023 Can we just do a search on 2023?

 

[cheekybuddha]

OK thank you so this is VBA Then right in VBA where does this get put.. On the button to access the report?

No, this is just SQL.

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

Are you using it to open a report?

 

[cheekybuddha]

The year is just part of the date Like 01/01/2023 Can we just do a search on 2023?

Yes, but you need to share how you are implementing your search

 

[DLAFKO]

Yes, but you need to share how you are implementing your search

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?

 

[DLAFKO]

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.

Not sure what all that means

Here is the SQL I currently have but if i leave end date blank it returns nothing

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)));

 

[DLAFKO]

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 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

 

[DLAFKO]

The query becomes:

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:

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.

The query becomes:

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:

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.

Ok thank you and this goes into the code of the unbound control or do you need an unbound control for each WHERE statement?

 

[gemma-the-husky]

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.