Solved Combined results in multi user search queries (1 Viewer)

[MajP]

put a
debug.print strSearch before me.recordsource = strSearch
Post the sql string.
You understand this will not work unless every one of those controls is filled in. Is that what you want?

 

[TML15]

Here is the SQL string:

SELECT * from Ontario Where((street_name like "*mills*") and (municipality like "*barrie*") and (Street_Type like "**") and (Direction like "**") and Odd_Start>= and Odd_End<= and Even_Start>= and Even_End<=)

"You understand this will not work unless every one of those controls is filled in. Is that what you want?" - No, this is not what I want. What I need is for the user to be able to search any or some of these fields on the form and have it find results (if any) from the table. Have I configured the code incorrectly for this?

 

[MajP]

Sorry. I meant post the Resolved sql string. In other words the string that ends up in the immediate window. That will show what is not proper.

 

[TML15]

That is what I copied, but maybe I did it wrong?

 

[MajP]

Sorry. My fault. Is your plan to require all fields filled in? If not your code will need to be more robust. You can see the problems.

Odd_Start> = and Odd_End <= and Even_Start> = and Even_End <=

 

[theDBguy]

SELECT * from Ontario Where((street_name like "*mills*") and (municipality like "*barrie*") and (Street_Type like "**") and (Direction like "**") and Odd_Start>= and Odd_End<= and Even_Start>= and Even_End<=)

That doesn't look like you tried what I suggested last. Otherwise, this part: "Odd_Start>= and..." would have looked like this: "Odd_Start>=0 and..."

Please go back to post #17 and make sure you use that version of the code I gave you and then let us know.

 

[TML15]

Sorry @theDBguy , I was focused on fixing strNumber = Nz(Me.txtStNum, 0) that I missed copying the code.
So, the good news is that there isn't an error message. The bad news is that there are no search results.

 

[theDBguy]

So, the good news is that there isn't an error message. The bad news is that there are no search results.

That's because of what I and @MajP said earlier. What do you want to happen when the user doesn't fill in all your Textboxes on the Form for the search?

 

[TML15]

I'm looking for the search to bring results for what is filled in the textboxes. So, imagine a street address database that includes a range of
addresses like this in a table:

Odd Start / Odd End / Even Start / Even End / Street Name / Street Type / Direction / Municipality

1 / 9 / 2 / 10 / MAIN / ST / W / HAMILTON

If the user enters only 4, MAIN, HAMILTON in only the strNumber, street_name and municipality textboxes on the form, that
this example from the table above is displayed as search results but also any other results that match the criteria entered. So,
if in the table, 4 MAIN DR E, HAMILTON exists in the table, then I want that range from the table to also be displayed as search
results. As you can imagine, the more specific the criteria entered, the less results will appear and the less specific the criteria
entered, the more results will appear.

So, if the user doesn't fill in all the textboxes on the form for the search, then I want it to return all results for that field. If no
criteria are entered, then the entire table should appear in the search results.

I hope I explained that clearly.

Thanks!!

 

[TML15]

I should also add that I tried searches with all textboxes filled and also did not get the search results desired.

 

[theDBguy]

I should also add that I tried searches with all textboxes filled and also did not get the search results desired.

Can you please elaborate on the Odd Start/End and Even Start/End parts? Does the table contain regular street number address? For example, 378 or 12647 or 555? Actually, can you show us some actual sample data?

 

[TML15]

Okay @theDBguy , I think will work without showing customer data. The table does contain regular street number addresses. Here is a sample:

 

[theDBguy]

Okay @theDBguy , I think will work without showing customer data. The table does contain regular street number addresses. Here is a sample:

View attachment 90979

Hi. Thanks for indulging my request. I think I can work with that. However, just wanted to ask a small clarification. What is the actual street address for the first BAYLY shown in your image? Is it 11192120 BAYLY ST?

 

[TML15]

Perhaps that is where this is confusing. These are address ranges, not an actual address. Potentially, there could be 120 addresses in that first range. Make sense?

 

[theDBguy]

Perhaps that is where this is confusing. These are address ranges, not an actual address. Potentially, there could be 120 addresses in that first range. Make sense?

I guess so. I was just wondering why you had an Odd and Even part. Couldn't you simply have a Low/High or Min/Max (two columns instead of four) instead?

 

[TML15]

I would like that, it would be simpler. Especially since there are 60000+ records in this table. Unfortunately, it is a table I download from a vendor that we require to be able to search for either a specific address or a range of addresses.

 

[theDBguy]

I would like that, it would be simpler. Especially since there are 60000+ records in this table. Unfortunately, it is a table I download from a vendor that we require to be able to search for either a specific address or a range of addresses.

No worries. It was just confusing, at first glance, when you're not familiar with the whole setup. Be back with a suggestion in a minute.

 

[theDBguy]

This search I'm creating is to find an address or a range of addresses based on a table of addresses. So, as you suggested, I tried writing code and got this far:

Dim strsearch As String
Dim strText As String

strText = Me.txtStreet.Value
strText1 = Me.txtMun.Value
strText2 = Me.txtStType.Value
strText3 = Me.txtDir.Value

strsearch = "SELECT * from Ontario where ((street_name like ""*" & strText & "*"")and(municipality like ""*" & strText1 & "*"")and(Street_Type like ""*" & strText2 & "*"")and(Direction like ""*" & strText3 & "*""))"

Me.RecordSource = strsearch

Hi. The way I might do this is simply set the form to have the following record source:

SELECT * FROM Ontario

Then, the code for the search button might go something like this:

Dim strWhere As String

strWhere = " 1 = 1 "

'add street
If Me.txtStreet > "" Then
    strWhere = strWhere & " AND Street_Name Like '*" & Me.txtStreet & "*' "
End If

'add municipality
If Me.txtMun > "" Then
strWhere = strWhere & " AND Municipality Like '*" & Me.txtMun & "*' "
End If

'add street type
If Me.txtStType > "" Then
    strWhere = strWhere & " AND Street_Type Like '*" & Me.txtStType & "*' "
End If

'You get the idea... just add the rest of the filters the same way. Then... at the very bottom...

Me.Filter = strWhere
Me.FilterOn = True

End Sub

Hope that helps...

 

[TML15]

That seems much simpler @theDBguy. Should there be something in front of SELECT * FROM Ontario? I'm getting a "compile error : expected case" message.

 

[theDBguy]

That seems much simpler @theDBguy. Should there be something in front of SELECT * FROM Ontario? I'm getting a "compile error : expected case" message.

You'll have to show us your new code to help us figure out why you're getting that error.