universal wrote:
Hello all,
Iam a complete beginner, so please be gentle.
We are programmers here, not wolves trying to deflower a virgin.
Im trying to (if
possible) create a form to be used as a "search" facility of another
form.
All users list their favourite foods:
dave eggs chocolate
steve cheese chocolate
tony beef chocolate
And so in my search I have a combobox of eggs, chocolate, cheese, and
beef.
Should eggs be selected I would like only dave to be shown on the next
form. Though if chocolate is selected, then all three need o be
shown.
So far I've got:
stLinkCriteria = "[food1]=" & " ' " & Me![chooser] & "'" And "[food2]="
& "'" & Me![chooser] & "'"
but this doesnt work, and is probably not the most elegant way of
doing this.
Any ideas?
Many thanks
Ed
It appears you have 2 fields; Food1 and Food2. In your criteria you are
using an AND. I really believe you mean OR. What you are asking is "Give
me all records in this table where the favorite food in field1 is
chocolate and the favoirite food is chocolate in food2" Since I don't
see any people that are choco-holics and eat chocolate only, the result
should be zero records returned.
I suggest you go to google
http://www.google.com or to the newsgroup
search engine at
http://groups.google.com and enter the advanced search
area. Then do a search on truth tables. When you new to programming
truth tables can be confusing. I'm sure you'll find some computer pros
that have spent some debugging time trying to figure out what is wrong
with the code and then finding out their and/or logic is off.
Here's a common mistake. 1 and 2 or 3. Did you really want something
where the value is 1 and in the second value either a 2 or 3. It
certainly can't be a 1 and 2 and a 3. It can't be both 1 and 2 or a 3.
To correct it may be 1 and (2 or 3)
Now to correct your problem.
Dim strWhere as string
If not isnull(MeChooser) then _
strWhere = "[Food1] = '" & Me.Chooser & :"' Or [Food2] = '" &
Me.Chooser & "'"
(I put the single quotes inside. Less &'s)
I'm not sure why you need 2 columns for the food. I'm sure you have a
reason for that. If you ever want to select multiple foods you would want
to consider the word In. Ex:
strWhere = "[Food1] In ("Eggs","Grits","Waffles")