Search form with multiple keywords for one criterion

[Wilkobeczka]

Hello!
I have a problem with creating a search form which would allow me to look for the records by multiple keywords in the criterion. My db stores information about the characters in the book. How can I make my search form recognize that it stores multiple info about one character and every additional information is not the next one?
Here is how it looks like.


This is the way I arranged the characteristics with characters:

And this is how I've set the query:

The results are as follows: when I want to type a fault which is not in the same line with a virtue, but is connected with the same character, the access doesn't recognize it. What is more I cannot look for characters by typing for example two virtues that I would like them to have. I have a proper (I hope) relation between the main table of the character and the table with its personality traits.

Is there any way to solve my problem?

[andy49]

Two thoughts. You have filled in the same row which I believe is an AND so it must find both in one record before listing. Try putting the second like statement on the row below.

Secondly, have you considered searching for like * & criteria & *




Sent from my iPhone using Tapatalk

[Wilkobeczka]

WORKED! Thank you very much! It now shows a character even if the virtue and a fault are not in the same line.

However, it still doesn't allow to type many characteristics in one field, as I want to find characters which are BOTH kind and have a good-ear. Or I do not know how to type it, but I've tried several different methods and it still didn't work.

[andy49]

This may be of use.

It includes creating two or more search boxes or creating a parameter query

https://access-programmers.co.uk/for...d.php?t=140861



Sent from my iPhone using Tapatalk

[ssanfu]

IMO, your problems are caused by the table "TblCharactersWithPersonality". It is designed like a spreadsheet. There are 2 traits in 1 record.

I would change the table design to:

TblCharactersWithPersonality
-----------------------------

Field Name	Field type	Example
ID_PK	Autonumber
Character_FK	Long	Link to TblMainCharacter
Trait	Text	Kind, Good-ear, ...
TraitType	Text	Fault or Virtue
Characteristic	Integer	???

[Wilkobeczka]

Ssanfu,
Thanks, now the table looks much better. However, how should I set the criteria for a query? I've tried to set it several times, but the I got only three results:
No results at all
All results
Or characters sorted only by the 1st trait.

[Wilkobeczka]

I've tried to add all of the criterion, but it seems like it's not going to work. One thing is that when I try to put a criterion like ">30" in "age", access says that it's invalid or too complex. The second problem is that no matter what I type in the criteria it shows me only one character or no characters.

SELECT Tabela1.Trait, TblMainCharacter.Race, TblMainCharacter.Age, TblMainCharacter.CharactersName, TblMainCharacter.Sex, TblMainCharacter.Job, TblMainCharacter.Loyalty, TblMainCharacter.CityFROM ((TblMainCharacter INNER JOIN TblCharactersWithPersonality ON TblMainCharacter.[Character] = TblCharactersWithPersonality.[Character]) INNER JOIN TblCharactersInChapters ON TblMainCharacter.[Character] = TblCharactersInChapters.[Character]) INNER JOIN Tabela1 ON TblMainCharacter.Character = Tabela1.Pole1WHERE (((Tabela1.Trait) Like "*" & [Formularze]![Formularz1]![Char2] & "*") AND ((TblMainCharacter.Race) Like "*" & [Formularze]![Formularz1]![Race]) AND ((TblMainCharacter.Age) Like "*" & [Formularze]![Formularz1]![Age] & "*") AND ((TblMainCharacter.CharactersName) Like "*" & [Formularze]![Formularz1]![Surname] & "*") AND ((TblMainCharacter.Sex) Like "*" & [Formularze]![Formularz1]![Sex] & "*") AND ((TblMainCharacter.Job) Like "*" & [Formularze]![Formularz1]![Job] & "*") AND ((TblMainCharacter.Loyalty) Like "*" & [Formularze]![Formularz1]![Loyalty] & "*") AND ((TblMainCharacter.City) Like "*" & [Formularze]![Formularz1]![City] & "*"));

I gave up when it comes to trying to arrange three fields in a search form with one field in a query, but even without it, access doesn't display what it should display. I'm a complete newbie and looking for a solution already took me a few hours, so any help would be a blessing .