Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Unanswered: How to Find MAX value from the text field

    Hi-

    I have a CYC_CD field which is VARCHAR. The values of this field indicate the cycle run code For Example 0109M(January 09 run), 1209M (December 09 run) so on so forth. When I convert it to an integer and use MAX function it gives me incorrect result becuase technically 0310M is the Max run and not 0409M .

    Is there a way where I can pick up the MAX value in the text field. In my case pick up 0310M record and not 0409M record ?

    Thanks
    SM

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what I would do

    create a new feild in the Query and change the 0109M tobe a YYMM format
    make even beater YYYYMM then turn that into number them you get the MAX value

    MAX is a number thing

    where your values are TEXT Value not the same

    cast() and SUBSTRING() should help
    Last edited by myle; 04-13-10 at 00:11.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •