Next number in line

Hi,

I have a proposal app I use to make quotes for clients (ACCESS and VB.net), I was following an order to my proposal numbers (TR-018-892) and I was ordering my table in DESC (with the binding source) by the 892 part of the string; now the problem is I have reached 1000 proposals and the DESC function in my bindingsource no longer recognizes 1000 as the highest number, it always thinks 999 is the highest number, so I can longer add a new proposal passed 999.

Is there a way I can get vb.net to recognize 1000 as the highest number so that I may continue my proposal from 1000, 1001,1002, etc...?

Thank you.
FCapoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi FCapo;

It may be that the field in the Access database table will only support a length XX-XXX-XXX, 10 characters, that will hold something like TR-018-892. At the end you may also need to change the VB.Net application to support the longer field. Can you check to see how that field is defined in the database?
0
Paul JacksonSoftware EngineerCommented:
If you are ordering on the last part of the string how are you separating it out from the rest of the string, can you show us the relevant SQL. It may that you can use the VAL function on the separated out part of the string to turn it into a number and then DESC will work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndyAinscowFreelance programmer / ConsultantCommented:
This looks like you are constructing the ID from two (or three) fields and the three digit part is a string.
Convert that three digit part to a number and it will sort correctly.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
So the numbers are "TR-018-1000", "-1001" etc.
You can sort those by:

Select *
From Quotes
Order By Left([QuoteNo], 6), Val(Mid([QuoteNo], 8))

Open in new window

0
Paul JacksonSoftware EngineerCommented:
The first comment to describe using VAL to convert the string part to a numeric in order to allow sorting on the last part of the string was the 2nd comment in the question proposed by Paul Jackson. This solution was then further expanded by the next two comments by AndyAinscow and Gustav Brock. Despite Gustav Brock's assertion that his SQL is the solution it is only expanding on the ideas presented before and actually is not a correct solution as the asker specified he wanted a DESC sort on the final part of the string.
Points should be split 500 Paul Jackson, 250 AndyAinscow, 250 Gustav Brock.
0
AndyAinscowFreelance programmer / ConsultantCommented:
I agree with paul.
0
Gustav BrockCIOCommented:
I'm so sorry. Wrote the code and missed desc. How could I?
So:

Select *
From Quotes
Order By Left([QuoteNo], 6) Desc, Val(Mid([QuoteNo], 8)) Desc

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.