How to build a number?

[George Too]

Hello all,
I need help building a number. My table has a field which can hold any of three values:
1. a four digit number incremented by one or
2. a combination of a four digit number plus a letter at the end or
3. the word 'INFO'

I want to be able to generate the next available number. I have been using the Left() function but it obviously only returns 'INFO' as the highest number.

How can I get a real number instead of 'INFO'?

Thanks,

 

[WayneRyan]

George,

I need more info here ...

How do you decide which type of "number" to generate?
Do they all live in the same field?

Wayne

 

[George Too]

Hello Wayne,

The number has to follow the sequence from a DB I inherited at work. So, the number has to be a four digit number, presently at 9400. Next number would have to be 9401...There are situations when I skip the number for that record and I just use the word "INFO" in its place. Yet, in other occasions I would use a number with a letter at the end of it, 9400B. All this numbers "live" in the same field.

Does this make sense?

Thanks,
George

 

[WayneRyan]

George,

It makes sense, but how do YOU know what kind of number the next
record will be?

Given that it's a # (like 9xxx), you can use the DMAX to retrieve the
highest number where its Like '9*' and its length is 4. This numbering
scheme will totally fall apart soon though.

Still need more info though.

Wayne

 

[George Too]

Thanks for your replies guys,

What kind of number? Well, the standard is to follow the previous number regardless of letters, it is only in rare instances when I need to enter 'INFO' instead of a number or to append a letter to the end of the number. That part would have to be done manually and I don't have an issue with that.

Now, with regard to Pat's comment, I am aware that the number will increse to 5 digits in the future but we only add less than 200 per year to this count so I figure that in 3 years we will have a better system in place
besides, the powers to be won't let me change the numbers for traceability sake.
My original concern is why DMAX picks up 'INFO' as the highest value in that field and how can I avoid it?

Thanks again,
George

 

[George Too]

Thanks Pat,
Regarding DMAX, I figured that much, what ca I do so that it ignores letter values?
Now, what do you mean by "add a new primary key"? I am not using these numbers as the primary key. They are just document numbers. The primary key is an autonumber. They are planning to get rid of Access in a couple of years anyways hence my comment. But it is worth bringing it to my boss (and, no I can't purge the data ).

In the mean time, (for my own edification and future use ) I just would like to get around the DMAX situation while they decide what to do with the whole DB (where you guys have brought valid pointers).

Thanks a bunch,
George

 

[WayneRyan]

George,

Something like this:

DMax("[SomeField]", "SomeTable", "Len([IDField]) = 4 And [IDField] <> 'INFO'")

Be aware, that DMax can return Null, and you want to add 1 to the
return value.

hth,
Wayne

 

[George Too]

Thanks Wayne, That works well. It is unfortunate though that the people who owns the DB are resisting to change the number. I did bring up the suggestions you and Pat brought out but there is some resistance. So, for the time being I'll stick to this method until they move on to other methods or programs or whatever they wanna do.

Again, thank all for your time and effort. It's people like you who make this a better world to live in...

George