Variables in query

[Dryseals]

I have a table in a db that is going to be shared by multiple users, each will have different criterea they will want to search by and none are familiar with Access. I have about 12 text boxes and 6 check boxes on a form that I would like them to set the search criterea with, click a button and let the code build the SQL.

I have built small ones using "if" statements, building a string statement and then opening a recordset but thought there must be a better way to do this.
Any help or ideas would be great.

 

[kjv1611]

Can you not have your Query statement (assuming you are creating queries) do something like this?

strQuery = normal stuff here..
      WHERE [name] = txtName AND [address] = txtaddress
      ...and so on

I didn't put a lot of detail, but hopefully you get my idea? What I am thinking is that you could put a button, or use some _AfterUpdate() event for building the query based on the data in the fields on your form. So for 6 fields, you would just have 5 AND's in the WHERE part of your SQL string. Then you can just create set the string variable equal to the SQL statement, and create the query using the string variable you created for the SQL string..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

Can you not have your Query statement (assuming you are creating queries) do something like this?

strQuery = normal stuff here..
      "WHERE [name] = " & txtName AND [address] = " & txtaddress
      ...and so on

I didn't put a lot of detail, but hopefully you get my idea? What I am thinking is that you could put a button, or use some _AfterUpdate() event for building the query based on the data in the fields on your form. So for 6 fields, you would just have 5 AND's in the WHERE part of your SQL string. Then you can just create set the string variable equal to the SQL statement, and create the query using the string variable you created for the SQL string..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV