Results 1 to 14 of 14
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Sort records on numeric values stored in a text field

    I would like to sort records in a query on numeric values in a text field and if the field contains a K multiply by 1000 if it contains an M multiply by 1000000. I can use this Expr1: IIf([P_Value] Is Null,0,Val([P_Value])) to get the field to sort in numerical order, but I need more. Field would contain a value as 100, 220, 1.2K, 100K, 1M etc. Sort order I would like is 1,2,10, 200, 500, 900, 1K, 1.6K, 1M etc.
    Thanks!
    Tom



  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? if the field contains a K multiply by 1000 if it contains an M multiply by 1000 ??

  3. #3
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    sorry my error!
    if the field contains a K multiply by 1000 if it contains an M multiply by 1000000

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    these characters are all in one field? If so, it would take a rather lengthy expression assuming it can be done at all, due to the variables involved and the mixed data. That's because
    k100*100 = 0

    EDIT - by "if at all" i meant as an expression. A function call from query may be the way to go.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try
    Code:
    SELECT 
    IIf(InStr([P_value],"K")>0,Val([P_value])*1000
    ,IIf(InStr([P_Value],"M")>0,Val([P_Value])*1000000,[P_Value])) AS SortVal
    , TblTom.P_value
    FROM TblTom;
    My Testdata

    id P_value
    1 10
    2 20
    3 6.2K
    4 2.1K
    5 3M
    6 91.45M



    SortVal P_value
    10 10
    20 20
    6200 6.2K
    2100 2.1K
    3000000 3M
    91450000 91.45M

    Couldn't get it to sort without error.

    Added extra field to tblTom, adjusted the query to
    Code:
    UPDATE TblTom 
    SET TblTom.ExtraFld = IIf(InStr([P_value],"K")>0,Val([P_value])*1000,IIf(InStr([P_Value],"M")>0,Val([P_Value])*1000000,[P_Value]));
    Then sort ExtraFld ascending. (Rube Goldberg approach)

    id P_value ExtraFld
    1 10 10
    2 20 20
    4 2.1K 2100
    3 6.2K 6200
    5 3M 3000000
    6 91.45M 91450000
    .

  6. #6
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Will give it a try, Thanks

    Sort order should be:
    10 10
    20 20
    2.100 2.1K
    6.200 6.2K
    3000000 3M
    91450000 91.45M

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Then use this
    Code:
    SELECT TblTom.id, TblTom.P_value
    FROM TblTom
    ORDER BY TblTom.ExtraFld;
    Result:

    id P_value
    1 10
    2 20
    4 2.1K
    3 6.2K
    5 3M
    6 91.45M



    I have extrafld in the table; filled it via query, then sort the table with this set up

    -display the fields you want/need (P_Value)
    -sort on the numeric ExtraFld

  8. #8
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Thanks, will work on it. I see what I need.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    10-4, I couldn't get it to sort via query. You could probably use recordset(s) and/or array, but with an extrafield in the table, it could be updated as/when needed. I guess the solution really depends on detailed requirement. As I said --sort of a Rube Goldberg answer.

  10. #10
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    #5 is 95% good, but some values don't sort correctly.

    Results from #5 and #7 sort order
    ExtraFld P_Value
    1 1
    1 1
    1 .68
    2 2.2
    2 1.6
    2 1.5
    The rest seems OK

    I think what I need is to convert P-Value into a Number in ExtraFld and then sort on that number. I would like to do it in a query or VBA. Some Values will be decimal, some K = *1000, some M= *1000000, and some Values maybe blank, if I enter all Letters, leave ExtraFld blank. May have some other criteria on P_Value data. I believe Number field should sort correctly.
    Thanks,
    Tom

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree Tom. I made the ExtraFld a Double datatype. Then added some new data records.
    Then ran query1 then query2.

    This now tblTom
    id P_value ExtraFld
    1 10 10
    2 20 20
    3 6.2K 6200
    4 2.1K 2100
    5 3M 3000000
    6 91.45M 91450000
    7 .73 0.73
    8 .214 0.214

    Query1
    Code:
    UPDATE TblTom SET TblTom.ExtraFld = IIf(InStr([P_value],"K")>0,Val([P_value])*1000,IIf(InStr([P_Value],"M")>0,Val([P_Value])*1000000,[P_Value]));
    Query2
    Code:
    SELECT TblTom.id, TblTom.P_value
    FROM TblTom
    ORDER BY TblTom.ExtraFld;
    Query2 Output
    Code:
    id	P_value
    8	.214
    7	.73
    1	10
    2	20
    4	2.1K
    3	6.2K
    5	3M
    6	91.45M

  12. #12
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Sorry did work as expected

    I didn't see any changes? newest Query 1 & 2
    This is what I got.

    Click image for larger version. 

Name:	SortExtraFld.jpg 
Views:	7 
Size:	103.2 KB 
ID:	38667
    I[IMG]file:///C:/Users/MadTo/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg[/IMG]

  13. #13
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    OK, didn't know about double setting in Field size, fiqured it out and seems to work as needed!
    Thanks! always learning something new!
    Tom

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 01-16-2019, 02:13 PM
  2. Replies: 15
    Last Post: 12-11-2017, 07:29 PM
  3. return most recent records by date stored as text
    By mindbender in forum Queries
    Replies: 3
    Last Post: 04-27-2016, 07:00 AM
  4. Replies: 19
    Last Post: 09-25-2015, 10:26 PM
  5. Justify numeric text field
    By tpcervelo in forum Queries
    Replies: 2
    Last Post: 01-27-2011, 03:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums