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

[TML15]

Okay @theDBguy , I pretty much copied what you suggested:

Did I take out too much?

 

[theDBguy]

Okay @theDBguy , I pretty much copied what you suggested:

View attachment 90989

Did I take out too much?

Looks like you misunderstood...

The SELECT * FROM part is supposed to go to the Record Source property of your form.

The rest needs to go in the Click event of your Search button.

 

[TML15]

Sorry @theDBguy ... I'm learning on the fly! Okay, I changed it to Me.RecordSource = "SELECT * FROM Ontario" and the form is working.
I just have a little fine turning to do on the street number search. I need the search to find any number in the range, so I modified the street number search like this:

But then I get this error:

and when I Debug, this is yellow:

 

[theDBguy]

Hi. Glad to hear you're making good progress. If the street number fields are numeric (not text fields), then you shouldn't use the wildcard (*) character (that only applies to strings/text fields).

 

[TML15]

I should remove the * from - strWhere = strWhere & " AND Odd_Start >= '*" & Me.txtStNum & "*' " ? How would I rewrite that?
Like this?

strWhere = strWhere & " AND Odd_Start >= '*" & Me.txtStNum & ""

 

[theDBguy]

I should remove the * from - strWhere = strWhere & " AND Odd_Start >= '*" & Me.txtStNum & "*' " ? How would I rewrite that?
Like this?

strWhere = strWhere & " AND Odd_Start >= '*" & Me.txtStNum & ""

Maybe more like this:

strWhere = strWhere & " AND Odd_Start>=" & Nz(Me.txtStNum,0)

 

[TML15]

Tried it and the form works but it doesn't give results for the street number. I tried many variations of the code but without any luck.
So, I tried another way. Keeping in mind that we are working with numbers at this part of the search.

This didn't work either but there are no error messages. So, I'm stumped. I feel I'm close but I can't figure it out.

 

[theDBguy]

Tried it and the form works but it doesn't give results for the street number. I tried many variations of the code but without any luck.
So, I tried another way. Keeping in mind that we are working with numbers at this part of the search.

View attachment 91014
View attachment 91015
This didn't work either but there are no error messages. So, I'm stumped. I feel I'm close but I can't figure it out.

Hi. I suppose the next step is for you to post a sample copy of your db with test data, so we can see how you made the search form.

 

[TML15]

@theDBguy I've made a sample database. Please let me know if I uploaded it correctly.

 

[arnelgp]

why not just use a Filter buttons.

 

[MajP]

Here is my version. I added a few simple functions to make things a lot easier.
Uses combos.
Got rid of likes when you mean =
Filter within range
Filter one or all controls
Filter on entry except number

Private Sub cmdSearchStreet_Click()
FilterForm
End Sub


Private Sub Show_All_Click()

Me.FilterOn = False
Dim ctrl As Access.Control
For Each ctrl In Me.Controls
   If ctrl.Tag = "filter" Then ctrl = Null
Next ctrl
End Sub

Public Function FilterForm()
  Dim fltrStreetName As String
  Dim fltrType As String
  Dim fltrDirection As String
  Dim fltrRange As String
  Dim fltrMun
  Dim formFilter As String
  Dim minStart As String
  Dim maxEnd As String

  fltrDirection = SqlText(Me.txtDir, "direction")
  fltrType = SqlText(Me.txtStType, "Street_Type")
  fltrStreetName = SqlText(Me.txtStreet, "Street_Name")
  fltrMun = SqlText(Me.txtMun, "Municipality")

  If Not (Me.txtStNum & "") = "" Then
    If Me.Odd_Start <= Me.Even_Start Then
      minStart = "Odd_Start"
    Else
      minStart = "Even_Start"
    End If
    If Me.Odd_End >= Me.Even_End Then
      maxEnd = "Odd_End"
    Else
      maxEnd = "Even_End"
    End If
     
    fltrRange = minStart & " <= " & Me.txtStNum & " AND " & maxEnd & " >= " & Me.txtStNum
    Debug.Print fltrRange
  End If

  formFilter = CombineFilters(fltrStreetName, fltrDirection, fltrType, fltrMun, fltrRange)
  Me.Filter = formFilter
  Me.FilterOn = True

End Function
Public Function SqlText(val As Variant, FieldName As String) As String
  If (val & "" <> "") Then
    SqlText = Replace(val, "'", "''")
    SqlText = FieldName & " = '" & SqlText & "'"
  End If
End Function
Public Function CombineFilters(ParamArray Filters() As Variant) As String
  Dim FilterCombiner As String
  Dim i As Integer
  Dim strOut As String
  FilterCombiner = " AND "
   ' FilterCombiner = " OR "

  For i = 0 To UBound(Filters)
    If Filters(i) <> "" Then
      If strOut = "" Then
        strOut = Filters(i)
      Else
        strOut = strOut & FilterCombiner & Filters(i)
      End If
    End If
  Next i
  CombineFilters = strOut
End Function

 

[MajP]

FYI. The version I provided allows you to easily add more filters
If you want province. Then the code gets added
dim fltrProv as string
fltrProv = SqlText(Me.txtProv, "Province")
Then add to the combiner
formFilter = CombineFilters(fltrStreetName, fltrDirection, fltrType, fltrMun, fltrRange, fltrProv)

 

[TML15]

why not just use a Filter buttons.

It is an interesting method that could work. My concerns are that it could be difficult when sorting through 60000+ records and also finding a street number within the address range. I'd like to test it out to see how easy or difficult it may be for the users.

 

[TML15]

FYI. The version I provided allows you to easily add more filters
If you want province. Then the code gets added
dim fltrProv as string
fltrProv = SqlText(Me.txtProv, "Province")
Then add to the combiner
formFilter = CombineFilters(fltrStreetName, fltrDirection, fltrType, fltrMun, fltrRange, fltrProv)

I don't mind it @MajP . I'm curious to know how it would do with a full 60000+ records where the drop downs could be quite long depending on how big the street is.

 

[arnelgp]

if you 60,000+ record, you can increase the "number of records to display"
go to Options->Current Database, scroll on the bottom part and type:

999999999 (that is 999 million records to display) on the combobox:

"don't display lists when more than this number of records..."

 

[MajP]

I do not care if you have a million records.
Direction will only be at most 20 N, NE, NW, E, SE
Type at most 30ish Pl, St, Ave,
Municipality. I do not know where this data is from, but I guess 50ish
I would have to personally test the speed. If it is only 60k and that is about how big it will get then I would likely do a Find as you type on my combo.
I would type El and get any street with El. Then type ELM and get any street with ELM.

However if you want that one to be a Like textbox that can be easily modified.

 

[MajP]

The issue with the textbox search is when do you want that to happen. The combos are nice because you can fire it after the selection.
With a textbox you may or may not want it to fire after every character change. This would depend on the number of records. So then you need a button or some way to fire the filter when you are done typing.

If you want a text box and a like filter then just add a function to return Like filters and change to that.

Public Function FilterForm()
  Dim fltrStreetName As String
  Dim fltrType As String
  Dim fltrDirection As String
  Dim fltrRange As String
  Dim fltrMun
  Dim formFilter As String
  Dim minStart As String
  Dim maxEnd As String

...
  fltrStreetName = SqlLike(Me.txtStreet, "Street_Name")
...

End Function

Public Function SqlLike(val As Variant, FieldName As String) As String
  If (val & "" <> "") Then
    SqlLike = Replace(val, "'", "''")
    SqlLike = FieldName & " Like '*" & SqlLike & "*'"
    Debug.Print SqlLike
  End If
End Function

However if performance allows you can set the above up on the change event of the textbox as well. So when you type "E" you immediately get all records with "E" and then "El" give only those with "El". But how you do this would depend on performance and if the back end is Access or some other RDMS.

 

[MajP]

Here is a 6k FAYT example and it is pretty instantaneous. This is not something you would do on a non Access BE. This is an example of a listbox, but you can have a combo do the same.

 

[TML15]

Here is a 6k FAYT example and it is pretty instantaneous. This is not something you would do on a non Access BE. This is an example of a listbox, but you can have a combo do the same.

I've seen these examples and I like them. It doesn't work for ranges however. If the range starts at 1 and ends at 5, it won't find 2,3 or 4.

I am intrigued by your Test_MajP zip file. You were able to have the street number filter within the range. This is what I'm trying to accomplish
with the zip file I attached this morning. If you were to add that functionality to my db, then I believe my issue is resolved.

 

[MajP]

Sorry that looks like it is working, but not really. I will send you a fix.