473,320 Members | 1,722 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Searching across many fields with the same term in a query

41
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
Mar 11 '08 #1
15 1889
Zwoker
66
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.
Mar 11 '08 #2
Craggy
41
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
Mar 13 '08 #3
Craggy
41
Any help?

Seems like a basic function for most databases. being able to do a "find" across many fields...
Mar 17 '08 #4
Zwoker
66
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.
Mar 17 '08 #5
Craggy
41
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
Mar 18 '08 #6
Stewart Ross
2,545 Expert Mod 2GB
...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

Expand|Select|Wrap|Line Numbers
  1. Field 1 Field 2 Field 3
  2. Like ...
  3.         Like... 
  4.              Like ...
or use the SQL view and add the criteria directly into the WHERE clause ORing the fields as appropriate.

-Stewart
Mar 18 '08 #7
Craggy
41
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.
Mar 19 '08 #8
Craggy
41
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!
Mar 19 '08 #9
Stewart Ross
2,545 Expert Mod 2GB
...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
Mar 19 '08 #10
Craggy
41
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)
Mar 19 '08 #11
Stewart Ross
2,545 Expert Mod 2GB
...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
Mar 19 '08 #12
Craggy
41
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...
Mar 20 '08 #13
Stewart Ross
2,545 Expert Mod 2GB
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
Expand|Select|Wrap|Line Numbers
  1. (Like "
Expand|Select|Wrap|Line Numbers
  1. <just as before>") OR (Is Null AND (Nz(forms![formname]![controlname]) = ""))

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
Mar 20 '08 #14
Craggy
41
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
Mar 20 '08 #15
Zwoker
66
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
I think you have discovered one of the reasons that made me move away from the static queries. I could never work out what the issue was when I wanted an "ALL" type option in the user selection fields on the form. I tried all sorts of things like having empty fields, or a second (non-visible) column in the pick lists, with an "*" that was used in the query criteria, along with all sorts of other work arounds, without understanding what the prime cause of the problem was.

I'm curious about one thing - are any of your selection text boxes for numeric fields? I never found a work around for that - if the user entered a short numeric selection then the asterisks on each side of the criteria in the query would cause it to return any record that had that value as a substring of the field being checked against, as well as the desired true match. But removing the asterisks seemed (from memory) to cause more problems. Perhaps it was the null issue that I was running into there too, without realising it.
Mar 24 '08 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: donald | last post by:
Hi all, I have a website running asp (about to move to asp.net soon though) which has a list of DVD's I have the various pages I want, last 10, listing, full listing ect, but the one page i can't...
1
by: Robert Oschler | last post by:
I read a while back that MySQL will only use one index per query. (If this is not so, please tell me and point me to a doc that gives a good explanation of MySQL's current index usage policy). ...
1
by: jj | last post by:
Hi NG I got 2 tables In both of the tables there are 4 indentical fields 1) Road name 2) House number 3) Letter 4) Floor In table one there is an extra field - an Id field. In this table...
1
by: Lloyd Stevens | last post by:
Is there a way to peform a query search on a database where you type in a word to be searched across multiple fields? a bit like a search engine if you like. for example if searching a stock...
2
by: Tim Pollard | last post by:
Hi I'm hoping someone can help me with an access problem I just can't get my head around. I normally use access as a back end for asp pages, just to hold data in tables, so queries within access...
3
by: ANDY AIYER | last post by:
Guru's! Your time and guidance is much appreciated in this task that i am trying to get done. Background I have a SQL Server 2000 database table which contains 2 Fields (RecordID, XMLData...
7
by: pbd22 | last post by:
Hi. I am somewhat new to this and would like some advice. I want to search my xml file using "keyword" search and return results based on "proximity matching" - in other words, since the search...
7
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
0
by: tmdb57 | last post by:
I need to search for different names that may or may not exist in each of the tables in a database that contains 155 tables. The fields may not be named the same, and, the search names may exist in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.