Query or Report? Need to get addresses by odd or even numbers

[Kate]

I have a database with address fields that have street numbers and street
names then concantenated address field. I want to end up with a report that
gives me one side of a street (either Odd or Even Address, usually 4 digit
number) and with the street name in alphabetical order. How do I do this?

 

[John Spencer]

If you have the street numbers in one field and the names in another then you
can use a calculated field to decide if the number is odd or even.

Assuming fldStreetNumber is TEXT

Field:OddOrEven:IIF(IsNumber(fldStreetNumber),Val(fldStreetNumber) Mod 2,Null)

That expression will return 0 for even numbers, 1 for odd numbers and null for
values of fldStreetNumber that cannot be treated as numbers.

You can then sort by this expression. and the street name.

If you don't have separate fields then the process is much more difficult.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I have a database with address fields that have street numbers and street
names then concantenated address field. I want to end up with a report that
gives me one side of a street (either Odd or Even Address, usually 4 digit
number) and with the street name in alphabetical order. How do I do this?

How do you isolate the street number and street name?

Once that is done, then you can sort on:

[street name]
Val(Right([street number], 1) Mod 2
[street number]

 

[Kate]

Gentlemen, I really appreciate your help but I am such a novice you need to
tell me where this information gets placed. Mr. Barton, I typed what you
wrote (all of it) using my field name which is STREETNUM where you had
[street number] and STREETNAME where you had [street name]. I typed it in
the sort group line that said field. Access told me I was missing an
Operator. Am I doing this correctly? I was in the Report, not the Query.
Which one do I type this into and where? Thank you.
--
kate

I have a database with address fields that have street numbers and street
names then concantenated address field. I want to end up with a report that
gives me one side of a street (either Odd or Even Address, usually 4 digit
number) and with the street name in alphabetical order. How do I do this?

How do you isolate the street number and street name?

Once that is done, then you can sort on:

[street name]
Val(Right([street number], 1) Mod 2
[street number]

 

[Kate]

Mr. Spencer, thank you so much for your assistance but I don't know where I'm
supposed to type this. Do I open the Query or Report? Does your info go
into the Field line or the criteria line? Am I typing exactly what you typed
(like the fld?) and the my field name (STREETNUM) or do I type what you did?
I really need a step by step if you would be so kind. Thank you

 

[John Spencer]

IN your query Add a calculated field.
You do this by typing in one of the "cells" in the field row.
OddOrEven:IIF(IsNumber(StreetNum),Val(StreetNum) Mod 2,Null)

Now that you have this field available in your query, you can use it in the
report to sort by.
Open the report in design view
Select View: Sorting and Grouping from the menu
Set up sorting for the report using the dialog box. Hopefully you can figure
out how to do this step.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

Gentlemen, I really appreciate your help but I am such a novice you need to
tell me where this information gets placed. Mr. Barton, I typed what you
wrote (all of it) using my field name which is STREETNUM where you had
[street number] and STREETNAME where you had [street name]. I typed it in
the sort group line that said field. Access told me I was missing an
Operator. Am I doing this correctly? I was in the Report, not the Query.
Which one do I type this into and where?

You should enter those (using your field names) in the
Field/Expression column on three separate rows in the
Sorting and Grouping window. (Use the View menu to open
Sorting and Grouping)

 

[Kate]

when I do that Access error says, Syntax Error (comma) in query expression
[Val(Right([STREETNUM], 1) Mod 2]
--
kate

Gentlemen, I really appreciate your help but I am such a novice you need to
tell me where this information gets placed. Mr. Barton, I typed what you
wrote (all of it) using my field name which is STREETNUM where you had
[street number] and STREETNAME where you had [street name]. I typed it in
the sort group line that said field. Access told me I was missing an
Operator. Am I doing this correctly? I was in the Report, not the Query.
Which one do I type this into and where?

You should enter those (using your field names) in the
Field/Expression column on three separate rows in the
Sorting and Grouping window. (Use the View menu to open
Sorting and Grouping)

 

[Kate]

When I do that I get an error message that says, Undefined Funtion 'IsNumber'
in expression. What now?

 

[Marshall Barton]

when I do that Access error says, Syntax Error (comma) in query expression
[Val(Right([STREETNUM], 1) Mod 2]

Sorry, that should have been:

=Val(Right([STREETNUM], 1) Mod 2

 

[John Spencer]

DUH!!! Bang my head against the wall.

IsNumeric is correct
IsNumber is my mistake.

Sorry.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================