Search bar query criteria

[Genestealer]

Hi guys,

I have developed a league registration system for a SE London Veterans Associated Football league. (Edit: I am using Access 2003)

The system is currently searchable by clicking a dropdown box at the top which the queries refer to.

However a new team has just joined which has a similar name to another and it has caused me a headache.


In my queries under the field criteria I currently have the code:
Like "*" & [Forms]![mainmenu].[searchCombo1]

which refers to the location of the searchbar.
Due to the first part of the criteria if the search bar is empty this code displays all the records (which is a desired result)

However with the new team the Like "*" & creates problems.

Example:
There is one team we shall call Supertown
Now added to the league are an annoying team called AFC Supertown

Now when the search bar requests Supertown it also brings up AFC Supertowns results. I intially deleted the Like "*" & hoping this would fix the problem. But of course this meant I lost the ability to view all when the field is null. Which I need.

So in summary I need a query criteria that displays the results of the searchbar as they appear exactly
And display all the results if the searchbar is null

Please can anyone help

 

[Chrafangau]

I'm not certain this will work, but try replacing

Like "*" & [Forms]![mainmenu].[searchCombo1]

with:

IIf([Forms]![mainmenu].[searchCombo1]="",Like "*",[Forms]![mainmenu].[searchCombo1])

This *should*, if I understand your method correctly, provide you with all results when the combo box is blank, and only exact matches otherwise.

 

[Genestealer]

Posted to another forum where the answer which worked:

[Forms]![mainmenu].[searchCombo1] OR [Forms]![mainmenu].[searchCombo1] Is Null

Thanks Chrafangau for your help