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

Commented:
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
Commented:
how are the addresses look like?

1234 Main st

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

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

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
Author 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
Commented:
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
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
Author Commented:
IrogSinta,
In the design view of my query I did the following and it does not work:

Table:    Registered Voters
Sort:
Show:
Criteria:                                               0
Or:

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

FROM [Registered Voters]

this will give you the EVEN address

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

Experts Exchange Solution brought to you by

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

Commented:

Ron
0
Commented:
the val() function can be useful if the address number is  immediately followed by a alphabet. i.e., 1234A main st
0
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.