"Creating advanced searches using Code"

[James]

Hi,

I have two questions really,
1)How can i make a code that will alow the user to enter a number, say 2.1,
and the program will bring up the forms that have anything from 2.05 to
2.15 in that particualar field?
2) Also I have one search that I need to check multiple fields for the same
criteria and display all of the matches, for example, say i am looking for
people who own a particular pet, cats, but some people own a cat a dog and a
bird, cat may not be the first or second pet they list so i need to check all
three fields for the word cat.

any help would be nice I've been working at this for days

thanks,
James

 

[MacDermott]

1. This feels like an incomplete spec. What if the user enters 2? Does he
get values between 1.95 and 2.05? Or 1.5 and 2.5? Or an error message?

2. This kind of problem is often the result of a faulty data structure.
Instead of having separate fields in the same row for Pet1, Pet2, and Pet3
(what if the respondent has 4 pets? Or 14?), build a table with one field
for Pet (and probably at least one more for owner) and make multiple rows in
it. Your search will then become quite simple.

 

[James]

MacDermott,
1. If the user enters 2 I want the program to give the User all of the
forms that have the numbers between 1.95 to 2.05, this is a search that
involves a certain amount of somthing used, for example the amount of sugar
in a recipe. In this case the user would want all recipies that have close
to 2 grams of sugar, not neacessarily only 2 grams.

2. As for creating one row, is there any way to do it automaticaly, there
are alot of records already entered into the database, more than 2000, and I
would prefer not to have to enter them again.

Sorry this took a while, I used my work E-mail adress and cannot acess it at
home.

-James

 

[MacDermott]

OK, suppose you have a table with fields like this:
RecipeNumber
Ingredient
Amount

And a form named frmCriteria with 2 textboxes txtIngredient and txtAmount.
You can write a query based on this table, and use it when the form is open
and both textboxes are populated.
Include all 3 fields.
On the criteria line under Ingredient, put this:
=Forms!frmCriteria!txtIngredient
On the criteria line under Amount, put this:
Between Forms!frmCriteria!txtAmount-.05 And
Forms!frmCriteria!txtAmount+.05

To get your existing table into this form, you can write an append query.
Use your old table as the source and your new one as the destination.
You'll need a separate query for each column in your old table, but even
that's far better than re-entering all that data, isn't it?

 

[John Griffiths]

MacDermott,

1. If the user enters 2 I want the program to give the User all of the
forms that have the numbers between 1.95 to 2.05, this is a search that
involves a certain amount of somthing used, for example the amount of sugar
in a recipe. In this case the user would want all recipies that have close
to 2 grams of sugar, not neacessarily only 2 grams.

To get a 5% spread :-

SELECT <fields>
FROM <table>
WHERE Quantity IS NOT NULL
AND Quantity BETWEEN Quantity * .95 AND Quantity *1.05

You could also look up fuzzy logic on google to get some ideas.

Adjust to fit - John