sort numbers in a query

E

emc

I am using a text field in a table for an address number, ie 4 Brown Street,
8 Brown Street, 10/12 Brown Street and so on up to 40 Brown Street.

When I try to sort in a query (which runs a form) 10/12 Brown Street is
first and 4 and 8 are last. I have tried the Val function but it doesn't
appear to work. I can't use a number field in the table as a lot of the
addresses have obliques or dashes.

Please help.
 
V

Van T. Dinh

Addresses are always hard to decide on the correct Table Structure. Whether
the Unit / Villa / Apartment number, Street number and Street name should be
stored in separate Fields or store as one Field depends on the usage. In
your case, it is clear that they should be stored in separate Fields as you
need to manipulate some of the components separately.

Rebecca Riordan discussed about this in her book "Designing Relational
Database Systems" if you are interested.
 
E

emc

Thanks.

Van T. Dinh said:
Addresses are always hard to decide on the correct Table Structure. Whether
the Unit / Villa / Apartment number, Street number and Street name should be
stored in separate Fields or store as one Field depends on the usage. In
your case, it is clear that they should be stored in separate Fields as you
need to manipulate some of the components separately.

Rebecca Riordan discussed about this in her book "Designing Relational
Database Systems" if you are interested.
Click to expand...
 
J

jahoobob

Numbers and text are sorted differently. Numbers sort reading right t
left and text sorts from left to right. Look at the way each align
when you view the table/query; text aligns left justified and number
align right justified.
I don't have time right now to determine how to do this but I woul
start by using the Instr() function to determine the the firs
occurence of a space in the address and then extracting the number par
(that ends at the space-1) into another column in a query. The "/" i
10/12 is another matter that can be resolved using Instr() also.
If I get a chance, I'll work on this. Good luck.
 
E

emc

Thank you for your suggestion.

It has become quite apparent that I am mere novice, however I will put your
good thoughts to use.

emc
 
Top