QBF assistance puleeze

[Merrie]

I have created a query by form using the instructions here:
__________
1. Create unbound form with text boxes for each field to search. Name text boxes. Save form and reopen it
2. With form open, create query
In order to get variations on the search term use asterisks and Like
Like "*" & [forms]![Search_Form]![Name] & "*"
________

I have a basic table which has a Keywords field. It is just a text field; in some records, I have entered multiple keywords in that field.

When I search on Keyword, the db returns all records with any keyword as well as some records with no data in the field. I have two other searches (name, state) which work perfectly and only return records with data but the keyword search doesn't work at all.

Here is the search query that the QBF runs. I have substituted 'database' for my DB name.
_________________
SELECT Database.Name, Database.State, Database.Keywords
FROM Database
WHERE (((Database.Name) Like "*" & [forms]![Search_Form]![Name] & "*") AND ((Database.State) Like "*" & [forms]![Search_Form]![State] & "*") AND ((Database.Keywords) Like "*" & [forms]![Search_Form]![Keywords] & "*"));

I have tried a number of things with no success.
I want the db to return the records ONLY if the search string exists.
Should I structure the keyword field differently? - I'm thinking the * tells the DB to return all records in the field but don't know how to search a partial keyword string without returning irrelevant results.

I created a query like this before and it worked but it was years ago and I've lost the plot.
Any help would be greatly appreciated.

 

[isladogs]

Here is the search query that the QBF runs. I have substituted 'database' for my DB name.
_________________
SELECT Database.Name, Database.State, Database.Keywords
FROM Database
WHERE (((Database.Name) Like "*" & [forms]![Search_Form]![Name] & "*") AND ((Database.State) Like "*" & [forms]![Search_Form]![State] & "*") AND ((Database.Keywords) Like "*" & [forms]![Search_Form]![Keywords] & "*"));

I have tried a number of things with no success.
I want the db to return the records ONLY if the search string exists.
Should I structure the keyword field differently? - I'm thinking the * tells the DB to return all records in the field but don't know how to search a partial keyword string without returning irrelevant results.

Hi

first of all your query should have a TABLE name and not your database name

SELECT MyTableName.Name, MyTableName.State, MyTableName.Keywords 
FROM MyTableName
WHERE (((MyTableName.Name) Like "*" & [forms]![Search_Form]![Name] & "*") AND ((MyTableName.State) Like "*" & [forms]![Search_Form]![State] & "*") AND ((MyTableName.Keywords) Like "*" & [forms]![Search_Form]![Keywords] & "*"));

Does it give any records if you remove the filters?

SELECT MyTableName.Name, MyTableName.State, MyTableName.Keywords FROM MyTableName;

If yes, then review your filters.
At the moment ALL 3 have to match to give any records
If you want any 1 of them to apply, use OR in place of AND

Now carefully check what you EXPECT to get against what you do get
If you still aren't getting what you want restore the filters ONE at a time and recheck each time

HTH

 

[Merrie]

Thanks for your answer but it does not work

Yes, it is tablename.field not databasename.field - sorry for the confusion but

No, putting OR instead of AND did not work. My problem is the third part of the query. If I search on Name, that works. If I search on State, that works. However the Keyword search returns all records whether or not they have the character string entered in the form and even if they are blank. This does not happen with the 'name' part of the query or the 'state' part.

I need to enter a keyword into the search form and have the query return only records with that keyword. The keyword field is a text field with multiple entries. So, for instance in a record with 'letter' 'call' and 'apples' in the keyword field, if I search on 'apples', I want that record. I don't want records that do not have 'apples' in the keyword field but I get them anyway.

This does not happen if I search for a particular name or a particular state so the AND wasn't causing a problem.

 

[isladogs]

OK so what's different about the Keywords field?
Is it the same datatype as the others?

Suggest you post some data so one of us can work out why its giving different results

 

[Merrie]

I have attached two snips - one is the info on the field and the other is some test data I entered.

I tried to search on pears - got all records

Sigh

 

[isladogs]

I'm sorry but the 2 screenshots don't help me.

I'm about to log off as its very late here.

If you can post a version of your db with the table, form & anything else necessary to make it work, I'll look at it hopefully tomorrow

 

[Merrie]

Thanks very much, Colin, for your offer to help. After banging my head on it all day, I created a copy to send you and then opened it yet again and checked all the properties of the form. I had missed passing a field name. This is why I will never be a programmer - not enough attention to detail. Thanks again from a redfaced me.

 

[NauticalGent]

I had missed passing a field name. This is why I will never be a programmer - not enough attention to detail.

First of all, great job on figuring out what was wrong on your own...one reason why you have the possibility of become a programmer.

I myself am NOT a programmer and I do not presume to be at a higher level than anyone here. That being said, my coding skills and logic flow have increased exponentially in the last year thanks to this site, my flawless ability to discover the WRONG way the do what it is I and trying to do...but more importantly it is my refusal to quit. My coworkers are tired of hearing me say to myself "Son, you're a special kind of stupid, ain't ya?!?"

Even the Access Studs on here will tell you they don't always get it right the first time.

What is even worse, is that as you improve and gain confidence to do even "cooler" stuff with your code, the more you're going to realize just how much there is to learn.

Keep at it, there are better days ahead!

 

[isladogs]

Agree with everything nautical gent just wrote.
Glad you got it sorted.
I regularly make mistakes.
In fact today I've got to fix whatever I did wrong yesterday that broke something that used to work ....