0

Sorting using parts of a field

Jet Reports Historic Posts

July 01, 2009 23:24

None

Follow

Sebastiaan,

Sorry, but you can't do "quoted formulas" on non-navision database.

The only way I can think to do it is to use the "SQL=" functionality and write a SQL query to do it for you.

Are you using a MS SQL database?

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Jet Reports Historic Posts July 01, 2009 23:24

  Good morning to you all.
  I am able to sort using a normal field - using "+FieldName", "*"
  But I am retrieving fields from a non Navision database with the following format
  1.2.3.4 (four integers seperated by period)
  I wish to sort on this field so that 1.1.1.1 is followed by 1.1.1.2 which is followed by 1.1.2.1 etc up to 9.9.9.8 to 9.9.9.9
  Currently I am using excel to extract the individual numbers using left(A1,1) etc, then sorting on those fields.
  However is it possible to do the sort in Jet as I retrieve the rows?
  
  Many thanks in anticipation!
  Dave

• 0
  
  Jet Reports Historic Posts July 02, 2009 08:28

  I assume this field type is TEXT, then sorting it with +FieldName * should work just fine.
  Except ofcourse when the number continues like 10.91.3.1 like an IP.
  
  Maybe you can build something with Quoted formulas using the formulas below, however I think this will be beyond the 255 character limit of an formula:
  
  E12 = 192.168.1.100
  1st = MID(E12,1,SEARCH(".",E12)-1)
  2nd = MID(E12,SEARCH(".",E12)+1,SEARCH(".",E12)-1)
  3rd = MID(E12,SEARCH(".",E12,SEARCH(".",E12)+1)+1,SEARCH(".",E12)-1)
  4th = MID(E12,SEARCH(".",E12,SEARCH(".",E12,SEARCH(".",E12)+1)+1)+1,SEARCH(".",E12)-1)

• 0
  
  Jet Reports Historic Posts July 03, 2009 03:51

  Thanks for the replies.
  Normally in our particular project it would not continue to the 10.192.1.1 type of range, however I am trying to write it to cover that possibility.
  
  Yes its an SQL Database, I'll try experimenting with an SQL query to get the sort
  
  Dave