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

[ejman7]

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>

 

[xenou]

Added formatting to clarify the logic:

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.

 

[ejman7]

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?

 

[xenou]

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.

 

[stumac]

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:

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)