Selecting specific records based on low/high street address fields.

[DantheDbaseMan]

I have a table that consists of the following fields:<br>
Low Address Range Data example: 300<br>
High Address Range Data example: 900<br>
Street Name Data example: Main <br>
Street Suffix Data example: St<br>
ZipCode Data example: 75228<br>
City Data example: Dallas<br>
<br>
I want the user to type in the address information for zip code look up. There are over 1 million records in this database. How can I search the street number when the low and high range are in two separate fields? How would one write sql or querie for this?<br>
<br>
*This is a Texas only table.<br>
<br>
<br>
<br>
<br>

 

[JimHorton]

I&quot;m not sure if I understand what you're looking for here--let me know if this is on the right track. <br>
The user's typing into search fields, say, txtAdrNumber and txtStreet, and you want to know the zipcode from this table with zipcodes and low/hi ranges, try this:<br>
<br>
&quot;Select ZipCode From tblWhatever where LowRange &lt;= &quot; & txtAdrNumber & &quot; AND HiRange &gt;= &quot; & txtAdrNumber & &quot; And StreetName Like &quot;&quot;&quot; & txtStreet & &quot;*&quot;&quot;&quot;<br>
<br>
--Jim<br>

 

[elizabeth]

Jim, I would imagine you'd need an exact match of Street Name, Street Suffix, and City rather than a like, as you could have Cherry St, Cherry Blossom St, or Cherry St in 2 different cities. Building on your example,<br>
<br>
&quot;Select ZipCode From tblWhatever<br>
where LowRange &lt;= &quot; & txtAdrNumber & &quot; AND HiRange &gt;= &quot; & txtAdrNumber & &quot; And StreetName = &quot;&quot;&quot; & txtStreet & &quot;&quot;&quot;<br>
And StreetSuffix = &quot;&quot;&quot; & txtStreetSuffix & &quot;&quot;&quot;<br>
And City = &quot;&quot;&quot; & txtCity & &quot;&quot;&quot;&quot;<br>
<br>
Do I have the format correct? I'm bad with the quotes, etc.

 

[JimHorton]

elizabeth,<br>
That looks right...I guess he would need the exact match, but one typo (!) and no records...I've done some work doing NCOA for existing db's and that is tough to get right, but once it's standardized, then things like these searchess go smooth!<br>
--Jim

 

[elizabeth]

Good point. Sounds like combo boxes for each fields would be useful to avoid data entry errors, and starting with city to narrow the selection criteria.