Search results

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

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.

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?

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

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

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 /...

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.

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

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

Hi @theDBguy ! Yes, I did and had the same error message.

Sorry @theDBguy , I know it's hard to figure out online. Here is all the code: Dim strsearch As String Dim strText As String Dim strText1 As String Dim strText2 As String Dim strText3 As String Dim strNumber As Integer strText = Nz(Me.txtStreet.Value) strText1 = Nz(Me.txtMun.Value) strText2 =...

@theDBguy I really appreciate the effort!! Unfortunately, we have the same result as before with error 3075 & with Me.RecordSource = strsearch being highlighted yellow

Sorry...good assumption! As it is for an address number, it is a numeric value. I am now getting this error: with Me.RecordSource = strsearch being highlighted yellow when I debug. What do you think @theDBguy ?

Okay, so this is what I have... strsearch = "SELECT * from Ontario Where((street_name like ""*" & strText & "*"")and(municipality like ""*" & strText1 & "*"")and(Street_Type like ""*" & strText2 & "*"")and(Direction like ""*" & strText3 & "*"")and Odd_Start>=Me.txtStNum and Odd_End<=Me.txtStNum...

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 =...

As I am learning Access on the go, I admit my coding skills are not great and that Google & YouTube have been helpful. So, I am grateful for your advice and will look into how to implement it. Is there a website you might recommend that may help with coding this?

The BE is on a shared drive (for work).

The users are using a FE that was shared from my OneDrive and downloaded to their desktop.

I should also add that the tables being searched are being updated daily with new / updated data and are linked tables.

I've created a split db with the be on a shared server and fe on each users desktop. I have tables for the users to search using criteria they input into the form. When they press a 'search' button, it runs the query against the tables and finds the data based on their criteria and only...