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

x
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
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
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".
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
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
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.
Author Commented:
Roy Obrero,
Do I put that in the FIELD or CRITERIA space in the design view??
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"

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: