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,468
    Provided Answers: 10
    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-12-10 at 23:11.
    hope this help

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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