Dear Sir or Madam,



In a table I have a set of number which I am trying to sort(Asending)



IE: IC-1,IC-2,IC-3,....IC-129



These numbers have been moved and I would like to make access disreguard 

the "IC-" and only look at the number at the end.



Can this be do?

On design a query it does not let me specify what to leave out, only 

choose the field, and the manner of the sort.



Please help

In a Field in your query you can add the following code:



LinkTo_No_Only:

IIf(InStr([LinkTo],"-")>0,Right([LinkTo],Len([LinkTo])-InStr([LinkTo],"-

")),[LinkTo])



I'm using the LinkTo field in the IC-GeneralInfo table.



John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: KennethMungwira [mailto:KennethMungwira@Y...] 

Sent: Friday, December 21, 2001 4:54 PM

To: Access

Subject: [access] Sort numbers





Dear Sir or Madam,



In a table I have a set of number which I am trying to sort(Asending)



IE: IC-1,IC-2,IC-3,....IC-129



These numbers have been moved and I would like to make access disreguard



the "IC-" and only look at the number at the end.



Can this be do?

On design a query it does not let me specify what to leave out, only 

choose the field, and the manner of the sort.



Please help

Kenneth,



Because your IC number can have between 4 to 6 characters, if you try to

sort on the number only you will not get the results you expect.  Your

sort will look like this:



101

129

12

1

2

3



In order to get the sort to look like

1

2

3

12

101

129



The iif statement in the query must look like this:

LinkTo_No_Only:IIf(Len(IIf(InStr([LinkTo],"-")>0,Right([LinkTo],Len([Lin

kTo])-InStr([LinkTo],"-")),[LinkTo]))=2,IIf(InStr([LinkTo],"-")>0,0 &

Right([LinkTo],Len([LinkTo])-InStr([LinkTo],"-")),0 & [LinkTo])



Again I'm using the LinkTo field in the IC-GeneralInfo table.

 

John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: KennethMungwira [mailto:KennethMungwira@Y...] 

Sent: Friday, December 21, 2001 4:54 PM

To: Access

Subject: [access] Sort numbers





Dear Sir or Madam,



In a table I have a set of number which I am trying to sort(Asending)



IE: IC-1,IC-2,IC-3,....IC-129



These numbers have been moved and I would like to make access disreguard



the "IC-" and only look at the number at the end.



Can this be do?

On design a query it does not let me specify what to leave out, only 

choose the field, and the manner of the sort.



Please help

Hi All,

Here's a function that will take a value, a delimiter, and desired data type

(Integer(long) and real/Double).  If you embed the string in the query and

wrap it in the appropriate conversion function then you'll get a sortable

number that doesn't act like text. 



Here's the function - note that I have NOT added any error trapping...



Function GetNumVal(vVal As Variant, _

                   sDelim As String, _

                   Optional vType As String) As Variant

' Returns numeric value from string

  Dim iDlmLen As Integer

  iDlmLen = Len(sDelim)

  GetNumVal = Mid(vVal, InStr(vVal, _

               sDelim) + iDlmLen, 

               Len(vVal) - (InStr(vVal, sDelim)))

  If Len(vType & "") = 0 Or Left(vType, 1) = "I" Then

    vType = "I"    ' Default to Integer/Long

  Else

    vType = "D"

  End If

  

  If vType = "I" Then               ' Default is Integer(Long) value of

argument

    GetNumVal = CLng(GetNumVal)

  Else

    GetNumVal = CDbl(GetNumVal)

  End If

End Function



Here's what my query usage looks like (where my field to convert is called

[CapCode];

In this example I'm using a delimiter of "c-".   This will accept delimiters

from 1 to n long, and return numeric data types for decimal or integer of

maximum capacity(cDBL/cLng).  By wrapping the value in the converter of

preferred type (cDbl or cLng) you get the sortable value since the function

itself returns a Variant.



SortedOnNumval: CLng(GetNumVal([CapCode],"c-"))



>>> John Ruff 12/21/01 09:19AM >>>

Kenneth,



Because your IC number can have between 4 to 6 characters, if you try to

sort on the number only you will not get the results you expect.  Your

sort will look like this:



101

129

12

1

2

3



In order to get the sort to look like

1

2

3

12

101

129



The iif statement in the query must look like this:

LinkTo_No_Only:IIf(Len(IIf(InStr([LinkTo],"-")>0,Right([LinkTo],Len([Lin

kTo])-InStr([LinkTo],"-")),[LinkTo]))=2,IIf(InStr([LinkTo],"-")>0,0 &

Right([LinkTo],Len([LinkTo])-InStr([LinkTo],"-")),0 & [LinkTo])



Again I'm using the LinkTo field in the IC-GeneralInfo table.

 

John Ruff - The Eternal Optimist :-)







-----Original Message-----

From: KennethMungwira [mailto:KennethMungwira@Y...] 

Sent: Friday, December 21, 2001 4:54 PM

To: Access

Subject: [access] Sort numbers





Dear Sir or Madam,



In a table I have a set of number which I am trying to sort(Asending)



IE: IC-1,IC-2,IC-3,....IC-129



These numbers have been moved and I would like to make access disreguard



the "IC-" and only look at the number at the end.



Can this be do?

On design a query it does not let me specify what to leave out, only 

choose the field, and the manner of the sort.



Please help

There is a very easy way to sort the data as required.  It does, however,

require some SQL coding so you will NOT be able to limit yourself to the

query designer.



I'm going to assume that you want all fields in the table TestOne and want

to sort on the field FieldTwo.



Using the query designer create your query and sort ascending on the desired

field FieldTwo.  This of course will NOT work.



Now here's the trick!!!



Switch to the SQL window for the query.

Modify the ORDER BY clause as follows:

At the start:

ORDER BY TestOne.FieldTwo;



Change it to:

ORDER BY CLng(Mid(TestOne.FieldTwo,4));



That's all there is to it!!!



Now run the query.



Rand



-----Original Message-----

From: KennethMungwira [mailto:KennethMungwira@Y...]

Sent: Friday, December 21, 2001 10:54 AM

To: Access

Subject: [access] Sort numbers



Dear Sir or Madam,



In a table I have a set of number which I am trying to sort(Asending)



IE: IC-1,IC-2,IC-3,....IC-129



These numbers have been moved and I would like to make access disreguard

the "IC-" and only look at the number at the end.



Can this be do?

On design a query it does not let me specify what to leave out, only

choose the field, and the manner of the sort.



Please help

Hello Kenneth,



As a mere amateur I have some reservation about putting forward my simple 

solution on a network of real experts (to whom I am always grateful for 

the advice I often receive).  However, perhaps just putting an extra 

calculated field in your query and sorting on that will suffice? The 

calculated field I tried is Expr2: CInt(Mid([field1],4)) where field one 

contains all your numbers IC-1 IC-2 IC-3 etc.



Hope this helps. Best wishes,

Clive







> Dear Sir or Madam,

> 

> In a table I have a set of number which I am trying to sort(Asending)

> 

> IE: IC-1,IC-2,IC-3,....IC-129

> 

> These numbers have been moved and I would like to make access disreguard 

> the "IC-" and only look at the number at the end.

> 

> Can this be do?

> On design a query it does not let me specify what to leave out, only 

> choose the field, and the manner of the sort.

> 

> Please help

My suggestion is also to add a calculated field to the query and sort on 

it, but I suggest you use some built-in functions ABS and VAL:

"ABS(VAL(Char_Field)) AS SortKey".

The VAL function ignores leading characters and takes the numeric value 

that it finds, and the ABS function takes the absolute value, because 

with "IC-25", VAL("IC-25") = -25.

... AL





> 

> > Dear Sir or Madam,

> > 

> > In a table I have a set of number which I am trying to sort(Asending)

> > 

> > IE: IC-1,IC-2,IC-3,....IC-129

> > 

> > These numbers have been moved and I would like to make access 

disreguard 

> > the "IC-" and only look at the number at the end.

> > 

> > Can this be do?

> > On design a query it does not let me specify what to leave out, only 

> > choose the field, and the manner of the sort.

> > 

> > Please help