Suitable way to search different recipe

[chezster1234]

hi my name is james and i am rather new to access, i have managed to create a simple mini database but now i am wanting to search for records that are inputted here is my code below i would appreciate any help on this matter.


what i want it to do is bring a text box up and search the form and if no results are there for your search bring up an error message something along the lines of sorry no record found.

SELECT Juice_db.ID, Juice_db.Flavour_Name, Juice_db.Flavouring_1, Juice_db.Flavouring_2, Juice_db.Flavouring_3, Juice_db.Flavouring_4, Juice_db.Flavouring_5, [Flavour_Name]+[Flavouring_1]+[Flavouring_2]+[Flavouring_3]+[Flavouring_4]+[Flavouring_5] AS NewCol
FROM Juice_db
WHERE ((([Flavour_Name]+[Flavouring_1]+[Flavouring_2]+[Flavouring_3]+[Flavouring_4]+[Flavouring_5]) Like "*"+[What Would You Like To Search]+"*"));

[Micron]

Your sql statement suggests to me that you have designed your db with a spreadsheet mentality/paradigm. Mostly it is the repetition of like things arranged in separate fields and your attempt to bring them together with mathematical operations (+). Apologies if this is not correct, but if you are as new to Access and relational databases as you say, I strongly suggest you research normalization and see if what you have resembles any level of normalization. Even so, you have + signs in places you should not, and seem to be using them where you should not. For example, with respect to flavoring, what is "apple" + "orange" equal to? Even if they are not words but are numbers, what flavor is 3+4? My point is that if you're trying to "string together" words, you don't use the + sign.
http://rogersaccessblog.blogspot.ca/...on-part-i.html
Otherwise, what you need is a parameter query (one that prompts you for input) or a query that looks at your form control for a value. Something like
SELECT * FROM Juice_db.ID WHERE Flavor_Name LIKE & "*" & Forms!frmYourForm.txtPrompt & "*";
Note: this is constructed as a sql statement like you might have in a query. It's not usable in vba code exactly as written.

[chezster1234]

ok thankyou for the reply i will lok via the link and thankyou for pointing me in the right direction