04-12-10, 22:01 #1Registered User
- Join Date
- Oct 2009
Unanswered: How to Find MAX value from the text field
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 ?
04-12-10, 23:06 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
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
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
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