Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 Text field max number as integer

Author  Topic 

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-10-09 : 05:09:28
I have a table, empno as a field with text datatype..
when i store the data it store like wise

101
102
...
...
999

when i get maximum number it gets 999 correctly....
after saving 1000th record the record store in the first like of the table, for ex,

1000
101
102
...
...
999

now am trying to get the maximum number it brings only the 999
but in this case i need 1000 as a maximum number....
What can i do for fetch the maximum record?

[I know number field could be better to use but this question is asked by one of my friend and he probabaly can't change the field type at this stage]

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-10-09 : 05:26:53
Actually my customer has more than one site like 7005,7002 etc.,

so am storing the value 7005/101 where 7005 is site id and 101 is reference number

if it is for 7002, we are storing 7002/101 like wise

so now what am doing is

7005/101

7005/102

.......

......

7005/999

from the above am getting the the maximum one

i wrote the query like that

"select max(eno) from the table "

it gives 7005/999

after storing the next record ie) 7005/1000

am not able to get the maximum, that is 7007/1000 for this query "select max(eno) from the table "

it fetch onle 7005/999....

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-09 : 05:54:26
Try

select max(eno*1) from the table


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-10-09 : 06:22:57
I got data type mismatch in criteria Error

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com
Go to Top of Page

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2008-10-09 : 06:47:36
select max(CLng(replace(id, '/','') )) from employee worked

Kamran Shahid
Sr. Software Engineer(MCSD.Net,MCPD.net)
www.netprosys.com
Go to Top of Page
   

- Advertisement -