Multi-field Search Form, only the date range is not working

ejman7

New Member
Joined
Jan 4, 2018
Messages
2
I have a query which filters the table based on data entered into a search form. The fields are IssueID, IssueDescription, IssueOwner, IssueStatus, and Issue Date Range (using IssueDateBegin and IssueDateEnd). This works perfectly for all fields, EXCEPT for the date range. Here's my code:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">SELECT Issue_Tracker_Table.IssueID, Issue_Tracker_Table.IssueDescription, Issue_Tracker_Table.IssueDate, Issue_Tracker_Table.IssueOwner, Issue_Tracker_Table.IssueStatus
FROM Issue_Tracker_Table
WHERE (((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") AND ((Issue_Tracker_Table.IssueDescription) Like "*" & [Forms]![Issue_Search_Form]![IssueDescription] & "*") AND ((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd]) AND ((Issue_Tracker_Table.IssueOwner) Like "*" & [Forms]![Issue_Search_Form]![IssueOwner] & "*") AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*")) OR (((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") AND ((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd]) AND ((Issue_Tracker_Table.IssueOwner) Like "*" & [Forms]![Issue_Search_Form]![IssueOwner] & "*") AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") AND (([Forms]![Issue_Search_Form]![IssueDescription]) Is Null)) OR (((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") AND ((Issue_Tracker_Table.IssueOwner) Like "*" & [Forms]![Issue_Search_Form]![IssueOwner] & "*") AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") AND (([Forms]![Issue_Search_Form]![IssueDescription]) Is Null)) OR (((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") AND (([Forms]![Issue_Search_Form]![IssueDescription]) Is Null) AND (([Forms]![Issue_Search_Form]![IssueOwner]) Is Null)) OR (((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") AND ((Issue_Tracker_Table.IssueDescription) Like "*" & [Forms]![Issue_Search_Form]![IssueDescription] & "*") AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") AND (([Forms]![Issue_Search_Form]![IssueOwner]) Is Null)) OR (((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") AND ((Issue_Tracker_Table.IssueDescription) Like "*" & [Forms]![Issue_Search_Form]![IssueDescription] & "*") AND ((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd]) AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") AND (([Forms]![Issue_Search_Form]![IssueOwner]) Is Null)) OR (((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") AND ((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd]) AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") AND (([Forms]![Issue_Search_Form]![IssueDescription]) Is Null) AND (([Forms]![Issue_Search_Form]![IssueOwner]) Is Null)) OR (((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd]));
</code><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">I appreciate all help and guidance.</code>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Added formatting to clarify the logic:
Code:
SELECT Issue_Tracker_Table.IssueID, Issue_Tracker_Table.IssueDescription, Issue_Tracker_Table.IssueDate, Issue_Tracker_Table.IssueOwner, Issue_Tracker_Table.IssueStatus
FROM Issue_Tracker_Table
WHERE 
	(
		((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") 
		AND ((Issue_Tracker_Table.IssueDescription) Like "*" & [Forms]![Issue_Search_Form]![IssueDescription] & "*") 
		AND ((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd]) 
		AND ((Issue_Tracker_Table.IssueOwner) Like "*" & [Forms]![Issue_Search_Form]![IssueOwner] & "*") 
		AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*")
	) 
	OR 
	(
		((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*")
		AND ((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd]) 
		AND ((Issue_Tracker_Table.IssueOwner) Like "*" & [Forms]![Issue_Search_Form]![IssueOwner] & "*") 
		AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") 
		AND (([Forms]![Issue_Search_Form]![IssueDescription]) Is Null)
	) 
	OR 
	(
		((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") 
		AND ((Issue_Tracker_Table.IssueOwner) Like "*" & [Forms]![Issue_Search_Form]![IssueOwner] & "*")
		AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") 
		AND (([Forms]![Issue_Search_Form]![IssueDescription]) Is Null)
	) 
	OR
	(
		((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") 
		AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") 
		AND (([Forms]![Issue_Search_Form]![IssueDescription]) Is Null) 
		AND (([Forms]![Issue_Search_Form]![IssueOwner]) Is Null)
	) 
	OR 
	(
		((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*")
		AND ((Issue_Tracker_Table.IssueDescription) Like "*" & [Forms]![Issue_Search_Form]![IssueDescription] & "*") 
		AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") 
		AND (([Forms]![Issue_Search_Form]![IssueOwner]) Is Null)
	) 
	OR 
	(
		((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") 
		AND ((Issue_Tracker_Table.IssueDescription) Like "*" & [Forms]![Issue_Search_Form]![IssueDescription] & "*") 
		AND ((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd]) 
		AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") 
		AND (([Forms]![Issue_Search_Form]![IssueOwner]) Is Null)
	) 
	OR 
	(
		((Issue_Tracker_Table.IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*") 
		AND ((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] 
		And [Forms]![Issue_Search_Form]![IssueDateEnd]) 
		AND ((Issue_Tracker_Table.IssueStatus) Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*") 
		AND (([Forms]![Issue_Search_Form]![IssueDescription]) Is Null) 
					AND (([Forms]![Issue_Search_Form]![IssueOwner]) Is Null)
	) 
	OR 
	(
		((Issue_Tracker_Table.IssueDate) Between [Forms]![Issue_Search_Form]![IssueDateBegin] And [Forms]![Issue_Search_Form]![IssueDateEnd])
	);

I find it hard to decipher but with dates at least you should try to make sure that when you have dates in forms you know if they are true dates or simply text values that look like dates. If the dates in the database are true dates but the dates in the form textboxes are just plain text then you have to convert the textbox values to dates when you do any comparison to the true dates in your data table.
 
Last edited:
Upvote 0
Thanks for cleaning up my code displayed Xenou. For clarification, the reason I have the multiple OR statements to allow for instances where a field is left blank, to which I added the Is Null for those.

I have the field on the form formatted as a Short Date field. Is this enough, or is there a better way to ensure the dates on the form are formatted as dates? Will applying an input mask work for the form field, or should I format the form fields in the query directly?
 
Upvote 0
Generally formatting is irrelevant. What matters is the data type. Is it a data type of Text or a data type of Date. This has to be asked of both the control on the form and the value in the data table.
 
Upvote 0
I usually evaluate the date twice when I want to allow for nulls in a search form - greater than or equal to the start and less than or equal to the end. As commented above you will want to make sure both the controls are date format as well as the fields in the table.

You shouldn’t need to test the non-date fields for nulls because you are using wildcards.

Also instead of using the query design screen I find it much easier to do this in SQL – Access has a tendency to make it look very complex.

Try:
Code:
SELECT IssueID,  IssueDescription, IssueDate,  IssueOwner, IssueStatus
FROM Issue_Tracker_Table
WHERE NZ(IssueID) Like "*" & [Forms]![Issue_Search_Form]![IssueID] & "*"
AND NZ(IssueDescription) Like "*" & [Forms]![Issue_Search_Form]![IssueDescription] & "*"
AND NZ(IssueOwner) Like "*" & [Forms]![Issue_Search_Form]![IssueOwner] & "*"
AND NZ(IssueStatus)  Like "*" & [Forms]![Issue_Search_Form]![IssueStatus] & "*"
AND (IssueDate >= [Forms]![Issue_Search_Form]![IssueDateBegin] OR [Forms]![Issue_Search_Form]![IssueDateBegin] is null)
AND (IssueDate <= [Forms]![Issue_Search_Form]![IssueDateEnd] OR [Forms]![Issue_Search_Form]![IssueDateEnd] is null)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top