Address Lookup

[pvsmith5]

I posted a question like this a couple of weeks ago, but I still can't get it to work.

Let me do a better job describing what I need. I have a table full of street segments and addresses.

Street Name Start Address End Address
MAIN 1000 2000
MAIN 1001 1999

This is for the left and right side of a street. I want to build a query that will permit someone to enter and address, say 1020 Main and get the first record. So how do I go about searching both the Start Address and End Address fields at the same time with a passed parameter?

I don't think a union query will work--this is all in one table. It is a non-equi join. Can anybody give me a hint at how to do this?

 

[PHV]

Something like this ?
SELECT [Street Name], [Start Address], [End Address]
FROM yourTable
WHERE [Street Name] = [Enter street name]
AND [Enter house number] Between [Start Address] And [End Address]
AND ([Start Address] Mod 2) = ([Enter house number] Mod 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pvsmith5]

I'll sure give it a try. Thank you very much for taking the time to try to help.

 

[pvsmith5]

What is the purpose of the AND ([Start Address] Mod 2) = ([Enter house number] Mod 2)

I can't get the SQL to take this without a Syntax error.

 

[PHV]

This is to choose Left or Right side of the street.
Which version of access ?
Which SQL engine ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pvsmith5]

Access 2003
SQL is Jet 4.0.

 

[PHV]

So, should work.
Can you please post your real actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pvsmith5]

Here's what I have. The syntax error comes right at the beginning of the first Mod 2.


SELECT All_Roads_All_Schools.NAME, All_Roads_All_Schools.START_ADD, All_Roads_All_Schools.END_ADD
FROM All_Roads_All_Schools
WHERE NAME=[ENTER STREET NAME] AND [ENTER HOUSE NUMBER] BETWEEN [START_ADD] AND [END_ADD] AND ([START_ADD] Mod 2)=([ENTER HOUSE NUMBER] Mod 2);

 

[PHV]

Are START_ADD and END_ADD defined as numeric ?
You may try this:
PARAMETERS [ENTER STREET NAME] CHAR, [ENTER HOUSE NUMBER] INTEGER;
SELECT NAME, START_ADD, END_ADD
FROM All_Roads_All_Schools
WHERE NAME=[ENTER STREET NAME]
AND ([ENTER HOUSE NUMBER] Between START_ADD And END_ADD) AND ((START_ADD Mod 2) = ([ENTER HOUSE NUMBER] Mod 2));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pvsmith5]

START_ADD and END_ADD are both numeric.

The code doesn't pull back any records when it should. Like I said, this part works great:

Between [Start Address] And [End Address]

and it returns records.

But the Mod 2 makes it blow up, or just not return anything.

So do I have to have the Mod2 part?