Adding 1 to a number...Project Numbers 1

[roystreet]

Hello,
I have a system in which makes project number by running a vb function.

Here is the function below:
frm.txtUniProjNum = Format(Nz(DMax("[tbl_MasterProj].UniProjNum", "tbl_MasterProj"), 0) + 1, "0000")

UniProjNum is a text field in tbl_MasterProj. The numbering has worked great for awhile. As you can see the numbering convention is "0000" so the numbers will have leading zeros. Recently the numbering convention must have 5 numbers instead of 4. I thought, I could simply update this line of code to "00000" for it to add another leading zero. It worked one time correctly, add the next incremental number. After the first time, every time I ran the code to generate a new number, it would continue to give me the same number over & over. Is there a reason why it would only do it once & then it would continue to use the same number over & over again?

For example, last number using the 4 digit convention is "1387", now after 5 digit convention the next number generated is "01388" <Perfect so far>, next number it generates after that is "01388"...So on & so forth.

Any thoughts?
Thanks,
~roystreet

 

[PHV]

frm.txtUniProjNum = Format(Nz(DMax("Val(UniProjNum)", "tbl_MasterProj"), 0) + 1, "00000")


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[roystreet]

Hello PHV,
....I appreciate your help. I keep getting this error now though

[blue]"3464 Data Type mismatch in criteria expression"[/blue]​

I'm trying to figure it out, there seems to be one little thing wrong & I'm not sure what it is.

Thanks,
~roystreet

 

[PHV]

And this ?
frm.txtUniProjNum = Format(Nz(DMax("Int(UniProjNum)", "tbl_MasterProj"), 0) + 1, "00000")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[roystreet]

PHV...
It appears that you did it! Great Job!! That was the perfect string. Was the change you made to integer?

I appreciate your help - Greatly!

~roystreet

 

[MichaelRed]

simple, effective ... and somewhat illogical?

It should NEVER be necessary to store the 'leading zeroes'. The use of the integer or long value would make the whole process simplier, and the leading zeros could be used only for the actual display.

MichaelRed

 

[roystreet]

Hello Michael,
Actually there are cases when leading zeros are a requirement. There is a lot of background information which I couldn't possibly discuss here. I have made a system that pulls together over 75 years of data. These records beginning with simple hard copy documents had similar, but different numbering conventions. I will give an example of how leading zeros can make an incredible difference. A project made in 1920 was numbered SA02AK-A, a project later in the 50's was numbered (Under a new convention) SA002AK-A. Later in the 90's another project was made (under newer convention) SA00002AK-A. 3 distinct projects, but very similar numbering. People all over the world, but mostly in the US that have had these projects completed for them (or are being currently worked), have official documentation that references these numbers. They can't be changed. I can not force (Which would be a miserable failure & foolish on my part), I can't try to force them to update every document that is out there to match the newer numbering convention.
Part of the beauty of developing a useful system is flexibility in dealing with old records & creating new ones.
...I can only work with what I've been given & I'm glad to be able to create something that is very useful to them.

So, the word never is quite strong when there are cases that you may not be aware of. Although I do understand where you are coming from & I hope you can understand what type of data I must deal with in making this living system work. In a new system that never had to deal with various conventions this wouldn't necessarily be an issue at all.

If you have a different method, please let me know. Briefly I will state my needs:
The end project number contains 4 seperate fields. An example: 1st field would have "SA", 2nd would have numerical value (Above listed), the 3rd would have "AK", & the 4th would have "A" The hyphen, yes can be purely cosmetic & doesn't need to be it's own field.
.
Some projects would need a NM instead of an AK or the last "A" sometimes needs to be a "Q" when the project was initiated. There are other values that could be placed there also.

I an definitely open for discussion if you have further ideas on the matter. I am always willing to learn a better method of doing things. Less coding & more simplicity in design can make for a smaller system footprint & easier for the designer (Or who would fill your place) to understand. Remember, I'm having to deal with new & old records - Ones I had no control over & made before modern computers & before centralizing of data.

Thanks,
~roystreet

 

[MichaelRed]

I'm quite sure I am missing the point. Entirely.

In your original post, the example was a 'numeric' string with the zeros prepended to the value garnered from the string with the dmax function.

Thus, the internal storage of the value as a string only complicates the processing by requiring the conversion to the numeric 'value', reformatting this with the leading (zero) characters and (aparently) storing the resultant string as the project number.

'Autonumber' fields in MS Access provide the (dare i mention it?) the automatinc increment of the value portion, an further provide at least a reasonable protection aginst duplication of the value which might occur in the more manual process you are usiing.



Your second example, the project 'mumber' uses an alphanumeric string with some embedded zero characters.

I did not notice an expllicit breakdown of this project number into seperate (stored) segments, thus (at least to my mind) rendering it irrelevant to the request from the original post.

However, in the interest of full disclosure ...

Storing the 'original' project numbers as complete entities would have (aparently) prevented duplication of the specific examples without recourse to the padding routine. This, however would not prevent the storage of duplicate values where they existed in the disseperate imports mentiond.

Breaking them into segments could have been done with the 'numeric' segments stored either as text (again avoiding the need for padding) or as numeric fields, which would equire the pad - and incuring hte penalty of also storing the segnment length as well additional code to use the correct pad. But, alas, this would also not have prevented the duplication of values originating from disseperate imports.

Please feel free to continue my education in this matter.

PS there are routined posted in these fora which illustrate 'incrementing' alpha-numeric strings)



MichaelRed

 

[roystreet]

Hi Michael,
I appreciate your comments on this. I appreciate your response & hopefully you understand I wasn't speaking sarcastically towards you. I am serious about learning how to do this better & looking forward to simplifying this system even more. And broadening the "power" of what it can provide for my users. I am willing to change core aspects of my system if I see that it's a better method of doing things. I'm not sure I understand if you are being sarcastic with me?
I am definitely willing to learn, & that's how I got where I'm at. I learn more by putting my hands on plow & posting specific questions & reading the answers than by books - Although I have learned from them also. I also hope that I can provide some incite on things I've learned as to be a contributor & not always a "questioner", even if in a small way. I want to be able to help if I can.

Your post is confusing to me & I absolutely know it's because of my lack of understanding. I can tell that you are much more knowledgeable than I. I will probably chew on it for awhile so that I can gain more from it.

In the case of this database that I've made, it appears after much testing, posting, etc that the method I'm using has worked quite well & reliably, until a new numbering convention was required. I have not found a way in which Auto-number will do the trick. (Although I have tried) I do have portions of the system in which I use auto-numbering, but not for this type of usage.

The whole project number (all of the separate fields or portions I listed) aren't stored in one field as each field is also used for multiple uses that don't even necessarily require the numerical portion of the project number. Ie different project number prefixes (SA instead of ST) trigger various code or are used for querying, duplication, searching, etc. Plus, this method allows easy flexibility in changing just portions of the whole number. Yes, they do appear as one whole field for cosmetic purposes in many places of the system. I do store each field in a text field as number fields would always "throw out" the zeros that weren't "needed" according to Access.
I didn't post all of the aspects, requirements, or how things are performed in this system as I try to minimize the time required for others to assist. I know though that-that can hinder those helping me. I know others need help just like me & I don't want to take up a lot of time from them also.

Once again I hope you didn't think I was being sarcastic, nor questioning your intelligence. I simply thought that, by what I had learned thus far, either by my own testing & others thoughts on these things in the past, that I found a situation in which a different method would require the method I've been using & that using the word never wasn't always applicable in some cases.

~roystreet