Results 1 to 2 of 2
  1. #1
    mohanmoni is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2015
    Posts
    6

    Retrieve Minimum & Maximum number when we have text in column


    Hi,

    I need a query that retrieve maximum & minimum number in column when we have text in the same column.

    Thanks.

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So what is the format of the column? Test/string I imagine?
    It is generally a very bad idea to have a column of mixed values (strings/numbers).

    In order to get the maximum/minimum numeric value, you are first going to need to convert the values to a number. The following formula will convert all numbers to that numbers, and all text to 0:
    Code:
    ConvertField: IIf(IsNumeric([MyField]),[MyField]+0,0)
    where MyField is the name of your field.

    You can place criteria on this field to remove the the zero entries (note that if you may have 0 numeric entries that you want to include, we will need to get a little more creative to just remove the ones that come from the Text values (by using the IsNumeric field in a separate calculation).

    You can then use the Maximum/Minimum Aggregate Function on this calculated field we show above.

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

Similar Threads

  1. Minimum and Maximum Number
    By mohanmoni in forum Queries
    Replies: 3
    Last Post: 02-04-2015, 01:55 PM
  2. minimum number in report
    By rmiell in forum Reports
    Replies: 2
    Last Post: 10-10-2012, 02:33 PM
  3. Filter data by number as text column.
    By msadiqrajani in forum Access
    Replies: 14
    Last Post: 08-15-2012, 02:34 PM
  4. Replies: 2
    Last Post: 07-21-2012, 01:21 AM
  5. Can't see minimum or maximum buttons?!?
    By Felix_too in forum Forms
    Replies: 2
    Last Post: 12-29-2010, 10:04 AM

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