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

  • Thread starter Thread starter Kate
  • Start date Start date
K

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?
 
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
 
Kate said:
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?
Click to expand...


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]
 
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


Marshall Barton said:
Kate said:
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?
Click to expand...


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]
Click to expand...
 
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
 
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
 
Kate said:
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?
Click to expand...


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)
 
when I do that Access error says, Syntax Error (comma) in query expression
[Val(Right([STREETNUM], 1) Mod 2]
--
kate


Marshall Barton said:
Kate said:
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?
Click to expand...


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)
Click to expand...
 
When I do that I get an error message that says, Undefined Funtion 'IsNumber'
in expression. What now?
 
Kate said:
when I do that Access error says, Syntax Error (comma) in query expression
[Val(Right([STREETNUM], 1) Mod 2]
Click to expand...


Sorry, that should have been:

=Val(Right([STREETNUM], 1) Mod 2
 
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
'====================================================
 
Back
Top