Web Development

Ask something
Discussion Locked

Run a Query against multiple fields in Access 2007

0 helpful
4 replies

by powlboyjr 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello, everyone.
I'm trying to create a form to edit a "current inventory table." What I want to do is open a form with a subform embedded based on a query. This box shows all current assets in the table.

Right above this subform, I have a text box and a button labeled "Search." When Search is clicked, I was to run a query against all columns of data in the table and return the results in the subform (allowing editing of this returned data).

I tried adding criteria o "tblCurrentAssets.*" but Access states I cannot run criteria against "*". Please help, or offer any alternative solutions. Thank you very much in advance.

4 replies

by tony hopkinson

Sect * From My Table
Where (Column1 = "SearchString")
Or (Column2 = "SearchString")
....
Or (Column99 = "SErachString")

Awful isn't it, it will get ridiculously slow as you build up data as well. I'd be thinking about designing the table(s) differently.

by powlboyjr

I understand that it will take a lot of code later down the road. But this is my first db design, I'm more network security. Would you have any recommendations for table structure. Currently I have all fields in 1 table (ID (primary), make, model, serial, asset number, location, condition, purchase date, initial price, current value). Thanks for your help already.

by tony hopkinson

If you optimise it for this sort of search it will make other things harder.

For instance if you had and assets table like this
Unique_Nbr PropertyName PropertyValue
1 1 HP Pavillion
1 2 HP300
1 3 Bill's Office
2 1 Dell
2 2 Dimension
2 3 Spares Rack

Serach goes much easier, but you are going to have knock up a way of entering the data.

Another possibility which might appeal more would be to addan extra column to your current structure called Search and fill it with all searchable content.
e.g HP Pavillion HP300 Bill's Desk
then you could do

Select * from MyTable where [Search] Like "%" + Target + "%"

HtHs

by loubonnett

You're right about not being able to search against the all fields asterisk, however, you can create an alias of the concatenated search field names and search against that.
Of course, you'd have to use a LIKE search with the value wrapped in wildcard (a contains search). The SQL would look something like this:

SELECT *, [Field1] & [Field2] & [Field3] AS AllFields
FROM tblMyTable
WHERE [Field1] & [Field2] & [Field3]Like '*SearchValue*';

You can add as many search fields as you'd like to the alias.

Good luck with your project.

This discussion is closed to new replies.