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

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:39
Joined
May 21, 2018
Messages
8,463
Add these two calculated fields to your query to help with the odd even issue
Code:
MinStart: IIf([odd_start]<[Even_start],[Odd_start],[Even_start])
MaxEnd: IIf([odd_End]>[Even_End],[Odd_End],[Even_End])

Code:
  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.

Code:
  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.
 

Attachments

  • Test_MajP.accdb
    976 KB · Views: 84
Last edited:

TML15

Member
Local time
Today, 06:39
Joined
Apr 15, 2021
Messages
41
@MajP Perfect!! That's exactly what I was looking for!! I'm impressed how fast you create the code!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:39
Joined
Oct 29, 2018
Messages
21,357
@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

Member
Local time
Today, 06:39
Joined
Apr 15, 2021
Messages
41
@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

1619193437091.png


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

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:39
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 06:39
Joined
Apr 15, 2021
Messages
41
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

You've got your good things, and you've got mine.
Local time
Today, 06:39
Joined
May 21, 2018
Messages
8,463
No you need to write the query for the field used in the form and the two used in the code.
 

TML15

Member
Local time
Today, 06:39
Joined
Apr 15, 2021
Messages
41
Thanks @MajP & @theDBguy for all of your invaluable help and to the others that also tried to assist!
Your are amazing!
 

Users who are viewing this thread

Top Bottom