Grateful for any advice on the syntax of a query where a user can input one, or several parameters to a query that will search a data-set.
Thanks
Grateful for any advice on the syntax of a query where a user can input one, or several parameters to a query that will search a data-set.
Thanks
if you use a form, user can pick items on the form like combo boxes, or list boxes, etc...
alter the query with the criteria....
Code:'---------------- sub btnOpenQry_click() '---------------- dim sWhere as string dim qdf as querydef sWhere = " where 1=1" if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'" if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'" if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'" set qdf = currentdb.querydefs("qsMyQry") qdf.sql = "Select * from table " & swhere qdf.close docmd.openquery qdf.name end sub
Thanks for your reply.
I don't think that I can use combo or list boxes as there are thousands of potential parameter values.
I'm quite new to Access, can the query you detailed above be built using the Query Design grid?
Thanks
Ranman's approach should work perfectly well.
However, if you want to use a query instead, look at the example I just posted in this similar thread where the OP also wants to use a query instead of code
https://www.accessforums.net/showthread.php?t=72021
It would help if you could/would be more specific in your question.....
Do you mean you want to search one field for one or more parameters (ex. search City for "Bath", "Birmingham", "Bradford", "Bristol", "Cambridge", "Canterbury", "Carlisle")
or you want to search many fields for one (respective) parameter? (ex LastName = "Smyth", FirstName = "Conrad", City = "Leeds")
Thanks for your interest Steve.
I want a query where a user can select any of a number of search criteria fields. These fields will be free text. Ideally I'd like to use the Query Design grid to build the query.
Not so sure about the "free text". Would you give examples of search fields and criteria?
Have you seen this" http://www.allenbrowne.com/ser-62.html
Code is here http://www.allenbrowne.com/ser-62code.html
Hi Steve,
Thanks for your reply. I think the code that you linked to is exactly what I need. But I'm unsure of how to tailor it to my needs.
In the example code:
'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
End If
If I had say 5 fields that could potentially be searched (FirstName, MainName, Town, County, Country) would I need to declare a criteria string for each one?
What does the "Me" mean in the code "Me.txtFilterMainName"?
Is each IF statement nested within the first IF statement i.e.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
If Not IsNull(Me.txtFilterTown) Then
strWhere1 = strWhere1 & "([Town] Like ""*" & Me.txtFilterTown & "*"")"
End If
End If
Thanks again.
Hi Colin,
No, I just want to give the user the flexibility to search on one or more fields, where they enter a single value for one or more fields
Thanks
Maybe we're at cross purposes but that's what my example is doing. The highlighting is just a 'bonus'.
The links to allen Browne's site provided by ssanfu should be exactly what you need
"ME" is a reference to the form... a "shortcut", if you will.
Instead of writing "Forms!Form1.txtFilterMainName", you can use "Me.txtFilterMainName". The "Me" syntax can only be used in a form module.
I use the "Me" syntax because it is easier to tell whether the value is from a form or a VBA variable.
-----------------------------------------------------------------------
Using code, you only need to create criteria if there are values in the controls.
-----------------------------------------------------------------------
No. Generally not.
Code:If Not IsNull(Me.txtFilterMainName) Then strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND " End If If Not IsNull(Me.txtFilterTown) Then strWhere1 = strWhere1 & "([Town] Like ""*" & Me.txtFilterTown & "*"")" End If
-----------------------------------------------------------------------
One other thing. I've had problems using a test like
If there is a space in the control, you get a false positive. So I've been usingCode:If Not IsNull(Me.txtFilterMainName) Then
From the inside out,Code:If Len(Trim(NZ(Me.txtFilterMainName,""))) > 0 Then
the NZ function converts NULLs to an empty string.
Then the Trim function removes leading and trailing spaces.
Lastly, the Len function checks to see if the count of characters is greater than 0.
-----------------------------------------------------------------------
Note that the code is enclosed with code tags. When you reply, in the reply menu, you see the "#". That is the button to add the code tags to indicate the code and to keep formatting.
You can click the code button (#) then paste in the code or you can paste the code in the reply, select the code, then click on the code button (#).
Thanks again Steve, you've been a great help.
Steve
Why do you suggest using
rather thanCode:If Len(Trim(NZ(Me.txtFilterMainName,""))) > 0 Then
The latter has never failed for meCode:If Nz(Me.txtFilterMainName,"") <>"" Then
@Colin
I'd had problems with code like
orCode:If Not IsNull(Me.txtFilterMainName) Then
because there was one or more spaces in the control.Code:If Not IsNull(Me.txtFilterMainName) Or (Me.txtFilterMainName = "") Then
So I started using the Len and Trim functions.
I've also seen it written
but I prefer to have the Trim function included to deal with any spaces...... (belts and suspenders/braces)Code:If Len(Me.txtFilterMainName & vbNullString) > 0 Then
Another variation using vbNullString instead of the NZ function:
Code:If Len(Trim(Me.txtFilterMainName & vbNullString)) > 0 Then