Sorting by Text field of numbers

I am using Access 2003.
I have created a database to help me files by "Lot#'s".  Our lot numbers for 
one of our products is all over the place so I created the Lot # field as a 
text field.   Ex:  02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02.  
Why does access put - 1000272925 before 10-06-02-03-05?  Does this look right 
to anyone?  

My thinking is that "10" comes before "1000272925".  What affect does the 
"-" have in sorting the numbers.  Would I be better off to put a space 
instead of the "-"?  Can anyone help me with this problem?

Aurora
0
Utf
1/20/2010 5:14:06 PM
access.reports 4434 articles. 0 followers. Follow

2 Replies
1496 Views

Similar Articles

[PageSpeed] 33

On 20.01.2010 18:14, Aurora wrote:
> I am using Access 2003.
> I have created a database to help me files by "Lot#'s".  Our lot numbers for
> one of our products is all over the place so I created the Lot # field as a
> text field.   Ex:  02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02.
> Why does access put - 1000272925 before 10-06-02-03-05?  Does this look right
> to anyone?
Yes, because the hyphen is in the ASCII order after the numbers.

You may try using Replace() in your order criteria:

ORDER BY Replace([Lot#'s], "-", "")

You may need some left padding also, e.g.

ORDER BY
   Right("0000000000000000" & Trim(Replace([Lot#'s], "-", "")), 16)


mfG
--> stefan <--
0
Stefan
1/20/2010 5:30:25 PM
Aurora wrote:

>I am using Access 2003.
>I have created a database to help me files by "Lot#'s".  Our lot numbers for 
>one of our products is all over the place so I created the Lot # field as a 
>text field.   Ex:  02-01-01, 10000272925, 10-06-02-04-06 and 1503011 A1-02.  
>Why does access put - 1000272925 before 10-06-02-03-05?  Does this look right 
>to anyone?  
>
>My thinking is that "10" comes before "1000272925".  What affect does the 
>"-" have in sorting the numbers.  Would I be better off to put a space 
>instead of the "-"?  Can anyone help me with this problem?


Those are NOT numbers, they are text strings that are being
sorted in the usual dictionary order.  If you want them
sorted as if they were numbers, you will have to convert
them to actual numbers by removing the non digits and using
a conversion function (Val, CLng, ?) to get actual numbers
for sorting.

-- 
Marsh
MVP [MS Access]
0
Marshall
1/20/2010 6:09:29 PM
Reply:

Similar Artilces:

Counting number of dates in a column earlier than today
Hi, Please can someone advise me on the formula I require for this problem. I would like to calculate the number of dates in a specified column, that equal to the current date or the date has passed. Any suggestion will be greatly appreciated Hi, Try =COUNTIF(G:G,"<="&TODAY()) Will return the number of dates in column G that are =< today "dnmnas" wrote: > Hi, > Please can someone advise me on the formula I require for this problem. > > I would like to calculate the number of dates in a specified column, that > equal to the current date or th...

Running Access Query Returns Incorrect Number of Records
I am running some code which loops through and runs the queries in an Access Database. The count of the records is then stored in a Worksheet. For most of the queries, the figure stored in the Worksheet matches the number of records if you run the query in Access. But for certain queries, the count of the records does not match the number of records when the query is run in Access. If I change the query to a make table query and then base another query on that table, the results are correct. Can anybody suggest running the query from Excel would results in a different number of records bein...

Sort Worksheets #2
How do I sort sheets in a workbook, please? Thanks! Check out http://cpearson.com/excel/sortws.htm In article <e02c01c43c07$5afeeb70$a601280a@phx.gbl>, "Manhar" <anonymous@discussions.microsoft.com> wrote: > How do I sort sheets in a workbook, please? Thanks! Hi see http://www.cpearson.com/excel/sortws.htm >-----Original Message----- >How do I sort sheets in a workbook, please? Thanks! >. > ...

Hotkeys for text & background coloring
Hi. Does anyone know the hotkeys for text coloring (font color) & background coloring (fill color)? Eg: First you need to select the font color & fill color in the related toolbar incons. - When I highlight "I am great", then press "Ctrl+O" for example, the text will be colored as what it's displayed in the "font color" toolbr icon. - The simliar way applies to background coloring If the above is not available, any workaround is still apreciated. -- Additional information: - I'm using Office XP - I'm using Windows XP You could use a sma...

Can't display all text in cell
I'm trying to display text in a cell. I've got Wrap Text on, and I hav several paragraphs of text. All of it appears in the formula bar, bu not in the cell displayed on the screen. And yes, I increased the ro height, but it still doesn't show. Any ideas? Thank -- Message posted from http://www.ExcelForum.com I am having the same problem. Apparantly there is a 1024 character limitation per cel in excel. The only advice I have received so far, which does work, is to press alt+enter to create a line break. If you find another work around, I would be interested. Hi besid...

Does anyone use "location" field in Outlook?
After all these years, I just noticed when I go to make an appointment in Outlook (2003version, with BCM), that there's a second field after "subject" which would seem to be the one everbody mostly uses. The appointment fields are: 1. SUBJECT: (example: "Conference call with Joe and Mary) 2. LOCATION: (does anyone use this?) Is the idea to put something like "Annapolis, MD" meaning maybe a meeting there or something? I know it's a hokey question, but wondered about some examples of how other people are using it. Thanks, G Standard following us...

Empty From: field
Hi. We use a exchange 2003 server and I have notice that my outlook 2003 client behaves strange if the From tag in the header looks like this; From: �� <sender@domain.com> I�ve hooked up Thunderbird and it shows the From field as I think it should, instead of a blank from field it shows sender@domain.com If the header looks like this; From: sender@domain.com or like this; From: �name� <sender@domain.com> it works just fine. The problem is the first example� Any ideas? Cheers Jonas Jonas <barre@gargamel.nu> wrote: > We use a exchange 2003 server and I have notice th...

counting same text from different cells
Hi I was Trying to count text such as "p" for pass from different cell in excel but i am not sure what is the formula for that. It will be really helpful if u could send me the solution thank you om Hi =COUNTIF(A1:A100,"p") -- Regards Frank Kabel Frankfurt, Germany "om" <om@discussions.microsoft.com> schrieb im Newsbeitrag news:96B25715-6B88-40F8-8360-9602A05E3B30@microsoft.com... > Hi I was Trying to count text such as "p" for pass from different cell in > excel but i am not sure what is the formula for that. > > It will be rea...

Like numbers in a field
I am working with a 25,000 row parts issue listing that was created by parts issue date. There are many repeat numbers in the part number column as the same part would have been issued on several different dates. I would like to be able to combine the like part numbers to know how many parts were issued over the course of all of the dates. Any ideas??? The Column headers are Part Number, Quantity, Unit Price, Total Price and Transaction Date. Create a new query based on your parts issue listiong table. Drag the Part number field into the query grid twice. Click on the Total...

random sequence of numbers
How do I generate a random sequence of numbers? e.g. simulating pulling the numbers 1 through 25 out of a hat. One way: Put the numbers 1 through 25 in A1:A25. Put this in B1 and copy down to B25: =RAND() Select any cell in the A1:B25 and choose Data/Sort. Sort on column B (ascending or descending, doesn't matter) with no headers. If you need to save the sequence copy A1:A25 to a different location. Since RAND() will recalculate after the sort, you can get another random sequence by choosing Data/Sort again. In article <1eb301c38762$7f1fef90$a301280a@phx.gbl>, "...

Linking Outlook fields from the "Contact Folder" to a custom form in Outlook...
Using Exchange Server 2003/Outlook 2003, we have created a custom Help Desk form in Outlook. We would like the ability to link specific fields from the Contacts Folder (Example B), in a drop-down list box if possible. We are far from knowing that something like this is even possible. Would appreciate *any* suggestions. Thank you. ...

PO numbers in FRx?
Hello: Can FRx transaction detail reports in drill-down viewer for income statements display PO numbers from GP Purchase Order Processing, or does FRx not get that detailed? childofthe1980s FRx can bring in the originating master number of a transaction, however, I cannot think of a case where the PO number will be the originating master number, since the PO itself is not an accounting transaction. So I think you'll be able to see the receipt and/or vendor invoice numbers there, but not the PO number. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports htt...

Random Number in Query
Hi. I am trying to get a Random number between 0 and 5 to appear on each row of a query. I can get the same number on each row (it changes every time I run the query) but cannot get a different random number for each row. Basically, I want to create some Test data by adding the Random number to a Received Date and then saving this new date as a Completed Date. I have created a Module to generate a Random number: Function Random_Number() As Integer Randomize Random_Number = Int(Rnd * 6) End Function Then, I call the function from a query: Field = Number: Random_Number() Ca...

Can my form fill in a field, yet allow me to override?
Each customers is sent to us from a salesman, and we provide service for 1-7 days before the activity ends. A new charge is generated for each day of service. Employee #1 uses the customer service form to generate a bill, and includes the name of the salesman. Employee #2 uses the form to generate a bill on the next day, and has to look up the first bill, in order to know which salesman to record. The same thing happens every day until the activity ends. With each new referral for service, there's a strong chance it will be due to a different salesman, so the customer number an...

Sorting Spreadsheet with Merged Fields
I have a huge spreadsheet that currently fits comfortably on an 11 x 17" page making it difficult to work with and understand....just too big. I'd like to make the spreadsheet fit on an 8.5 x 11" (landscape) page by stacking fields. This is marketing pursuit data so most fields hold text. When I stack the fields some fields must be merged. So far...not tough....I have a beautiful spreadsheet with all the data in a convenient format, easy to understand. The problem is sorting the first field (priority) per field grouping....can't sort because of merged fields. I t...

Hidding rows when sorting
Hi all I have a sheet, where I upload informaton - some rows are hidden (parts already in stock) So, when they sort by some coloumn, it shows all rows, and hides the one which does not match. Is there an event I can catch so I can hide even more? It is a file someone else made at some time, so: how do I add the sort option? WBR Sonnich "hides the one which does not match" Sounds like you are filtering, not sorting. Gord Dibben MS Excel MVP On Tue, 1 Jun 2010 09:46:57 -0700 (PDT), jodleren <sonnich@hot.ee> wrote: >Hi all > >I have a s...

Why my default number format changed
I am using Excel 2007. From time to time, I found the default number format changed when I open an exiting excel file. For example, originally its default format was "General", but when I open the file, found it changed to "Custom". How is it happened? How can I prevent? How can I fix it. Thanks in advance! ...

DrawText() and text orientation
Hi, I have owner draw fixed CMyTabCtrl derived from CTabCtrl. It has overriden DrawItem() and I tried to draw text on tabs with help of DrawText() or TextOut(). On my control I need vertical tabs but horizontal text on tabs. When I set vertical tabs in resource editor then in run-time all texts has vertical orientation on tabs which I dont want. How to change text orientation from vertical to horizontal with DrawText() or TextOut() ? Where is stored such property ? How to enlarge tab size in CMyTabCtrl ? (I need to have larger than standard tab sizes) Peter If you do it using GM_ADVA...

date in a text cell
I need to show my date as 06-Dec-2005 in a text cell. Is it possible to have a regular date format in a text cell. Thanks If you format the cell as Text, then you'll have to type in what you want--exactly the way you want to see it. If you don't format it as text, you could type the date in anyway that's a date, then use a custom format of: dd-mmm-yyyy (format|cells|number tab|custom category) Dajana wrote: > > I need to show my date as 06-Dec-2005 in a text cell. Is it possible to have > a regular date format in a text cell. > > Thanks -- Dave Peterso...

Text function multinational format
Hi In one of my spreadsheets I used a text function to retrieve year from a cell. It's important to point put that I use Polish version of Excel. The function (in my version) was as follows '=tekst(A1;"rrrr")' - value of A1 is "2009-01-23". The format "rrrr" is an equivalent of "yyyy". I than had to send it to someone with an English version of Excel. Unfortunately, the displayed value was "rrrr" and not "2009". Is it possible to use this function in both versions of Excel? Kind regards IgorM Ron de...

group and sort by month / site
I have the following tables/fields relevant to the query I’m after: Screened.site Recruitment.DateOfReferral all I want to do is design a query and subsequent report which shows the number of referrals received in a given month/year and to group the outcome by site (to know how many were referred from a given site). At the moment, I have this query: SELECT Recruitment.DateOfReferral, Screened.Site, Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral FROM Screened INNER JOIN Recruitment ON Screened.StudyNumber = Recruitment.StudyNumber GROUP BY Recruitment.DateOfReferral, Screene...

Bar Chart for Text Data
I am trying to construct a bar chart showing students on the x axis and letter grades on the y axis. The data won't graph unless I make another column assiging a numerical value to the letter grade and plot the number. Any ideas? I am using Excel2000. Thanks Why don't you assign numerical descriptions for the letter grades 0 = E/F 1 = D 2 = C 3 = B 4 = A You can create a bar chart using the numerical info. You can then use Ron Bovey's XY Chart Labeler to relable the Y axis. http://www.appspro.com/Utilities/ChartLabeler.htm Check out Jon Peltier's info on a Vertical...

Automatic Sorting for a newbie
I have two columns, one with names the other with values from 1-50 the numbers match up with the names like a ranking system for example: bob 2 rachel 1 jenny 4 jim 3 etc. I want excel to automatically sort these into rachel 1 bob 2 jim 3 jenny 4 Does that makes sense? the names are already entered but a user enters the numbers in different sheet. I have it set to pull the numbers but i want seperate column to sort them into ascending order without having to d it manually everytime. thanx -- Message posted from http://www.ExcelForum.com Why create a third column C and enter in C2 =...

Re-order Number
Hi IS there anyway you can enter the re-order Number at the Matrix Stage of an Item, instead on every Item individually? If you enter it on the Purchase Order side it doesnt update the Individual item???? H ...

Powerpoint Placeholder Text Box
I have a series of template pages with multiple text boxes. I would like all the text boxes to auto fit consistently if one needs to. It works like that on a two box that is in the basic package, but when I created a 3 box and a 4 box, they each auto fit based on the text in the individual box. I've tried grouping them in slide master, and normal view. Any ideas? ...