Tweak SQL query to clean up and narrow down results

[mafhobb]

Hi

I have the following SQL associated to a field's afterupdate event.

' Search Procedure
Me.lstsearch.RowSource = "Select [ContactID], [CustomerID], [FirstName], [LastName], [PostalCode], [CompanyName], Format([WorkPhone],'(###) ###-####'), Format([HomePhone],'(###) ###-####'),Format([MobilePhone],'(###) ###-####'),[EmailName]" & _
                        "From [Contacts]" & _
                        "Where [CustomerID] like '*" & Me.txtsearch & "*'" & _
                        "OR [FirstName] like '*" & Me.txtsearch & "*'" & _
                        "OR [LastName] like '*" & Me.txtsearch & "*'" & _
                        "OR [PostalCode] like '*" & Me.txtsearch & "*'" & _
                        "OR [CompanyName] like '*" & Me.txtsearch & "*'" & _
                        "OR [WorkPhone] like '*" & Me.txtsearch & "*'" & _
                        "OR [HomePhone] like '*" & Me.txtsearch & "*'" & _
                        "OR [MobilePhone] like '*" & Me.txtsearch & "*'" & _
                        "OR [EmailName] like '*" & Me.txtsearch & "*'"
Me.lstsearch.Requery

It works well, but now as the db grows, I need to tweak it as the number of results I get is too large.

First I would like to know how to order the results so they come out alphabetically (by Last name, for example)

Then, I will create a second text field in the form where a second text string is entered(let's call it txtsearch2) that basically takes the results yielded by the SQL above and narrows them down to those that also match this second text string.

Any suggestions?

Thanks

mafhobb

 

[mafhobb]

Well, adding "Order By" in the code was easy enough to organize the list by alphabetical order, however at the very top of the list I am getting a whole bunch of entries that have no entry for the last name. Any chance to get these to show up at the bottom of the list instead of the top?

mafhobb

 

[vbaInet]

To get the Nulls at the bottom, you need:

1. Substitute the Null for something
2. Order By this new substituted field followed by the Surname field.

By the way, are you doing this on a form? Do you know about the Filter and Order By properties of the form?

 

[mafhobb]

I realize that the form has orderby properties, but if I can, I prefer to have everything in the code, otherwise I find it easy to lose track of what does what and where it is happening....my brain is limited...

So what do you mean by "substitute Null for something". Does that mean that I have to go to the table and replace all the empty entries for the last name for a default value that would be listed last in alphabetical order? The orderby twice in the code?

 

[vbaInet]

Your brain is very scalable If you document what you've done you should be able to track where you are applying criteria. If you use the Filter property of the form it will be much easier to alter the criteria.

You will have two fields, one which is calculated and the other will be the Surname field. Try this:

Write this in a new column in your query

Not IsNull([Surname])

Under this field, sort by Ascending. Open the query in Datasheet view and see what you get.

 

[mafhobb]

I apologize in advance...it is not that I am not doing what you are suggesting, it is that I do not know how to do it...

I added

Not IsNull([Surname])

in the code and I got no results in my listbox. Then I changed it to

not isnull([lastname])

, which is the name of my field, and I got no results either.

So I removed this code and went to the form properties and added it to the "filter" property. This time I got results, but they are exactly the same I had originally...they are all mixed up. In other words adding this did not do anything.

 

[vbaInet]

If you notice in my last post I mentioned testing this in a query.

 

[mafhobb]

All right, so I have tested this in a simple query in datasheet view and what I have found is that adding this in a query colum (the "lastname" column) basically returns only the results that have a value in the field. Then I sort the field ascending and I get all the lastnames in alphabetical order.

Now, it is not my intention to remove the records that have no lastname, I just want to move them to the bottom while the rest of the records are listed alphabetically as I have them now.

How do I get the records with no lastname to show at the end?

mafhobb

 

[vbaInet]

The field displays -1 or 0, not your Surname. What you have done is put it under the criteria of Surname and that wasn't what I said.

I will repeat. Write Not IsNull([Surname]) in a NEW COLUMN, now I didn't say put it under the [Surname] field, I said put it in a new column. Then you sort in Ascending under this field, followed by a sort in Ascending of your Surname field. Two sorts.

 

[mafhobb]

OK. I see what you are trying to explain to me.

If I get this right, you are suggesting to have an additional column in my query that checks to see if the field lastname is null or not. If it is null then it assigns a value of -1 to that record if it is not null it assigns 0. Sorting ascending for that field returns the "0" first and "-1" second. This means that if after this first sort I then sort for the original lastname field, I then get the records ordered by lastname is the order I want and the null values at the end.


mafhobb

 

[vbaInet]

You're on the ball.

OK. I see what you are trying to explain to me.

If I get this right, you are suggesting to have an additional column in my query that checks to see if the field lastname is null or not. If it is null then it assigns a value of -1 to that record if it is not null it assigns 0.

Correct and Correct!

Sorting ascending for that field returns the "0" first and "-1" second. This means that if after this first sort I then sort for the original lastname field, I then get the records ordered by lastname is the order I want and the null values at the end.

So if it is Null it will return -1 and if it's not, it will return 0 but we've added NOT so that it swaps the values around, so Null now becomes 0 and the others are -1 and when we sort it - because -1 is less than 0 - Null will stay at the bottom.

Try it out.

 

[mafhobb]

Here is the code:

Me.lstsearch.RowSource = "Select [ContactID], [CustomerID], [FirstName], [LastName], [PostalCode], [CompanyName], Format([WorkPhone],'(###) ###-####'), Format([HomePhone],'(###) ###-####'),Format([MobilePhone],'(###) ###-####'),[EmailName]" & _
                        "From [Contacts]" & _
                        "Where [CustomerID] like '*" & Me.txtsearch & "*'" & _
                        "OR [FirstName] like '*" & Me.txtsearch & "*'" & _
                        "OR [LastName] like '*" & Me.txtsearch & "*'" & _
                        "OR [PostalCode] like '*" & Me.txtsearch & "*'" & _
                        "OR [CompanyName] like '*" & Me.txtsearch & "*'" & _
                        "OR [WorkPhone] like '*" & Me.txtsearch & "*'" & _
                        "OR [HomePhone] like '*" & Me.txtsearch & "*'" & _
                        "OR [MobilePhone] like '*" & Me.txtsearch & "*'" & _
                        "OR [EmailName] like '*" & Me.txtsearch & "*'" & _
                        "Order By Not IsNull([lastName]), [LastName], [FirstName]"
Me.lstsearch.Requery

...works like a charm!

mafhobb

 

[mafhobb]

Now on to the second request on this thread...

How do I modify this SQL so it uses a second text string to narrow down the results? I would basically need to query a query, right? Get the results for the 1st text string and then filter out those that do not have the second text string.

Is this a union query?

mafhobb

 

[vbaInet]

What do you mean by "use a second text string to narrow down the results"? Isn't this what the WHERE part is for? ... which you've already done.

 

[mafhobb]

No, the "where" basically compares all fields of interest against one single text string entered by the user, so the user can enter anything in that textbox and it will return results regardless of where that string in found. This works really well in most instances, except on some where the results returned are too many.

The idea now is to have two text boxes. If there is text on both, then the search needs to be done for one text string and then searching those results for the second text string will return fewer results.

Basically I want to find records that contain the first text string AND also the second text string somewhere in all the record's fields.

mafhobb

 

[vbaInet]

The idea now is to have two text boxes. If there is text on both, then the search needs to be done for one text string and then searching those results for the second text string will return fewer results.

It's a simple matter of validating the textboxes to see whether text has been entered.

* If the first textbox has text, create a WHERE clause
* If the first textbox doesn't have text, continue to check textbox2
* If the second textbox has text, create a WHERE clause (but if you already had a WHERE clause previously from textbox1, combine it with this WHERE clause
* If the first textbox doesn't have text, use the WHERE clause from textbox1 if it had one.

 

[mafhobb]

The validating part I can deal with easily. The question is more about the syntax of the Where clause in the Select statement. Can I have two Where clauses in the same select statement? What is the syntax to make sure that I get records that match the first AND the second text strings on any of their fields?

mafhobb

 

[vbaInet]

Aircode:

Const strSQL As String = "SELECT * FROM TableName"
Dim strWhere As String

If Len(txt1 & vbNullString) <> 0 Then
    strWhere = ... string here ...
End If

If Len(txt2 & vbNullString) <> 0 Then
    If Len(strWhere) <> 0 Then
        strWhere = ... where clause of txt1 + txt2 here ...
    Else
        strWhere = ... only txt2 where clause here ...
    End If
End If

... concatenate strSQL and strWhere here (you need to do a check) ...

 

[mafhobb]

I may not have explained myself well.

What I need is to search for a word in multiple fields which will give me a number of results and then search for another word within those results to narrow them down. It is not a text string with two words, but two independent words in the same or different fields

Mafhobb

 

[vbaInet]

It is not a text string with two words, but two independent words in the same or different fields

That's why it's called aircode... it's there to depict the logic and syntax without knowing the details of the fields or controls being used.

What I need is to search for a word in multiple fields which will give me a number of results and then search for another word within those results to narrow them down.

When you type into the second textbox, the text in the first textbox is still there isn't it?