Form for querying database (1 Viewer)

[dakcg]

Hi, I am trying to put together a form so users could query the database for select info, like- customers that have *design* in their name. I figure I can create a query that would use their criteria easy enough, but I want them to be able to pick which field to search for the data in, like customer number, city, state, ect. Once again, I am pretty sure I can use a combo box with a selection of fields for them to search, but the only way I can think to do it is with one query built for each choice, with macros or code to say: if this choice then run this query. I am sure that would work, but I have to figure there is an easier way. Any thoughts?

DAK

 

[Fornatian]

You need to look at building the SQL query string in VBA.

If you write your queries in SQL already(without using the QBE grid) then you are almost there. Alternatively, look at the SQL for some queries you already have(you can change to SQL using the button in the top corner). To build whats known as a dynamic query you will need to build the string on the fly. If you are not au fait with building strings then you may be better suffering the overheads of numerous queries.

As an example of a simple string SQL statement, say your field is called txtWhereField and the criteria is called txtCrit your SQL statement might look like:

'set a variable to hold the string
Dim strSQL as String
'build the string
strSQL = "SELECT * FROM MyTable WHERE " & Me.txtWhereField & "='"& txtCrit & "';"
'run the query
Docmd.RunSQL strSQL

Hope that gives you some direction.

Ian

 

[dakcg]

Thanks Ian,

I can see where it is leading, I think, but, I still don't see where that will create the different results, maybe I am dense though!
I must admit that, while I can write minor queries in true SQL, I still depend on the wizards to do most of the work. But, I am trying to learn to use code instead. I thank you for your input, any other help would be welcome.
DAK