Query Criteria to be a flexible combination of user inputs

[elliem19]

Hi all,

I have a database of 600+ records containing 12 columns of data per client, things like Name, Contact Number, Email, Postal Code, How they heard of us, Buyer profile etc.

I want users of the database to be able to 'build their own query' using an unbound form (with the 12 data fields) that prompts the criteria for the records they wish to view. For example, a user may be interested in seeing all 'Owners' that are in the Postal code '1234', or the clients from the suburb 'Sydney' that heard of us through 'Word of Mouth', (plus possibly even more specific search criteria). The idea is that the form allows them to choose many flexible combinations of criteria.

What I need is for the query to leave the criteria blank for any data/field not specifically requested by the user. Or in other words, for example, If the user leaves the 'Client Name' blank on the form, don't exclude all records with Non-Blank 'Client Name' from the query results...rather it should just ignore this as a criteria.

At the moment I'm getting zero query results unless I fill in every field on the form and it matches a record exactly.

I've searched and searched for solutions, and I'm thinking I may get lucky with some kind of IF statement within the SQL of the query. Something which sets the criteria of a column to match the input of the form, IF it wasn't left blank by the user.

Either that or some kind of VBA code that customises the query criteria based on how many of the fields on the form were given criteria.

Bonus Brownie Points: If on certain Memo fields, the inputted string from the user just has to be contained within the memo, rather than match it completely.

Thanks for any help

 

[MarkK]

Hey there and welcome to the forum.

What you need to do is write the WHERE clause of the SQL dynamically, so you check if a particular textbox has search text in it, if not, ignore, if so, "OR" that text in to the WHERE clause for the intended field. So a WHERE clause looks like ..

WHERE Field1 = 12 OR Field2 = 'Steve' OR Field3 LIKE '*brownie points*'

... so there's how you do the brownie points too.

You can trigger it with a search button, and then you just check all the controls that might have text in them, a loop you can control with an array, so imagine your search controls are named the same as your fields, except prefixed with tb ...

Dim vMySearchControls as Variant
vMySearchControls = Array(Me.tbField1, Me.tbField2, Me.tbField3)

... and then you can loop thru those controls ...

dim tmp as string
dim var
for each var in vMySearchControls
   if Nz(var.value, "") <> "" then    [COLOR="Green"] 'there is data here[/COLOR]
      tmp = tmp & "OR " & mid(var.name, 3) & " = " & var.value & " "
   end if
next
if tmp <> "" then tmp = mid(tmp, 4)   [COLOR="Green"] 'drop leading OR [/COLOR]
if tmp <> "" then tmp = "WHERE " & tmp [COLOR="Green"]'your finished WHERE clause, or nothing[/COLOR]

Which you can then merge into a full SQL statement ...

dim sql as string
sql = "SELECT * FROM Table " & tmp

Do you see what's happening there?
Lemme know,

 

[MarkK]

And maybe you want to AND them together, not OR.

 

[Brianwarnock]

This is a pretty standard request and has been answered many times , it is just finding the threads that is difficult.
It is best coded in the SQL. View as the design view creates a monster when saved by splitting the criteria .

The concept is
Where
FieldA = forms!searchform!boxA or forms!searchform!boxA is null
And fieldB = forms. Etc
And fieldC = etc

BoxA can be a textbox or a combo

You can use any combination of criteria and if all are left empty you select all records.

Brian

 

[elliem19]

Aha! Such speedy responses and so deliciously simple!

Thanks very much to you both, Thank you thank you thank you!!

So far I've had success with Brian's easier SQL approach (still relatively new to Access, so baby steps for me) - this is what I ended up with in case other users weren't sure how to put it all into the correct syntax with brackets etc;

WHERE
And ((Contacts.[Client Name])=forms![General Query]![Client Name] Or forms![General Query]![Client Name] Is Null)
And ((Contacts.[Contact Number])=forms![General Query]![Contact Number] Or forms![General Query]![Contact Number] Is Null) ..... etc....

My next step would be to attempt the 'Brownie Points' part in getting certain fields to accept similar, not exact, strings. Any advice?

In the meantime I'm going to give LagBolt's solution a go as well, and let you know how I go.


Extra Extra brownie points, if I wanted to get really sophisticated and allow the user to enter multiple entries into a certain field, for example; "How many Buyers are there in the 1234 or 5678 postal code?" is there some special method of entry, say separated by a semi colon ';' that would allow SQL to recognise it as an 'OR' statement?

 

[elliem19]

Nope, it would appear I am crashing and burning with this other method. I'm vaguely familiar with loops, but Arrays are out of my league.

Are these bits of code VBA or SQL? My initial thought was VBA but now I'm not so sure.