Date Wildcard search 1

[btrini10]

Hi All
I have a field defined as Date/Time and am trying to do a wildcard search. The "*" is not working. I am not sure what else can be used. Any advice?

 

[PHV]

The Like operator is devoted to strings, so test against a formatted date ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[btrini10]

Hi
Part of the problem is that the filed is null until a date is entered. I would like to use a wildcard search that produces the record without regard to it being null or having a valid date. In my query I am using a between expresion where the user enters a start and end date. How do I get the query to show all transactions if the user does not want to enter a start and end date?

 

[PHV]

What is your actual WHERE clause in the SQL view pane ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[btrini10]

Hi PHV
Following is the SQL select statement for the query:
SELECT Project.[Project #], Project.[Project Name], Project.SubCategory, Project.[Budgeted Hours], Project.[Start Date], Project.[As of As of Date], Project.[End of Field Work], Project.[Close Date]
FROM Project
WHERE (((Project.[Project #]) Like [Forms]![Project Listing]![Combo0]) AND ((Project.[Start Date]) Between [Forms]![Project Listing]![Startbegin] And [Forms]![Project Listing]![StartEnd]) AND ((Project.[As of As of Date]) Between [Forms]![Project Listing]![Asofbegin] And [Forms]![Project Listing]![Asofend]) AND ((Project.[End of Field Work]) Between [Forms]![Project Listing]![Endbegin] And [Forms]![Project Listing]![Endend]) AND ((Project.[Close Date]) Between [Forms]![Project Listing]![Closedbegin] And [Forms]![Project Listing]![Closedend]));

I would like the user to have the option to see all projects or select specific dates.
Thanks

 

[PHV]

Something like this ?
SELECT [Project #], [Project Name], SubCategory, [Budgeted Hours], [Start Date], [As of As of Date], [End of Field Work], [Close Date]
FROM Project
WHERE [Project #] Like [Forms]![Project Listing]![Combo0]
AND ([Start Date] >= [Forms]![Project Listing]![Startbegin] OR [Forms]![Project Listing]![Startbegin] IS NULL)
AND ([Start Date] <= [Forms]![Project Listing]![StartEnd] OR [Forms]![Project Listing]![StartEnd] IS NULL)
AND ([As of As of Date] >= [Forms]![Project Listing]![Asofbegin] OR [Forms]![Project Listing]![Asofbegin] IS NULL)
AND ([As of As of Date] <= [Forms]![Project Listing]![Asofend] OR [Forms]![Project Listing]![Asofend] IS NULL)
AND ([End of Field Work] >= [Forms]![Project Listing]![Endbegin] OR [Forms]![Project Listing]![Endbegin] IS NULL)
AND ([End of Field Work] <= [Forms]![Project Listing]![Endend] OR [Forms]![Project Listing]![Endend] IS NULL)
AND ([Close Date] >= [Forms]![Project Listing]![Closedbegin] OR [Forms]![Project Listing]![Closedbegin] IS NULL)
AND ([Close Date] <= [Forms]![Project Listing]![Closedend] OR [Forms]![Project Listing]![Closedend] IS NULL);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[btrini10]

Hey PHV
I am not sure this would work since, if the user chooses to enter a start and end date for any particular field, then i would not want to see any records with a value of Null in that field. Is there any way to include an If statement in the where clause so that all records are displayed, without regard to whether it is null or has a date, if the user does not enter a start and end date. Or if a start date is entered, show all records after that date, even the ones that have Null in the end date.

 

[HuzbinPharteen]

The * wildcard works with text fields, not Date/Time. You will need to use either >= [your value] or a BETWEEN clause. Remember date/time must be enclosed with # signs.

Good luck

 

[PHV]

btrini10, have you even tried my suggestion ?

 

[btrini10]

PHV
I apologize for even doubting the code in the first place. I did not and still do not understabnd how the simple SQL statement produced such a complex query.
Thank you so much again.