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
1515 Views

Similar Articles

[PageSpeed] 29

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:

Customer Item Number
I require the addition of Customer Item Numbers to the SOP reports. Prior to V7 this was done using the explorer (smartlist) dictionary. This is not the case in V8 and I need to know how to access the Sales Customer Item Cross Reference Table using the Dynamics.dic dictionary? Could not find any existing functions for this and require insight. Thanks. The alternate reports in SmartList are no longer required for v8.0 because the table used for storing the Customer Item Numbers is now part of core Dynamics.dic. Please create a relationship to the sopCustomerItemXref (SOP60300) Sales ...

searching form multiple Values in a Text Field
Hi.. I have a table of Products and StateID. The products with a field for which states they are availble using the codes from the StateID table tbl-Products ProductID Description Available 1 "Blue Coffee Table" "1,2,3,5" 2 "Red Coffee Table" "2,3,4,19,21,18" tbl-StateID StateID State Country 1 AU ACT 2 AU NSW 3 AU VIC 4 AU QLD 5 AU SA 6 AU TAS 7 AU ...

Sum last 10 numbers...
I have numbers in cells J2:J8 right now. I will be adding a number to cell J9 tomorrow and J10 the next day and so on. I need a formula to sum the last 10 numbers in the J column. Thanks. I thought this would work but it is not for some reason =SUM(INDIRECT("J"&MAX(1,COUNTA(J:J)-10)&".J"&COUNTA(J:J))) Any ideas? Thanks One way: =SUM(OFFSET(J2,MAX(0,COUNTA(J2:J65536)-10),0,10,1)) In article <CE4E33E7-090D-46FD-A14A-B2421631C94E@microsoft.com>, Jambruins <Jambruins@discussions.microsoft.com> wrote: > I have numbers in cells J2:J8 r...

extract matching text to make report
I have a daily print log w/ 6 columns that record: date, client name, printer's initials and three columns pertaining to sheet size. This is working well on a daily basis as the number of sheets in a particular size are continuously added as the month and list go on. At the end of the month I need to consolidate the list in order to bill the clients. Some of the clients names (print orders) will show up numerous times. Some names are new clients, etc.. Question is: Besides using filters on the same sheet, how can I make a consolidated list that will show the clients name only ...

Text Import Wizard Not Starting
Trying to open/convert a text file to Excel. Wizard is not launching, opens the row in 1 big column. Steve Does the file have a .txt extension? Andy. "SteveB" <anonymous@discussions.microsoft.com> wrote in message news:05bc01c3c3e9$ceb599b0$a001280a@phx.gbl... > Trying to open/convert a text file to Excel. Wizard is > not launching, opens the row in 1 big column. Good question,is the PC plugged in :), on the original file it does, not certain if client manipulated when saving. I left a voicemail. >-----Original Message----- >Steve > >Does the fil...

Label Fields
These labels are xml I think. I'm trying to create a label that does 2 things. 1. I would like to remove the "$" from the price. It seems like xml uses the following to rference the price: Begin Field Price Value = "Price" Left = 4440 Top = 450 Width = 1290 Height = 780 Align = Right 2. Is there a way to make the cents part smaller than the main part and aligned at the top right? ...

data sort incorrectly
Three rows of data are being put incorrectly at the end of the second column sorted. I have a total of 8 columns and am sorting by 2. Since the first column is sorted correctly, I suspect the problem is with the second column. Everything is formatted as text, since the data contains numbers and text. I have checked all formatting, spacing, etc. Any ideas? Thanks very much. If you entered the numbers first, then changed the formatting to text, then the value is still a number. Since you formatted the cells as Text, try selecting each of the 3 cells. Hit F2, then enter. Excel w...

SQL Query for Updating Reorder Numbers
We use data import software to update item information for new customers from Excel or CSV files, however it is very time consuming to update reorder numbers since they aren't in the Item table of the database where most of the other information is stored. Is there an easier way to update reorder numbers? I was thinking of maybe putting the reorder numbers in the extended description and running a query to update the items but it doesn't seem to work. It's probable that your import is not creating an item reference in the supplierlist table, where supplier reorder number is...

how do i delete connected text boxes?
i need to delete a sperate section in a text box on Publisher. it's in the vintage newsletter and the Inside this issue section Unlink the text boxes, the text will stay in text box one. You will have to delete the text by elongating the first text box. -- Mary Sauer http://msauer.mvps.org/ "drjones168" <drjones168@discussions.microsoft.com> wrote in message news:2949781C-9685-4614-B7C3-2418E76C02D0@microsoft.com... > i need to delete a sperate section in a text box on Publisher. it's in the > vintage newsletter and the Inside this issue secti...

Sorting tasks on multiple keys
A question from a user of Outlook 2007: "Is it possible to have my tasks ordered by person tasked and under that by subject? I can't figure out how to do both." Sure, set your view to Group By the Assigned To field and to sort by Subject. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "Andrew M. Saucci, Jr." <spam-only@2000computer.local> wrote in mes...

Additional Item fields in HQ and Store operations
I need 6 additional item fields to define information in my item table file in HQ and SO. Does anyone have a solution? Lloyd, We don't have anything in place as of now, but this is certainly functionality that we could provide to you. If you are interested in receiving an estimate, please drop me an email or give me a call. -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises 320-230-2288 rsakry@rite.us www.rite.us "LLoyd" <LLoyd@discussions.microsoft.com> wrote in message news:97CE5C26-1A04-4873-95D1-22A913DD4D47@microsoft.com... >...

text and letters
Dear all, couple days ago i have set a query how can use the concatenate formula More of you replied and thank you very much there is a way that i acn take specific characters from a word? eg; i have in a cell the word BUDGET there is a way to make a link with formula to another cell and take only the B D G in order to show BDG? Thanks in advance Manos If you're always substituting for the same word, you can use =IF(B1="BUDGET","BDG","") If you always want to delete the letters E, T and U: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"E",&...

How can I turn off rounding for numbers???
Excel 2003: I've got a column of cells. I highlight the entire column, do a format / cells / number then I choose 0 decimal places I have a list of serial numbers that I want to input, and then "fill down"... because they're all in sequence. However, when I input. They are 14 digits long. Every time I input a 14-digit number, Excel forces it to round up to the 5th number from the right. How can I get it to stop doing this? Hi Excel should not round in this case. Maybe it shows the number in the scientific notation. Try the folowing custom format: 00000000000000 >...

Outlook 2000 *No texte and no picture*
Hi, I have a little problem with outlook 2000. Presently we use windows XP pro with Office 2000. The problem is, when i send a email (we use a template with logo, etc.) at the reception i never see the template .. all picture is in attachtment and time to time i cant see the message. if i do reply the message appear. I already checked the setting .. it's the same as all other computer. it's only 2-3 computer that have this problem. So, for now i cant find why the problem appear. PLZ if someone can help me this will be very appreciated =) Have a good day Fr�d�ric ...

Reformatting Text to Excel
Does anyone have an easier way to move data from a text file to Excel, rather than manually adjusting each line. Here is a sample of the data: Card Name: Woodwraith Strangler Cost: 2BG Type: Creature � Plant Zombie Pow/Tgh: 2/2 Rules Text: Remove a creature card in your graveyard from the game: Regenerate Woodwraith Strangler. Set/Rarity: Ravnica: City of Guilds common Card Name: Zephyr Spirit Cost: 5U Type: Creature � Spirit Pow/Tgh: 0/6 Rules Text: When Zephyr Spirit blocks, return it to its owner�s hand....

Supplier sort order
When you look up a supplier to assign it to an item, you can sort by supplier name. The next time you go into that supplier lookup window the sort is back to sorting by supplier ID instead of the name as was previously chosen. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message...

Sum Numbers By Text
Hello All, I have an excel sheet that contains two columns. One is text ie. "Ne Items" & "Old Items". The other column contains numbers. I am havin a difficult finding the correct formula that will sum only "New Items" Can someone please help me with this opportunity? Best Regards, Shahba -- Message posted from http://www.ExcelForum.com Hi try =SUMIF(A1:A100,"New Items",B1:B100) -- Regards Frank Kabel Frankfurt, Germany > Hello All, > > I have an excel sheet that contains two columns. One is text ie. "New > Items" &a...

printing checks with account name and number
I can not seem to figure out how to print checks and have the account name, number and the bank it is drawn on printed on the check. HELP! See http://umpmfaq.info/faqdb.php?q=39. "Lynn" <lwalk3@hotmail.com> wrote in message news:3b5f01c4729c$e2f554d0$a401280a@phx.gbl... > I can not seem to figure out how to print checks and have > the account name, number and the bank it is drawn on > printed on the check. > HELP! ...

How to change number to text
This is a multi-part message in MIME format. ------=_NextPart_000_0022_01C37C46.1E8FC780 Content-Type: text/plain; charset="big5" Content-Transfer-Encoding: quoted-printable Hi all, How can I change the numeric into text such as=20 1,234 to One Thousand two hundred and thirty four Thank you ------=_NextPart_000_0022_01C37C46.1E8FC780 Content-Type: text/html; charset="big5" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D&q...

How to Sort Desktop Icons
At times, your desktop shows icons sorted alphabetically in two groups. All users Desktop, and user Desktop. Create a temporary desktop folder. Copy the icons from the Windows Explorer Desktop into this folder. Now delete the desktop icons. Copy the icons from the temporary desktop folder to the Windows Explorer Desktop. Now your desktop will be sorted alphabetically in one group. -- Lassar ...

text on speadsheet, converted to % value in chart
I have a 10 step process. How do I give text "X" a 10% value on the spreadsheet so it will display as 10% for each completed step in a chart? In a seperate cell, do something like =COUNTA(A2:A10,"X")*10% and then base your chart off of this new cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "RenKemp" wrote: > I have a 10 step process. How do I give text "X" a 10% value on the > spreadsheet so it will display as 10% for each completed step in a chart? Luke, we are mixing metaphores should be...

Pulling Multiple Text Strings in a One Cell
Okay....this is what i need help on. These multiple lines in one cell is throwing me off. If i make the multiple lines appear as a single text string in one cell, i still have spacing issues. (A1) ATM ID: Example Project ID: 12345 Project Name: HELP Site Name: Company Name Model: 2345e EF SN: Not Assigned I need it to look like this all in one cell, due to a system upload. ATM ID: Example**Project ID: 12345**Project Name: HELP**Site Name: Company Name**Model: 2150e EF**SN: Not Assigned Please HELP!!! Thanks Try: =LEFT(A1,15)&"* *"&am...

How to reference main report field in subreport calculation
My main report will populate a field X, but int he sub report, I need to reference that X value in order to do a calculation. How do I do this and where? rickr wrote: >My main report will populate a field X, but int he sub report, I need to >reference that X value in order to do a calculation. How do I do this and >where? Try using Parent.[field x] in VBA code or in a text box expression. -- Marsh MVP [MS Access] ...

can't change the alignment of numbers in cells
i have an excel workbook in which there are 3 sheets. in one sheet when i put any number it goes to the left side of cell and if i press the align left or center button it does not work. i even changed the category to number but still same problem. If i put english words, i can change everything. thankx! Try changing the cell's format to General. (Format|Cells|number tab) What was it formatted like before? create_share wrote: > > i have an excel workbook in which there are 3 sheets. in one sheet when i put > any number it goes to the left side of cell and if i press the a...

Formatting fields....
Normally, if I want to format a field in a query – I set the properties in the field using the GUI... However, I’m doing a funky join, which the GUI is unable to represent... So I’d have thought that using format() in the SQL would be the best way – e.g. format([fieldname],”#,##0.00”) But this appears to change the data type to text and left align it, which makes it look .....Special..... Any ideas? Rob Wills wrote: >Normally, if I want to format a field in a query � I set the properties in >the field using the GUI... > >However, I�m doing a funky join,...