Re: sort numbers in a query


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


--
jahoobob
.

Relevant Pages

  • Re: change default numbering in access all versions
    ... If you want to sort a text field in number order then you have to force the text to become a number using the Val function or CDbl. ... Another alternative is to store all the text numbers with leading zeroes or force leading zeroes before you sort using the format function - ... i have other numeral columns without this type of confusion. ...
    (microsoft.public.access.queries)
  • Re: Group Header Sorting
    ... Use the Val function: add it as a computed field to the query, and then sort ... >I have a group header that I am using to sort data on a report. ...
    (microsoft.public.access.reports)
  • RE: Report in Certain Order
    ... Thanks OssieMac for your concise and prompt answer. ... This is so the numeric sort ... Set sort for this field to Ascending. ... Val function reads the text up to last numeric and creates a real number so ...
    (microsoft.public.access.gettingstarted)
  • Re: Help Needed With Format Of Query Expression
    ... they will BE text and sort as text. ... the CInt or Val function, or you can calculate them instead of using the ...  Microsoft Office Access MVP ...
    (comp.databases.ms-access)