Searching across many fields with the same term in a query

[Craggy]

Is it possible to search across many fields for the same search term field?

I basically need it to do this kind of search:

If [ref] or [name] or [address] contains [*search term*]

This needs to be incorporated along with a few other filters too, so it would have to be part of a query i guess..

Thanks

[Zwoker]

Is it possible to search across many fields for the same search term field?

I basically need it to do this kind of search:

If [ref] or [name] or [address] contains [*search term*]

This needs to be incorporated along with a few other filters too, so it would have to be part of a query i guess..

Thanks

Hi,

Are you wanting to do this in a Query created in Design View, or are you talking about doing it in VBA code (in the click event of a button on a form, for example)?

And what would the source of the *search item* be? Is it a user entered value in a field on a form, or prompted at runtime when the user runs a query? Something else?

Let me know what you are wanting and I should be able to give you some examples of how to do it.

Regards,
Zwoker.

[Craggy]

Hi,

Are you wanting to do this in a Query created in Design View, or are you talking about doing it in VBA code (in the click event of a button on a form, for example)?

And what would the source of the *search item* be? Is it a user entered value in a field on a form, or prompted at runtime when the user runs a query? Something else?

Let me know what you are wanting and I should be able to give you some examples of how to do it.

Regards,
Zwoker.

thanks.

I think this needs to be done in a query so that I can filter my records easier.

currently my query filters out records flagged as deleted and then filters for the type of record (complaint, incident and compliment)

It would be useful if I could have a search box added on to this as well, so I could, for example, search for all records flagged as "complaints " that contained the word "internet" in any of the fields.

The form has about 6 fields that I would like to check for the presence of the keyword, but it wont have much impact if every field within the query is checked for the keyword.

Thanks for your help

[Craggy]

Any help?

Seems like a basic function for most databases. being able to do a "find" across many fields...

[Zwoker]

Hi,

Sorry for the delay in replying.

If you are putting it in a static query, and the data items involved are Alpha (strings) then you can add an entry to the criteria field of each relevant column in your query, like the following:

Like "*" & [Forms]![<form name>]![<form field name>] & "*"

The items in the angle brackets would be replaced with whatever the appropriate name is.

I have found that this kind of selection criteria seems a little slow, and can be unreliable where you might have an item that matches a portion of a longer value that you don't want, or where you want to ignore the selection criteria for a certain field (I.e. choose an ALL option on your form).
It is NOT a good idea to use it for numeric fields at all.

I gave up on queries written in the design view environment and switched to querying my data sources via dynamicaly created SQL strings. I found this faster and more reliable, as I could code to get the exact records I wanted.

Let me know if you need any more details.

[Craggy]

Hi,

Sorry for the delay in replying.

If you are putting it in a static query, and the data items involved are Alpha (strings) then you can add an entry to the criteria field of each relevant column in your query, like the following:

Like "*" & [Forms]![<form name>]![<form field name>] & "*"

The items in the angle brackets would be replaced with whatever the appropriate name is.

I have found that this kind of selection criteria seems a little slow, and can be unreliable where you might have an item that matches a portion of a longer value that you don't want, or where you want to ignore the selection criteria for a certain field (I.e. choose an ALL option on your form).
It is NOT a good idea to use it for numeric fields at all.

I gave up on queries written in the design view environment and switched to querying my data sources via dynamicaly created SQL strings. I found this faster and more reliable, as I could code to get the exact records I wanted.

Let me know if you need any more details.

Thanks,

This works fine for a single field, but if i wanted the same term to be searched over several different fields, how would I go about this.

If I was to use that formula in each of my desired fields then it would only return results where every field contained the keyword - right?


i.e.

we have field1 and field2 and then we have search

in the query for field1 we would have: Like "*" & [Forms]![<form name>]![search]& "*"

and the same in field2

This would only display results if both field1 and field2 contained search

what I need is if field1 OR field2 contains search

I know how I would go about that in VBA for a single calculation but i dont know how I would go about that for an entire query.

Thanks

[Stewart Ross]

...we have field1 and field2 and then we have search
in the query for field1 we would have: Like "*" & [Forms]![<form name>]![search]& "*"
...
what I need is if field1 OR field2 contains search

Hi. To OR criteria in the Query Editor place them in staggered criteria rows, like this

Field 1 Field 2 Field 3
Like ...
		Like... 
			 Like ...

or use the SQL view and add the criteria directly into the WHERE clause ORing the fields as appropriate.

-Stewart

[Craggy]

Oh my.. its so obvious i feel stupid now!

Thanks.

There is one more problem I face now, this works fine for fields that have something in it, but does not display a record that is completely blank...

even if I use

like "*"

on its own, only completed records appear. (I dont want to force peopel to have to enter some text in every field)

Is there a way to ensure that the query does not filter out null records?

Thanks again.

[Craggy]

Im trying to figure this out...

So far ive played about with iif's

Like IIf([Forms]![MAIN]![searchpannel].[Form]![searchtext] Is Not Null,"*"&[Forms]![MAIN]![searchpannel].[Form]![searchtext]&"*","*")

(which is really quite pointless!)

Now if im not mistaken, this should check that the search field contains something. if it does, then it uses this as the search criteria. If its not, then it uses "*"

Obviously the problem here is that "*" will filter out the null fields that I want to inculde.(making the above formula pointless unless theres a way of substituting "*" for something that will just search for everything)

Is there a way to tell it not to bother trying to filter that field at all if the search box is null?

Thanks!

[Stewart Ross]

...Is there a way to ensure that the query does not filter out null records?

Sure. The simplest way is to change the conditions in the staggered criteria lines of your query to
like "<just as before>" OR Is Null
where <just as before> is the existing criterion in each of your like statements.

-Stewart

[Craggy]

Thanks for the quick response!

the problem with that approach is it will then include all null records along side my filtered records.

i.e.

I search for "Internet", and it finds 2 records that contain internet. it will now also include every record that is blank alongside those.

Like IIf([Forms]![MAIN]![searchpannel].[Form]![searchtext] Is Not Null,[Forms]![MAIN]![searchpannel].[Form]![searchtext],ALL RECORDS)

I think i need something like above, where ALL RECORDS is however way you tell access to just ignore the criteria all together and act as if it was blank (which "*" does not)

[Stewart Ross]

...I think i need something like above, where ALL RECORDS is however way you tell access to just ignore the criteria all together and act as if it was blank (which "*" does not)

Oh, I see, it is if your form search box is null, not the fields themselves.

Still a simple way to do it: just enclose the reference to your text box in the Nz() function, which will return an empty string if the text box is null:

Like "*" & Nz([forms]![formname]![controlname]) & "*"

-Stewart

[Craggy]

Sorry about this confusion.

It is the fields that are blank that is causing the problem

e.g.

1) Dave
2)
3) Danny
4) Don
5)

Using the standard like "*"&<search>&"* " technique:

If the search string is blank then we get the following results:

1) Dave
3) Danny
4) Don

(I need this to include the two blank fields 2 & 5)

If the search string is "a" then we get the following results:

1) Dave
2) Danny

If I add the "or" criteria for "is null" on another line then no matter what I search for, the blank fields will be included as well. So searching for "a" results in:

1) Dave
2)
3) Danny
5)

Obviously I would like this to only show 1 & 3

I tried incorporating the or on the same criteria, which seems like it should work:

iif ( <search> is not null, <search> , "*" or is null)

but this converted my query into some huge query that didnt work.

Using the NZ function seems to work the same as the standard Like "*"&<search>&"* " technique


The problem is "*" does not null fields which is really making somethign that should be simple overly complex!

I suppose a sloppy work arround would be to give each field a value upon creation, but this is not really ideal.

It seems there should be a way to ignore the criteria under certain circumstances

e.g.

iif ( <search> is not null, <search> ,Display ALL records)

Where Display ALL records includes all records, whether it is null or contains a value, as if I had not entered any criteria at all. Usually I would use "*" which displays all records UNLESS they are null...

[Stewart Ross]

Hi. You are mixing up apples and pears here; a null field value is not a blank string; it indicates that a field has no current value. It has no data type; it is not a string, a number or anything else - it is if anything a placeholder just indicating 'nothing entered here'.

Nulls complicate all forms of calculation, field comparisons and so on because of their nature; nulls tell us where data is missing, if you like, not where it is present. You are searching text strings for matching values; it would be a strange comparison operator that automatically returned values that have not been entered as well.

The simplest approach is to set the default value of the text fields in your underlying table to an empty string, "", which will make sure that all of your comparisons work just as you intend them to without doing anything else at the query end at all. You would need to run an update query to set all currently-null text fields to an empty string, but this takes just moments to do.

If you don't want to take the simple approach you can replace the criterion in each of the staggered criterion lines with something based on the skeleton below

(Like "

<just as before>[b]") OR (Is Null AND (Nz(forms![formname]![controlname]) = ""))[/b]

Access is likely to remove brackets around some of the expressions, but I have included them for clarity. I have used Nz for your forms control reference in case the query is run with no entry made in that textbox - a null value in other words.

-Stewart

[Craggy]

Thanks again for your help.

I assumed that since the field types were set to text or memo in the table that when they were created they would default to a blank text or memo field rather than a null field.

I set each text/memo field with a default value of "" and it works fine now.

Its much clearer now what the problem was, thanks