Solved Combined results in multi user search queries

[MajP]

Add these two calculated fields to your query to help with the odd even issue

MinStart: IIf([odd_start]<[Even_start],[Odd_start],[Even_start])
MaxEnd: IIf([odd_End]>[Even_End],[Odd_End],[Even_End])

  If Not (Me.txtStNum & "") = "" Then
       
    fltrRange = "MinStart <= " & Me.txtStNum & " AND maxEnd  >= " & Me.txtStNum
    Debug.Print fltrRange
  End If

Example
MinStart <= 123 AND maxEnd >= 123

FYI without adding the calculated field in the query the filter could be done. You street number has to be greater than the Odd_Start OR greater than the Even_Start. And it has to be less than the Odd_End or Less than the Even_End. Which is more complicated than doing the work in the query.

  fltrRange = "(Odd_Start <= " & Me.txtStNum & " OR Even_Start <= " & me.txtStNum & ") AND ODD_END >= " & Me.txtStNum & " OR EVEN_END >= " & Me.TxtStNum & ")"

Also that example had nothing to do with street number. That had to do with street names. You could do an FAYT on the name.

 

[TML15]

@MajP Perfect!! That's exactly what I was looking for!! I'm impressed how fast you create the code!

 

[theDBguy]

@MajP Perfect!! That's exactly what I was looking for!! I'm impressed how fast you create the code!

Hi. Congratulations! Glad to hear you got it sorted out.

This is what happens as soon as we get a sample db. You quickly get a solution without needing to have four pages of back and forth discussions.

Good luck with your project.

 

[TML15]

@MajP Perfect!! That's exactly what I was looking for!! I'm impressed how fast you create the code!

One more question @MajP ... When opening the form, a pop up appears looking for a parameter

I press OK and is goes away. I'm just wondering what is causing this prompt?

 

[MajP]

I do not get that on my end. I downloaded what I uploaded to double check, and no issue. There can be many culprits.
1. Check the forms Filter and Order By property and ensure nothing is in there.
2. Double check the forms query and ensure it works in design view.
3. Make sure ID is in the form's query.
If you cannot find the issue repost it. Did you modify once uploaded.

 

[TML15]

I do not get that on my end. I downloaded what I uploaded to double check, and no issue. There can be many culprits.
1. Check the forms Filter and Order By property and ensure nothing is in there.
2. Double check the forms query and ensure it works in design view.
3. Make sure ID is in the form's query.
If you cannot find the issue repost it. Did you modify once uploaded.

3. Make sure ID is in the form's query. - That's what it is. I substituted the sample data for the real data which does not have the ID column.

Is the ID essential to the query?

 

[MajP]

No you need to write the query for the field used in the form and the two used in the code.

 

[TML15]

Thanks @MajP & @theDBguy for all of your invaluable help and to the others that also tried to assist!
Your are amazing!