I just dont understand

[MOTOWN44]

Hello

I have a problem with the search function that CORE helped me with in the Queries Macros and More thread.

Basically I have ditched the filters for a standard search function.

The user is presented with a form with combo boxes that contain the search criteria eg site, task priority and status.

The boxes are called StatusCombo0, PriorityCombo0 etc etc.

These are liked to a query that has the criteria

Like [Forms]![FrmSEARCH]![PriorityCombo0]

In each field (obviously the status combo is liked to the status field and so on.

This works fine in that it displays results the issue is the results its displays…which is also where you may not understand what im saying because I cant really explain it very well..ive drawn a few blank faces in the office explaining this lol

Basically. When I search by site in my combo box I select Newcastle this displays all the results for Newcastle as you would expect…oh no

(And now for the bit I cant explain…)

For some reason the only results that are displayed are the ones that have a value of Newcastle in the Site field, and only if there is a value in the other fields I am searching even if I have left them blank.

Eg Newcastle is in the site search field, priority and status have the default * in the combo box so they will display any value.
Now when I hit search the only results it displays are the ones that have a value of Newcastle in site, and any other value priority and status. If there is a blank in either of these 2 criteria it will emit the result from the results.

I think the problem is its not searching for Null values as part of the query, ive played on with Is Null and Is Not Null but I can display every table result or none when I do this.

Any Help will be VERY much appreciated.

Thanks

Matthew

 

[namliam]

This is very logical behaviour for the way you build this...

Do a search on the forum for "search form" you should find some samples how to do this "properly" and circumvent this problem.

 

[Kryst51]

Here's a thread on the topic. I just did a quick search in google, using the advance d technique

Maybe it will help.

 

[SpiritedAway]

If you go onto Microsoft's website and download their contacts template - they have a filtered search query which searchs across the whole form in all fields without the need to click in specific field or use wildcard tags. Just download it, look at the filter macro, familarise yourself with it, and then in most cases, if you go into design view and cut n paste the textbox&button (including its related macro) and drop it in your own form it will work perfectly. although you will need to go into the macro and type in the fields of your form you wish to search.

 

[DCrake]

Just to explain what's happening, when you use a wildcard such as an asterisk what you are asking Access to do is show you all records irrespective of what is in the field. And this is where the issue is Something is something, Nothing is Nothing, Nothing is not something. So a Null field does not contain anything, therefore it is not picked up. So to get around this problem you would have to use the syntax

Like "*" Or Is Null


This would then return the correct filtering.

David

 

[Banana]

One thing about templates from that place is that they usually highlight what Access can do, but not necessarily the best practice.

Filtering by form is probably most ineffective way of doing things and I don't think it's has been seriously used in any applications that has more than 2 users.

 

[Kryst51]

One thing about templates from that place is that they usually highlight what Access can do, but not necessarily the best practice.

Filtering by form is probably most ineffective way of doing things and I don't think it's has been seriously used in any applications that has more than 2 users.

What would you say is a better method, out of curiousity?