SELECT query on multiple fields 1

[Anesthaesia]

I am trying to create a search for a table in a database. The table has about 10 fields. What's the easiest way to create a SELECT query for all these fields?

At the moment I am using :
.."SELECT * FROM tbl WHERE field1 OR field2 OR field3 (etc.) LIKE ""%" & me.text & "%""", cn...

 

[chadt]

I dont get it....do you want to only pull back the 10 fields or do you want to place criteria against any of the 10 fields.

 

[uberskunk]

I reckon using the following code might be useful to you.

dim sSQL as string
sSQL = "SELECT * FROM tbl WHERE "
For i = 0 to recordset.fields.count - 1
sSQL = sSQL & recordset.fields(i).value & " LIKE '%" & me.text & "%' OR "
Next i

sSQL = left(sSQL, len(sSQL) - 4)


My appologies if this code will need a little work, but I think this is right.

Hope it helps,
Mike

 

[chadt]

this code will through errors.

 

[Anesthaesia]

Thanks uberskunk...

That looks like what I need. I'll give it a try.

chadt:
I want to place the same criteria against all of the 10 fields.. I only realised after posting that my example was incorrect anyway

 

[Anesthaesia]

WORKS GREAT!

Only thing I had to change was :
recordset.fields(i).value
replace with :
recordset.fields.item(i).name

This returns the field name, whereas the one above returns the field value.

Thanks...

 

[uberskunk]

Oh yeh, my mistake sorry I was rushing when I wrote that.

I believe you can also remove the .item and just use
recordset.fields(i).name

But anyway, glad I could help some,
Mike