Need to create a search based on a specific field

[Julie]

Okay so I am really new at all of this and I will try to explain as best as I
can as I would appreciate the return of information in the same matter (dummy
terms). On my form I need to create a text box that allows someone to do a
search based on one of my fields in my table. I actually need 2
searches...one for each. I have a memo column and a account number column in
my table. I would like to put on my form a selection so they can type in a
part of or the entire account number and have it retrieve the information and
all the rest of the associated information with it based again on my table
data. I would also like the next search to be able to have them enter either
a partial name, or even an address and have it return anything in the table
that meets that criteria....because there will be information held in the
memo section that will need to be searched in case of a name being put in
there as well. I hope I make sense...it is hard when I know what I want and
can see it in my head but can't figure out how to make it happen. Thanks
again.

 

[Dorian]

The best way to approach this is to have a 'Find' screen in front of your
main screen. On this screen you enter search criteria and from this you build
the record source for your main screen. I do this in lots of applications and
it works very well. However, quite a lot of VBA coding is needed. You would
need to build a record source with something like SELECT ... FROM MyTable
WHERE AccountNumber LIKE '*123*' AND MyMemoField LIKE '*Find This*'

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Julie]

Well since I don't know code and as I said previously I need it explained in
"dummy language" I don't think that this would be a suitable solution for me
but thanks...Any other suggestions?

 

[Philip Herlihy]

Well since I don't know code and as I said previously I need it explained in
"dummy language" I don't think that this would be a suitable solution for me
but thanks...Any other suggestions?

Ok. VBA and everything that goes with it is a bit daunting if you're
new. Two suggestions:

1) Use a parameter query. Build a query which returns all the fields
you want. Then put "Like", followed by a string in square brackets, in
the "Criteria" row of the query builder under the field you want to
match. If your entry is:
Like ["Enter characters"]
.... then Access will prompt you for the value to replace "Enter
characters" when the query runs. Use asterisks (*) to match any
characters at that point, so you might want them at the beginning and
end of what you type into the prompt, or it will only match the whole field.

I've not used parameter queries that much but I suspect they may be
harder to use with numeric fields (where the number is _stored_ as a
number and not a string), in which case you'd need to replace the
relevant field in your query with a function which converts the contents
to a string, so that it can match the numeric text characters you type
at the prompt. Instead of having MyNumericField at the top of a column
in the query builder you change it to read:
MyNameForIt: Cstr([MyNumericField])
You may also need to use the NZ function to deal with null values:
MyNameForIt: Cstr(nz([MyNumericField],""))
.... which passes on a null string ("") where there is nothing in
MyNumericField. Sounds daunting, but if you fool around with these
things it'll make sense and you'll be much encouraged. Experiment with
a plain text field and a parameter query first, and then worry about
numeric fields if you have to.

You can build a simple form on top of a parameter query, and the prompt
will still "fire", and limit what you see in the form.

2) Use "filter by form". See Help about this. You can create a simple
form, then click the filter-by-form button and type a matching string
(remember to use asterisks where appropriate or you'll only get exact
matches) in one or more fields, the click the "apply filter" button.
Voila! No programming - it's built into Access. You (and your users?)
just need to know how to use it.

Try both. You'll use them again and again.

Phil, London