how to pull off house numbers into a different column in 2007 access query

I want to be able to pull out with a query, a list of odd and then even addresses on particular streets, where the address field is a text field because it has numbers and letters. Ex) list of all odd house numbers on Main St and list of all even numbers on main st.
usernamecAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
Add a field with the following expression: Val([Address]) Mod 2
Then in it's criteria, put 0 for Even numbers and 1 for Odd.

Or you can use the following expression in your field: Iif(Val([Address]) Mod 2 = 0, "Even", "Odd")
Then just add a criteria for Even or Odd.

Ron
0
Rey Obrero (Capricorn1)Commented:
how are the addresses look like?

1234 Main st

are the names of street uniformly entered in the field ?
Main st.
Main St

1. first you need to make sure that street names are uniform.

2. create a column in the query to separate the number and street name


HouseNumber:Left([address],instr([address]," ")-1)
street:mid([address],instr([address]," ") +1)

to get the EVEN number use the Mod function

[HouseNumber] Mod 2   ' even number will have answer of zero (0) , one (1) for ODD numbers
0
usernamecAuthor Commented:
Rey Obrero,
I separated the house number from address and have one column for house number and one column for the street name. I am having trouble with the EXACT Syntax. In a query to pull out ONLY ODD numbers, EXACTLY what do I put in the criteria field. AND EXACTLY what do I put in a separate inquiry to pull out the EVEN numbers. My COLUMN NAME is "House Number". No underscore between "House" and "Number".
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

IrogSintaCommented:
Did you try my solution?  There's no need to separate the house number from the street name when you use the VAL function because VAL ignores any non-numeric characters after the number.

Ron
0
Rey Obrero (Capricorn1)Commented:
<There's no need to separate the house number> yes, this is true, if you are not going to a grouping on the name of the street.

for EVEN number

 select  [House Number], [House Number] Mod 2
from tableName
where ([House Number] Mod 2) = 0  


for ODD number

 select  [House Number], [House Number] Mod 2
from tableName
where ([House Number] Mod 2) = 1
0
usernamecAuthor Commented:
IrogSinta,
In the design view of my query I did the following and it does not work:

Field:     RESIDENCE ADDRESS        Val([RESIDENCE ADDRESS]) Mod 2
Table:    Registered Voters            
Sort:
Show:
Criteria:                                               0
Or:

Please tell me exactly what to put in what fields in the design view.
0
usernamecAuthor Commented:
Roy Obrero,
Do I put that in the FIELD or CRITERIA space in the design view??
0
Rey Obrero (Capricorn1)Commented:
in the design view of your query, go to SQL view then
copy this SQL statement  and paste

SELECT Left([RESIDENCE ADDRESS],InStr([RESIDENCE ADDRESS]," ")-1) AS [House Number], Mid([RESIDENCE ADDRESS],InStr([RESIDENCE ADDRESS]," ")+1) AS [Street Name]
FROM [Registered Voters]
WHERE (((Left([RESIDENCE ADDRESS],InStr([RESIDENCE ADDRESS]," ")-1) Mod 2)=0));

this will give you the EVEN address

to  get the ODD number, change  "=0"  with "= 1"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IrogSintaCommented:
@username, I misunderstood your original question.  Please follow Rey's solution.

Ron
0
Rey Obrero (Capricorn1)Commented:
the val() function can be useful if the address number is  immediately followed by a alphabet. i.e., 1234A main st
0
Rey Obrero (Capricorn1)Commented:
post at #a40793144 should resolve this problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.