f



Sorting a Text field like a Number Field

I have a table with around 18,000 records in it.  One of the fields is
a Text field, but 85% of the things in that field is a number (The
other 15% is a number with a text suffix).

I was wondering if there was an easy way to Sort this field as if it
were a number (because, when it sorts as text it looks like this...

1
11
12
14
2
21
24
3
35


So far, the only thing I came up with was to make a query with this
expression...

TempSection: CDbl([Section])

But that either produces a #Error for those rows where the Section has
a text character in there or it won't even open the query in datasheet
view because of Data type mismatch in criteria expression.

What can I do?

Can I create an iif statement to catch that error? or Something?

Thanks!
--
Nathan
0
ngiven
10/5/2003 11:31:25 PM
comp.database.ms-access 1111 articles. 0 followers. Post Follow

4 Replies
589 Views

Similar Articles

[PageSpeed] 48

Try this:
sc:iif(isnumeric(x!y),cdbl(x!y),0)

where X is table and Y is field.

Sort on this but display y.

This will place all fields containing text at the top and then sort
numbers.  If you want all text fields at the bottom then change 0 to
99999999.

If you really need to sort the text contianing fields as well you will
need a funtion to extract the numeric part and use that as the
arguement to cdbl.  You wouldn't need the iif function then.

Good Luck
Ira Solomon

On 5 Oct 2003 16:31:25 -0700, ngiven@hotmail.com (Nathan Given) wrote:

>I have a table with around 18,000 records in it.  One of the fields is
>a Text field, but 85% of the things in that field is a number (The
>other 15% is a number with a text suffix).
>
>I was wondering if there was an easy way to Sort this field as if it
>were a number (because, when it sorts as text it looks like this...
>
>1
>11
>12
>14
>2
>21
>24
>3
>35
>
>
>So far, the only thing I came up with was to make a query with this
>expression...
>
>TempSection: CDbl([Section])
>
>But that either produces a #Error for those rows where the Section has
>a text character in there or it won't even open the query in datasheet
>view because of Data type mismatch in criteria expression.
>
>What can I do?
>
>Can I create an iif statement to catch that error? or Something?
>
>Thanks!

0
Ira
10/6/2003 2:04:52 PM
in the query pane where the fieldname is overtype with: cint(
fieldname ).  This will convert the field into a interger, so when
sorted, it appears in the correct order.

if there are also letter charactors stored in that field in a normal
SQL environment you can use a case statement: something like

case when isnumeric(fieldname) = true then cint(fieldname) else
fieldname end

But unfortuantely access sql, insn't ansi standard so its not that
good.

I hope this helps....
Philippa

ngiven@hotmail.com (Nathan Given) wrote in message news:<f749d926.0310051531.a1f1a3d@posting.google.com>...
> I have a table with around 18,000 records in it.  One of the fields is
> a Text field, but 85% of the things in that field is a number (The
> other 15% is a number with a text suffix).
> 
> I was wondering if there was an easy way to Sort this field as if it
> were a number (because, when it sorts as text it looks like this...
> 
> 1
> 11
> 12
> 14
> 2
> 21
> 24
> 3
> 35
> 
> 
> So far, the only thing I came up with was to make a query with this
> expression...
> 
> TempSection: CDbl([Section])
> 
> But that either produces a #Error for those rows where the Section has
> a text character in there or it won't even open the query in datasheet
> view because of Data type mismatch in criteria expression.
> 
> What can I do?
> 
> Can I create an iif statement to catch that error? or Something?
> 
> Thanks!
0
philippa
10/20/2003 2:00:09 PM
Should be a simple matter of ORDER BY val(strField) if I'm
understanding your problem correctly... but it's late.

to test this create a table (tblStrNum) with 2 fields
(str:text,num:number)
open the table and enter 1,22text,100 into str. now enter 1,22,100
into num.

create a query (qrySortStrNum):

Select str,num 
FROM tblStrNum
ORDER BY str;

str	num
1	1
100	100
22text	22

this represents your current situation. now modify the sql to read:

Select str,num 
FROM tblStrNum
ORDER BY val(str);

result:

str	num
1	1
22text	22
100	100

and thereyago :-)

-John

ngiven@hotmail.com (Nathan Given) wrote in message news:<f749d926.0310051531.a1f1a3d@posting.google.com>...
> I have a table with around 18,000 records in it.  One of the fields is
> a Text field, but 85% of the things in that field is a number (The
> other 15% is a number with a text suffix).
> 
> I was wondering if there was an easy way to Sort this field as if it
> were a number (because, when it sorts as text it looks like this...
> 
> 1
> 11
> 12
> 14
> 2
> 21
> 24
> 3
> 35
> 
> 
> So far, the only thing I came up with was to make a query with this
> expression...
> 
> TempSection: CDbl([Section])
> 
> But that either produces a #Error for those rows where the Section has
> a text character in there or it won't even open the query in datasheet
> view because of Data type mismatch in criteria expression.
> 
> What can I do?
> 
> Can I create an iif statement to catch that error? or Something?
> 
> Thanks!
0
jobrienct
10/28/2003 9:12:33 AM
I did it this way.  In my case, I had street numbers [STREET_NUMBER] 
entered as text.  None were > 999, but some had non-numerical junk, or 
nulls.
This method left records with unusual content unmodified, and useful 
results that could be sorted nicely.  You could expand the code to cover 
longer strings.

STREET_NUM: IIf(Len([STREET_NUMBER])=1,"  " & 
STREET_NUMBER],IIf(Len([STREET_NUMBER])=2," " & 
[STREET_NUMBER],[STREET_NUMBER]))

John wrote:

> Should be a simple matter of ORDER BY val(strField) if I'm
> understanding your problem correctly... but it's late.
> 
> to test this create a table (tblStrNum) with 2 fields
> (str:text,num:number)
> open the table and enter 1,22text,100 into str. now enter 1,22,100
> into num.
> 
> create a query (qrySortStrNum):
> 
> Select str,num 
> FROM tblStrNum
> ORDER BY str;
> 
> str	num
> 1	1
> 100	100
> 22text	22
> 
> this represents your current situation. now modify the sql to read:
> 
> Select str,num 
> FROM tblStrNum
> ORDER BY val(str);
> 
> result:
> 
> str	num
> 1	1
> 22text	22
> 100	100
> 
> and thereyago :-)
> 
> -John
> 
> ngiven@hotmail.com (Nathan Given) wrote in message news:<f749d926.0310051531.a1f1a3d@posting.google.com>...
> 
>>I have a table with around 18,000 records in it.  One of the fields is
>>a Text field, but 85% of the things in that field is a number (The
>>other 15% is a number with a text suffix).
>>
>>I was wondering if there was an easy way to Sort this field as if it
>>were a number (because, when it sorts as text it looks like this...
>>
>>1
>>11
>>12
>>14
>>2
>>21
>>24
>>3
>>35
>>
>>
>>So far, the only thing I came up with was to make a query with this
>>expression...
>>
>>TempSection: CDbl([Section])
>>
>>But that either produces a #Error for those rows where the Section has
>>a text character in there or it won't even open the query in datasheet
>>view because of Data type mismatch in criteria expression.
>>
>>What can I do?
>>
>>Can I create an iif statement to catch that error? or Something?
>>
>>Thanks!
0
Bruce
1/21/2004 10:20:19 PM
Reply:

Similar Artilces:

multiple conditions in a join, access doesn't like on (table1.field=table2.field and table1.field=table3.field)
Hi I have written the following query and am having problems with the last outer join in ms access... it doesn't like the multiple conditions that I have in there, if I have just one or the other its OK but not both... and I have tried putting the second condition in a where clause but then i don't get an outer join i'm pulling my hair out on this one... i would be extremely greatful for some ideas... SELECT ro.thickness,ro.width,ro.grade,sum(ro.pcs) as pieces, ro.length,GT.thicknessid,t.thicknessid,gt.gradethicknessgroupid, gtd.gradeid, gr.gradeid FROM(((NewOrders as RO LEFT O...

Linking A Text Field With A Number Field
Is there any way to link a field I'll call StudentId in Table1 with StudentId in Table2? In Table1, StudentId is a number and in Table2 it's a text field. The values match up fine so other than one being a number and the other a text field, everything else would be okay. I know I can create an append query to copy, say, Table2 to e.g. Table3 and make StudentId a number field in Student3 but wondered if there's a more "elegant" solution. Thanks, Sheldon Potolsky On Mar 6, 11:42=A0am, Sheldon <SHPsalm...@aol.com> wrote: > Is there any way to link a field I...

How to convert Number Field to Text Field
I'm newbie in Filemaker, currently I'm using Filemaker pro v 6.01 on Windows XP. I have a small problem. I'm trying to make a solution for Hotel Rate. I want to change the USD. 0.00 to "Included" for breakfast rate which already included in the room rate. Anybody can help, will be much appreciated. Thanks. "Adhie" <priyadi77@yahoo.com> wrote in message news:ba1b5c27.0404080446.21e110b2@posting.google.com... > I'm newbie in Filemaker, currently I'm using Filemaker pro v 6.01 on > Windows XP. I have a small problem. I'm trying to make a ...

MS Qry and text field to date field
I am using MS Query to pull files (from IBM AS400) and put them in Excel. However, when I do this the dates are now text fields. The text/ Date field looks like this: 20070331 (YYYYMMDD) I would like the change this text to a date field so Excel will see it as a date. Can someone please help me? I have looked all over the place for a solution and I have found nothing. Also, if you know of a good place to get information on MS query please let me know. /Thanks! wwolford wrote: > I am using MS Query to pull files (from IBM AS400) and put them in > Excel. However, when I do this the...

Sorting numbers in a text field
Hi there... Whilst I am sure this question has been asked in other forms before, I have searched the groups using google and not found an answer. This is my first question to this newsgroup, but not my first newgroup post to other groups. I have a VARCHAR2 field which contains numbers. Order by sorts these lexigraphically (how /do/ you spell that?!). I have overcome this with the code below: SELECT LPAD( text_col, (SELECT MAX( LENGTH(text_col) ) from table), ' ') AS Sorted FROM table ORDER BY Sorted; This works great for only numbers, however some of my numbers contain suffixes, as can be seen in the example output below: SORTED -------------------------------------------------------------------------------- 1 3 6 7 10 34 43 3-A 3-B 17-B 10 rows selected. I am looking for a purely sql hint, or some direction, and am using oracle 9i. Thank you in advance, Mitch. I have found my own answer, thank you. Please see code below (note that the +2 on the default in the decode is because there is only ever a hyphen and letter, 2 characters. if you had a variable no of characters afterwards this will not work) Thank you for any who looked over it Regards, Mitch. COLUMN Sorted format a20 SELECT DECODE( INSTR(test,'-'), --test for hyphen 0, LPAD( test, --if no hyphen, add padding to right align text/numbers ...

Sorting Numbers in a Text Field
One field in my database contains the dosage of medicines. It's a text field since I have to include the units. Here's a list of what might be in the field: 10 mg 20 mg 50 mg 100 mg When I sort I want them to appear as I listed them above. However, they appear as 10 mg 100 mg 20 mg 50 mg Any suggestions for solving this problem? Rich This question has been answered multi times before. You have entered the numbers in a text field so they are sorted on their ascii value and not on their numeric value. Put the numbers in a number field, leave the mg part out (since it is always mg...

Formatting number fields within a calculated text field
I have a calculated text field which is part of an invoice printout. The definition is as follows: Case ( WeeklyRental > 0 ; "Includes " & Weeks & " weeks rental. After " & Weeks & " weeks rental will be charged at �" & Round (WeeklyRental;2 ) & " per week"; "" ) This shows in an invoice to specify rental amounts after a certain number of weeks. I want this statement to completely disappear in other invoices where no rental is to be charged. As it is it works fine except - the weekly rental does not format properly...

Accessing text form fields name=field[label]
I'm pretty much a JavaScript novice. I'm good at learning by example and changing those examples to suit my needs. That said .... ..... I have some select fields in a form I created for a database search that I am unable to figure out how to access. (The search is implemented in Php/MySQL.) The user enters search values for: name, address1, city, .... etc., ..... and for each of these they also select whether the search should look for the field to be equal to, not equal to, Like, or Not Like the value in the database. So in the form I have: <input type=text name=city&...

text field only for text
Hi I would like some help about the following : I have a text field and I don't want it contains any numbers. How can I limit this situation ? So far, I couldn't find literature exacly about this here in the newsgroup. TIA Paulo "Paulo Rodrigues" <amigo.alf@netvisao.pt> wrote in message news:<newscache$kgmtmh$bb1$1@newsfront4.netvisao.pt>... > Hi > > I would like some help about the following : > > I have a text field and I don't want it contains any numbers. How can I > limit > this situation ? > > So far, I couldn't...

Text field calculation to include a Number field with thousands separator
No doubt I'm missing something quite basic here but a silly little problem that I can't find a solution to. Using FM8.5 Adv on Win XP. I have a number field called rem_charge, which appears on a layout, it is displayed as a number with 2 decimal places and a thousands separator when the value is 1000.00 or greater - all ok. Also on this layout I have a calculated text field called rem_chargetext, this field I have calculated as "�" & rem_charge & " plus VAT" I want the rem_charge part of this displayed with the thousands separator as required. Unfor...

how to sort with text and numbers in one field
I started working on a new database for our fire alarm shop. The idea was to keep track of all the devices (smoke detectors, pull stations, etc) as well as how many different makes and models of fire alarm panels that we maintain. It would also be there to answer the question that comes up at least once a year: "Exactly how many smokes (or pull stations) do we have in the field?" The boss has also requested that one of the forms to create would be an annual test form. This would allow the techs to find the building they need to test in the database, and then print up all the ...

SQL Server View integer field shows as text field in Access
Hi All! I have an SQL Server View where my field in the view is an integer field and works well. When I link to it from Access, the field is defined as a text field in Access. Does anyone have any idea why this is happening or how to fix it? Thanks in advance! KB On Sun, 3 Aug 2008 20:31:43 -0700 (PDT), kevinfbutt <kevin.butt@iavalley.edu> wrote: That's quite unusual. Can you post the SQL for that field in that view? What version of Access and SQL Server? Fully patched? Can you replicate the problem with a view in one of the SQL Server sample databases? -Tom. Microsoft Access ...

Access treating Memo like a Text field
Hi, I have an web app and I am using access 2002 and VB.net. I am using a data adapter to fill a dataset. I am retrieving product descriptions, some are long and i have used a field of memo datatype to save them in. The problem is sometimes I can only retrieve the first 255 characters in a memo field and other times I can retrieve the full product descriptions which can be over 640 charaters. The problem is consistent, the same fields are always truncated to 255 characters and the others are always displayed in full. Has anyone heard of this before? Any ideas on where I can look? Thanks ...

SQLExecute Update to MS-ACCESS text field
I'm trying to update an access .mdb table1 field test, a text field and am getting the resulting error below. Any ideas. TIA In v8=16 v11=5 v13= ToString[v8]<>"-"<>ToString[v11]; SQLExecute[db,"UPDATE table1 SET test = "<>v13<>" WHERE rec = "<>ToString[recV]]; Out JDBC::error: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '16-5'. ...

Parsing out data on text field in MS ACCESS
Hi All, A newbie here having a hard time figuring out how to parse out the City, State and Zip from a text field. I have a text field called "Registration" with a size of 40. In this field there are city, state and zip information. I need to seperate all three items into their own respective fields. Unfortunately, the information varies in size and location. An example, would be: "LOS ANGELES CA90063 ", while another record would be "LOS ANGELES CA90063". If anyone can point me to any references or examples on the web, it would be greatly app...

text fields to memo field
Is there a way to combine text fields into one memo field? This is an existing database. Some people in the database may have several "contact" entries where they have a "note" in a text field. I would like to figure a way to combine all the "note" fields for each person into one memo field. Thank you "RR" <texson552000@yahoo.com> wrote in message news:<S6HRb.969$ee6.74@fe2.texas.rr.com>... > Is there a way to combine text fields into one memo field? > > This is an existing database. Some people in the database may have seve...

How to link fields in MS word document to Access database
Anyone have experience in linking fields in an MS Word document to an access database? What I am considering doing is taking an existing fill-in-the fields word document, for which I cannot now save the data, and allow it to be opened with no data, have the fields filled in and then save the data in an Access database. I also want to later select the document (from a file in the database), open the word document and populate the fields from the database. Of course if there are then any updates I want to put them back into the database. Workable? Anyone with sample code? Bob Alston...

Problem concatenating memo fields in MS access database
I have an access database form where I have a button that is supposed to be appending the contents of 1 memo field to another is behaving non-predictably. The code is: Me![ToDo] = Me![ToDo] & Chr$(13) & Chr$(10) & Me![Studies1] & Chr$(13) & Chr$(10) & Me![Studies2] Me![Meds] = Me![MedsEntered] & Chr$(13) & Chr$(10) & Me![IVF] & Chr$(13) & Chr$(10) & Me![Diet] DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 Any thoughts on what I'm doing wrong? Could there be a casting problem? (I just want to append the text...

FileMaker Pro 9 OS X Defining text fields and numerical fields to aviod the quote in the fields to avoid it from exporting out to an Excel Spreadsheet or Text File.
I have been reading the help files in FileMaker Pro 9 about Text formats but have yet to find my solution, perhaps there is not one or it is a formula that is beyond my scope at this time. I have fields that are strictly numbers and the fields have been defined for numbers and I have no " around the text. I have field I have defined as text for it is a mix of text and number or text and non text characters such as / or ? or . or , or * and the like and they get " " in the field. I want to prevent that. How do I do this where none of my input gets the open and closing double ...

multi-field array sort using Sort::Fields method
Hello all. My goal is to be able to perform a "multi-field sort on a multidimensional array". Having read many posts in the newsgroups, I was unable to find a "straight" answer to this problem. I therefore came up with this method. My question is, is there a more efficient solution to this problem or is my method acceptable? Is there another CPAN module that can be used? I welcome all opinions. #!perl use strict; use warnings; use Sort::Fields; my ( @arr01, @arr02, $r, $c, $string, $aref, $delim, @arr_temp, @arr_final ); @arr01 = ( [1, 'a', 'dom'],...

Regular expression for email or mobile number in one field like facebook email/mobile field in registration
Regular expression for email or mobile number in one field like facebook em= ail/mobile field in registration I am building a registration form in in wh= ich i am trying a make a field say email/mobile where user can either put e= mail address or mobile number with country code.similiar approach is used i= n facebook registration.can anyone have solution for such regular expressio= n. i am building the form using angularjs with its default validator. ashokacharya1988@gmail.com wrote on 12 Jul 2015 in comp.lang.javascript: > > Regular expression for email or mobile number in...

Forcing a New Line in a Text Field in MS Access using VB
Heya! I feel I'm beating an old horse to death with this question but I can't get around the problem. I want to update a text field on a form with a string from a VB module. I need enter/linefeeds in the string. I've tried: Chr(10) & Chr(13) Chr(13) & Chr(10 vbnewline vbcrlf none of which seem to work. I've set the properties for the text box to [Enter Key Behavior] = 'New Line In Field' and [Can Grow] = 'yes'. At the moment, my code looks like this: celsql = celsql _ & "Program: " & encname _ ...

sort items like Netflix
Hello, I would like to be able to have the user sort a list of items similarly to the way you sort your queue on Netflix.com. (the numbers dont change dynamically on netflix, they must be doing something serverside with them) Like this: ([] represents a text or select filed) [1] an item [2] something else [3] a differnt thing [4] the other [5] yougetthepicture So, the user would change the 5 to a 1 if he wanted that item to be #1. I thought that if i had script that would move everything down a number each time the user changed one of them, that would work. The items can stay where they ar...

how to import text field from MySQL db to MS Access 2003 using ImportXML
Hi Guys, I'm trying to import data out of MySQL db to MS Access 2003 using ImportXML. I don't have any problems doing the import, except for one table with a "text" field defined. I'm getting an error message saying "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.". Thanks, Do you have to use ImportXML? Why can't you use the ODBC driver to MySQL? Linda <nepifanio@gmail.com> wrote in message news:1137431763.824498.100740@g44g2000cwa.googlegroups.com... > Hi Guys, > > I'...

Web resources about - Sorting a Text field like a Number Field - comp.database.ms-access

Topological sorting - Wikipedia, the free encyclopedia
In computer science , a topological sort (sometimes abbreviated topsort or toposort ) or topological ordering of a directed graph is a linear ...

Category:WikiProject Stub sorting - Wikipedia, the free encyclopedia
This is a collection of policy pages, discussion pages and categories relevant to the WikiProject Stub sorting . They are in no way "owned" or ...

INFOGRAPHIC: Understanding Facebook’s Post-Sorting Algorithm
Facebook’s News Feed is sorted by an algorithm that many people call EdgeRank . It weighs not only the timeliness of the post, but the relevance ...

Autism iHelp – Sorting on the App Store on iTunes
Get Autism iHelp – Sorting on the App Store. See screenshots and ratings, and read customer reviews.

Sorting the clutter - Flickr - Photo Sharing!
My kitchen floor during the organization process.

Joshua Landis on ISIS, Syria & the "Great Sorting Out" in the Middle East - YouTube
Danny Postel in conversation with Joshua Landis, Director of the Center for Middle East Studies at the University of Oklahoma, former president ...

Sorting inbox fact from fiction
Internet mythbusters expose flying hotels, cancer-causing bottled water and hillbilly weddings.

Sorting racist language from bad taste
Tackling racist language is a moral imperative. Censoring bad taste is morally indefensible. Our task is to learn to tell the difference.

CSIRO scientists sorting mail, cleaning labs after budget cuts
Highly skilled scientists and project leaders at the CSIRO are being kept from their research to clean laboratories, write promotional material, ...

Convicts and sex slaves: sorting the fact from the fiction in British TV series 'Banished'
A salacious story about the establishment of Sydney's penal colony should not be mistaken for history.

Resources last updated: 2/25/2016 11:47:23 PM