query that searches from multi fields on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I had originally posted this message on 12/04/04, but this is the first
chance I have had to get back to the newsgroups. This was the last reponse I
received:

Steven,

We must be missing each other's meaning here! What I suggested,
extrapolated across your 12 fields, should do exactly what you are asking.
But anyway, you have obviously tried it, whatever "doesn't work" might mean.
Could you give us a bit more detail of what you are doing, maybe with some
specific examples, and someone will be able to help.

-- Steve Schapel, Microsoft Access MVP

So to clarify, I have a form that that has 12 fields on it, some are
Item_Description, Part_Number, Manufacturer, Location...

What I am trying to do is write a query that will use the this form to
search the table for based on the criteria of each field that is filled in.
As an example, if I put in a description of bolt and left the remaining
fields blank I get all of the items that begin with bolt.

I can't get it to work if I put in bolt in description and Olhausen in the
Manufacturer. So how do I get it to look at multipule fields that have
criteria and ignore the ones that are blank.

Thanks

-Steve
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You iterate thru the controls that hold the data you want in your query.
Then generate an SQL string. Then run the resulting SQL. E.g.:

-- air code --

dim strSQL as string
dim strWhere as string

strSQL = "SELECT * FROM table_name "

If Not IsNull(Me!Manufacturer) Then
strWhere = strWhere & "Manufacturer = '" & me!Manufacturer & "'"
End If

If Not IsNull(me!Part_Number) then
If len(strWhere)>0 then strWhere = strWhere & " AND "
strWhere = strWhere & "Part_Number = " & me!Part_number
End If

.... etc. for other controls ...

' Create final SQL string
If len(strWhere)>0 then strSQL = strSQL & " WHERE " & strWhere

' run query or return recordset or ... whatever
' for example sake I'll get recordset
dim db as dao.database
dim rs as dao.recordset

set db = currentdb
set rs = db.openrecordset(strSQL)

' ... do whatever you want w/ recordset.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQbeJy4echKqOuFEgEQK+vgCcC3ICJTkKGAxMelnI38HOtXJj8l4AnjrB
/Xpjkr7G4PoF/bw44WucbSfW
=FG7i
-----END PGP SIGNATURE-----
 
Back
Top