Sorting Alphanumeric values in a text field

I'm using Access 2003 for a database for my company.  I have a field in a 
table that has both text and numbers.  They are part numbers, for example 
21BC124.  I kept the field as text because of the text with in the numbers 
and didn't figure that a numeric field would alow the text.  In my part 
numbers table it sorts correctly (first by number then by letter then by 
number again), but in my reports and queries there are a few number that sort 
in the wrong place.  Like this...

20D10-3
21BC123
21BC128
22D10
25TD47
21FA101
21FA200
25FA203
38FA601
21FP604
38WS100

I can't quite figure out how it is sorting, but I would like it to sort 
first in numerical order the by letter then by number again in the order of 
the characters, like this...

20D10-3
21BC123
21BC128
21FA101
21FA200
21FP604
22D10
25FA203
25TD47
38FA601
38WS100

Is there anyway of fixing this?

0
Utf
2/13/2008 4:42:03 PM
access 16762 articles. 3 followers. Follow

2 Replies
1715 Views

Similar Articles

[PageSpeed] 0

Julo.
   It doesn't appear that your text entries (say... PartNos) are not 
consistant enough to create a "perfect" sort.  There is no consistent logic 
to hang your code onto.
   Most PartNos have 2 digits at the start of the string, but from there on, 
the letters vary in length, and some contain some dashes further on.

   Try something like a claculated field in your query...
        Sort1 : Val(Left(PartNo,2))
                    Ascending
would at least sort correctly by the first 2 digits.

    You could try adding another sort field...
        Sort2 : Mid(PartNo, 3, 2)
                   Ascending
but that won't sort perfectly either... just better.

    Just experiment using string functions to get as close as possible to a 
perfect sort.
-- 
    hth
    Al Campagna
    Microsoft Access MVP
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

"julostarr" <julostarr@discussions.microsoft.com> wrote in message 
news:D2A140F3-AC45-483E-8D4B-FE5C5FA4052E@microsoft.com...
> I'm using Access 2003 for a database for my company.  I have a field in a
> table that has both text and numbers.  They are part numbers, for example
> 21BC124.  I kept the field as text because of the text with in the numbers
> and didn't figure that a numeric field would alow the text.  In my part
> numbers table it sorts correctly (first by number then by letter then by
> number again), but in my reports and queries there are a few number that 
> sort
> in the wrong place.  Like this...
>
> 20D10-3
> 21BC123
> 21BC128
> 22D10
> 25TD47
> 21FA101
> 21FA200
> 25FA203
> 38FA601
> 21FP604
> 38WS100
>
> I can't quite figure out how it is sorting, but I would like it to sort
> first in numerical order the by letter then by number again in the order 
> of
> the characters, like this...
>
> 20D10-3
> 21BC123
> 21BC128
> 21FA101
> 21FA200
> 21FP604
> 22D10
> 25FA203
> 25TD47
> 38FA601
> 38WS100
>
> Is there anyway of fixing this?
> 


0
Al
2/13/2008 6:53:37 PM
You might check to see if some of the records have leading spaces.  You can 
use the TRIM finction in your query and then check the sort.
-- 
KARL DEWEY
Build a little - Test a little


"julostarr" wrote:

> I'm using Access 2003 for a database for my company.  I have a field in a 
> table that has both text and numbers.  They are part numbers, for example 
> 21BC124.  I kept the field as text because of the text with in the numbers 
> and didn't figure that a numeric field would alow the text.  In my part 
> numbers table it sorts correctly (first by number then by letter then by 
> number again), but in my reports and queries there are a few number that sort 
> in the wrong place.  Like this...
> 
> 20D10-3
> 21BC123
> 21BC128
> 22D10
> 25TD47
> 21FA101
> 21FA200
> 25FA203
> 38FA601
> 21FP604
> 38WS100
> 
> I can't quite figure out how it is sorting, but I would like it to sort 
> first in numerical order the by letter then by number again in the order of 
> the characters, like this...
> 
> 20D10-3
> 21BC123
> 21BC128
> 21FA101
> 21FA200
> 21FP604
> 22D10
> 25FA203
> 25TD47
> 38FA601
> 38WS100
> 
> Is there anyway of fixing this?
> 
0
Utf
2/13/2008 7:09:01 PM
Reply:

Similar Artilces:

Can't use NA() instead of "" but then pb in chart with zero values
I have column that are created from the IF formula which returns the "" values if the result is false. I can't use NA() instead because then, I am using these columns for other calculations such as average. I need to use those columns also to create charts, but my lines with "" are showing zero values. I read in this forum that we can use a trick solution: to have a column using "" for the calculation and another using NA() for the graph. If I have too many coumns, is there an easier way to solve it? Thank, What is "too many" columns? In my e...

Outlook task won't sort by category
Outlook 2003 is unable to sort Task's by Category. I have checked it on two different machines and the result is the same "You cannot sort by this field". To recreate the error: 1) open tasks> Right click on the Subject bar and select the Field chooser 2) Drag the Categories field to the Task view 3) Click on the Category header to sort by category Is there anyway around this? I have a user who used this feature quite a bit and is not wanting to learn a new technique to sort his home tasks from work tasks. Thanks -k View-> Arrange By-> Categories -- Roady ...

searching 2 values from 2 cells
hi, i have this formula that i have set up but i dont know if i am going down the wrong route or its just the final bit i cannot work out.... i am trying to view the value in coloum 'I' when the first value in coloum 'B' is found.... there will (or should) only be one value of coloum 'B' as this is a list of names which gets generated each day. how to i view the value it brings up in coloum 'I'? =SUMPRODUCT(--('[Reporting 10-01-04.xls]Sheet2'!B$2:$B1000=B1),--('[Reporting 10-01-04.xls]Sheet2'!$I$2:$I1000=" "...

filling down or pasting into multiple fields in table entry
Hi all. Hope you can help. I occasionally need to enter info directly into a table. However, when I do, I would like to enter the same value into a large number of fields. It would be nice if I could: copy, highlight the top, say - 50 fields in a column, and then paste the value into all of them in one stroke. However, Access 2003 doesn't allow this. It just puts the value into the first field. Is there a simple way of doing this? Thanks very much - in advance. H. -- Pontins History E-Mail: usenet@pontinshistory.co.uk Please visit www.pontinshistory.co.uk Skype ID (instant messaging ...

Returning values from mfc-dll to non-mfc dll
Hi, I am new to mfc and dll. I have a non-mfc dll that calls a mfc dll. The mfc dll opens a dialog box. I have done this by 1. writing another func (LaunchDialog()) that does not return mfc-resources or use mfc-rsources as parameteres in the mfc dll 2. CAll LaunchDialog() from the non-mfc dll by including all required header and lib files. extern "C" __declspec( dllexport ) int LaunchDialog() { AFX_MANAGE_STATE(AfxGetStaticModuleState()); InputDialog indlg; return indlg.DoModal(); } The problem -------------- I actually need to pass the value of the text in...

Investment re-valuing
My 401(k) company uses fractional shares and fractional values. The accounts they use are not directly referenced on any particular exchange. I had to manually create the accounts, and I have to manually update the prices. recently, two of the investments were "re-valued". They reference it as a "Transfer" but all that really happens is all my shares for a given investment were listed as negative transactions with the current unit value (so that the dollar value of the transaction was negative). Then they immediately followed that with a positive transaction for the ...

Exchange Open Relay, sort of...
Hello: I've been working on this open relay issue on my exchange server for a bit and now I come to understand that I do not have an open relay. However there is still a problem. Let's say my domain is foo.com Right now anyone can use my server to send email to my domain users as long as they say it comes from a foo.com address. I tested this by conntecting via telnet into mail.foo.com and useing it to send email from any address with @foo.com to anyone in the domain. For example telnet: open mail.foo.com HELO MAIL FROM:<spammer@foo.com> RCPT to:fred@foo.com dat...

Insert incrementing fields into Pub document
I'm new to publisher, but very familiar with MS Word. I've downloaded an "Event ticket" from MS Publisher templates site; I see the number somehow auto-increments, but I can't find a "Insert Field" command. Where is this? Thanks for any help . . . You need to setup a merge data base. Publisher only has their version of "insert field" when you mail merge. You can create a merge list within Publisher or use a Word list or an Excel/Access data. Help here Mail, e-mail, and catalog merge http://office.microsoft.com/en-us/publisher/CH100502901033.asp...

Display values from 1st and 2nd field
Hi, I have a simple table that has two columns surname and firstname, how do I combine the results into a third column? Good at Excel but very new to Access. Thanks. Do it in a query like so: SELECT (FirstName + " ") & Surname AS FullName FROM YourTable; In query design view you'd do it by entering the following in the 'field' row of a blank column in query design view: FullName: ([FirstName + " ") & [Surname] By using the + operator to add the space between the names the space will be suppressed if by any chance someone was recorded only by th...

Returning highest value
I have a list with value_name and its value. Is there a formula tha can look down the list and pick the highest value and return th value_name For example _Value_Name_ _Value_ Value A 5 Value B 10 Value C 33 Value D 8 Value E 51 Value F 32 Value G 47 Value H 9 Value I 41 So the formula would return Value E as its result. Would be much appreciated : -- Fishbon ----------------------------------------------------------------------- Fishbone's Profile: http://www.excelforum.com/member.php?action=getinfo&...

using VBA to retrieve values to ComboBox
i am trying to retrieve a range of data (the stock code of different phones) from worksheet("Inventory") based on the name of the branches of where the phones are stored. worksheet("Inventory") has branch (Column A) and stock code (Column B) i have 2 combobox. combobox1 contains values (name of branches) such as: 1-BS 2-EN 3-HG 4-JE 5-SP 6-TB 7-WS 8-YT combobox2 will have to retrieve values from the worksheet("Inventory") based on the value in combobox1. the values in combobox1 can be found in column 1 while the values i need for combobox...

Update value in tbl source for combo box
Using Access 2003. I have a Form (frmRequests) and a SubForm(frmRequests_sub) for capturing report requests. The frmRequests_sub is based on the table "tblRequests" where all data entered in the subform is stored. Within the subform, I have a few combo boxes where the row sources are other tables (with values). I want these tables (supplying the combo boxes data) to be updated if the end user cannot find what they are looking for in the existing list. Example: In the "Requestor" field/combo box where row source is tblRequestor, if "Jane Doe" is not is ...

Where is the Administrative Note field gone ?
Hi there. Just going thru an upgrade from 5.5 to 2003 SP1 and have a question from one of our helpdesk admins. In our 5.5 environment we were putting little support notes about DLs - who asked for it to be created, the date, who has authority to be added/change the members, original System Request #, etc. Where is this field now in AD U&C now ? Did the Advanced Features, no luck. The only way to see them is thru the old 5.5 admin tool, but once we drop the 5.5 server we're kind of stuck. We don't want to use the Notes field because that's where we store i...

One Calendar form for different fields
Can I just use one form with a calendar on it for all date fields on all of my forms instead of many calendar forms with a control source for each field? I thought I could change the control source with the OnClick property of a button next to each date field I want to use but I haven't had success. Thanks Here's two. One the ActiveX calendar http://www.byerley.net/AccessCalDemo.zip and one based on an Access form http://www.byerley.net/Access2kCalcDemo.zip Demo code for each. "Maarkr" <Maarkr@discussions.microsoft.com> wrote in message news:E36209C4-02B7-41...

save text field w/ leading zeros in .csv format & not lose zeros?
I currently have a csv file with a field that has one digit numbers. I need to create a text field and convert the one digit number to two digit text by adding a leading zero. I have tried using the concatenate function to add the leading zero, but when I close the csv file, it reverts back to one digit. How can I get the file to save the field as text and include the leading zero? Check your CSV file in Notepad, not in Excel. Excel will frequently convert your text representation of a number into a number, even though it likely was written out to disk with the leading zero "...

Custom field type and external data source
Hi I would like to ask you for advice. In our enviroment we have one web application hosting 3 site collection. Those site collection need to share few (3-4) dictionaries. By sharing I undersand that users need to associate data from those dictionaries to the records they create on lists around those site collection. As we have three diffrent site collection (dictionaries are hosted on the main top level site collection), then to achive that we go for custom lookup field type to let users pick-up values from those dictionaries. Unfortunetly this solution has an issue, that whe...

How to delete Macro (double click on form field event)
I did a macro and now want to delete it. The macro opens frmDetails when the client double clicks on a field in frmQuickView I've clicked on View | Code and then clicked on frmQuickView but can't find the code for the Macro or how to delete it. thanks for any help. Mel Woops! I see it.Click on Macro's in the design window and hit delete. Sorry gang. ...

Text Rotation
Hello, I want to draw into a DC some rotated text, drawing also the rectangle like the selection of that text. Everything works fine except...I cannot align the text vertically to the bottom of the rectangle. Function DrawText can do that but only for single line text. I've tried also to create a bitmap with the unrotated text and rotate that bitmap but the bitmap it's not transparent and the rotation is slow... Can anybody suggest me what to do? Thanks in advance! Absolom, If single-line DrawText can do what you want, you might try to use multiple calls to this function. Yo...

Improve text searching capability in Dynamics
Currently to search for a vendor or account you must know the first few letters or words. Searching would be improved if a keyword search would bring up all matches, not just the first string of characters. ---------------- 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 pane. http://www.m...

Choosing fields for system integration
I have a question on system integration on selecting "Unused" fields for storing data. Our system for entering tasks, billing, and tracking status on projects has fields that would be nice to have in the Great Plains system. Like the last 4 digits of the phone number or the gps coordinates of an office. Should these types of additions be done in a new table or is the recommended way to piggyback on an existing field that is not currently being used? What are the issues that we might face on future upgrades with either adding new fields or using "unused fields"? As...

Error: The text string you entered is too long.
Can someone help me shorten this formula, without having to change the path location of the linked sheet in the formula or without having to use additional cells in either sheet to do the calculation? The result of the formula only shows up if I have the linked spreadsheet open because it will shorten "('C:\Open folder\Dummy folder\Default\Sub Folder\Another Folder\2nd to last folder\last folder "06\[Name of spreadsheet ''06.xls]Spreadsheet'!" to just "[Name of spreadsheet ''06.xls]Spreadsheet'!" Which shortens the formula enough to not use ...

Clicking in Text Box
Sometimes the users can really be annoying with inane stuff but they sign my checks. My question is this: When clicking into a text box, how do I get the cursor to automatically go to the left side of the box? Kinda like an auto Home keypress ... thanks Private Sub MyTextbox_GotFocus() Me.MyTextbox.SelStart = 0 Me.MyTextbox.SelLength = 0 End Sub -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Lythandra" <Lythandra@discussions.microsoft.com> wrote in message news:EA5FBBB5-3ED4-4428-B9F2-7D381D053D6C@microsoft.com... > Someti...

count the total number of certain values
I'm doing the net promotor score. How do I get the total number of cells with 9 and 10? Is there an example of calculating net promotor scores? Thanks, Hello there. I do believe you are wanting to try and work with the Countif function. There's plenty of good help on that in Excel and online. As the best advice I can / could & will give to anyone not entirely familiar with Excel, the best way to work out long and or complex problems is to break it down, one column (or row) at a time. IE: If you have all your data in a list format (and the 9's & 10's you are tryin...

Seperating text in a cell
Hi I have a column that contains the first and last name of parents. I want to seperate the first name and put it into it's own column. How can I do this Thank you Jennifer, have a look at data, text to columns -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my email address to reply by email ** "Jennifer Leen" <anonymous@discussions.microsoft.com> wrote in message news:C43D9516-A829-4085-A175...

PivotTable
I wish the page field of the PivotTable is more user friendly. In order = to select multiple items, I hide the items that I don't want and then = select (all) to get the multiple items. I am wondering if there is an = easier way to *unhide* when I am done. Currently, I drag the page field = button to the row fields area to select/deselect etc. and then drag the = page field button back to its place. Appreciate advice. Epinn=20 =20 Epinn I'm not sure I fully understand what you are after here. You don't need to use "Hide" and "Unhide" from the PT toolb...