SQL Statement help

[Xterra14s]

Current SQL Statement:


SELECT IssuesDB.ID, IssuesDB.User, IssuesDB.Date, IssuesDB.Status, IssuesDB.Resolution
FROM IssuesDB
WHERE (((IssuesDB.ID) Like "*" & [forms]![EndUser Search]![Ticket#] & "*") AND ((IssuesDB.User) Like "*" & [forms]![EndUser Search]![User] & "*")) OR (((IssuesDB.ID) Like "*" & [forms]![EndUser Search]![User] & "*") AND ((IssuesDB.User) Like "*" & [forms]![EndUser Search]![Ticket#] & "*"));

Goal: I created a search form in a ticket system database. I have two ways of searching for a ticket: First is by the ticket number but if the user doesn't have that number they can also search by their Username. If I enter the Ticket number using the current SQL statement it works beautifully. If I enter the username it's returning all users since name all share letters example. If in the drop down menu I select the name Bob Green. I'll get results for Bob Green, Tom Bean, Rob Tree since they all share the letter O, E, and so on. I want the names to only be an exact match.

I tried this SQL Statement also but with this it requires the Ticket number AND Username to be an identical match. I want one or the other, not both.

SELECT IssuesDB.ID, IssuesDB.User, IssuesDB.Date, IssuesDB.Status, IssuesDB.ResolutionFROM IssuesDB
WHERE (((IssuesDB.ID)=[Forms]![EndUser Search]![Ticket#]) AND ((IssuesDB.User)=[Forms]![EndUser Search]![User]));


Any and all help would be greatly appreciated!

[ranman256]

change AND to OR

[Xterra14s]

change AND to OR

I did this but it wont show anything in the Query when I look up by User. If I search by Ticket# it does give me results. I think it has to do with ticket# being blank, is there a way to include a null ticket#?

[CJ_London]

try

WHERE (((IssuesDB.ID)=[Forms]![EndUser Search]![Ticket#] OR [Forms]![EndUser Search]![Ticket#] is null) AND ((IssuesDB.User)=[Forms]![EndUser Search]![User] OR [Forms]![EndUser Search]![User] is null));

Using #, spaces or other non alphanumeric characters in table and field names is a bad idea. At some point it will come back to bite you with an error message that does not make sense and you will spend hours trying to figure it out

[Xterra14s]

try

WHERE (((IssuesDB.ID)=[Forms]![EndUser Search]![Ticket#] OR [Forms]![EndUser Search]![Ticket#] is null) AND ((IssuesDB.User)=[Forms]![EndUser Search]![User] OR [Forms]![EndUser Search]![User] is null));

Using #, spaces or other non alphanumeric characters in table and field names is a bad idea. At some point it will come back to bite you with an error message that does not make sense and you will spend hours trying to figure it out

It didn't bring back any results =(

[CJ_London]

well that is the usual way of handling null controls for a multiple criteria.

suggest confirm your form is populated with data which will return something.

Also try changing the AND to OR per Ranmans post

[davegri]

How about 2 SQL selects, one for when the name is provided and the other when the ticket number is provided. Then the search is on the name or the ticket, not having to provide for nulls at all.