showing the number 3 as 003

[rluciano]

I created a macro that sets the value of a new CLIENT_ID
based on certain values.

Heres the expression

[Forms]![NEW_CLIENT]![Supplier File Code]+''+Format(Date(),"yy")+''+Format(Date(),"mm")+''+'000' & [Forms]![NEW_CLIENT]![Total Clients]+1

I want the CLIENT_ID to follow this convention

Example
Supplier File Code = AA
Total # of Clients for Supplier AA=100

New CLIENT_ID should be
AA0707000101

First Characters will be set is the supplier file code, next two digits are the year formatted as such, next two digits are the month formatted as such. After that I need 6 digits to accomodate for the client number. I am setting the value of the CLIENT_ID with the expression shown above. Any suggestions on how to keep the six digits while I set the next client number by (Total Clients]+1 if lets say the next client number would be 3. The way I have it now, the result is:

AA07070003

A different way is all good too.

 

[rluciano]

I was able to solve this problem for the most part by using conditions in the macro. For Ex.

Forms![NEW_CLIENT]![Total client]+1 Between 1 AND 9

Then

[Forms]![NEW_CLIENT]![Supplier File Code]+''+Format(Date(),"yy")+''+Format(Date(),"mm")+''+'00000' & [Forms]![NEW_CLIENT]![Total Clients]+1

and so on, so if the value was between 10 and 99 it would pad it with '0000'. It seems to work well except for when [Total Client]+1=10 or 99 becasue it would generate a seven digit number instead of 6. I dont understand because I thought using Between N And X was inclusive. Could use some insight on that,

 

[ESquared]

First, why not use a four-digit year? Yeah, yeah, I know, it's not necessary. But it's best practice. How do you know it won't go international some day? Avoid confusion as much as possible. Are you sure everyone will know, just by looking at it, that 0807 is July 2008 and not August 2007?

Second, when I do numbered stuff like this I don't try to pad it because that limits its lifetime. I use separators or type changes:

AA200707-3
ABC200707-101
TUVWXYZ200707-123456789 ' see... unlimited future expansion

Third, do NOT use

[Forms]![NEW_CLIENT]![Total Clients]+1

... unless you plan for the application to never be used by more than one person at a time. You need a Supplier table with a LastClientNumber column. To get a new one, you do this:

CREATE PROCEDURE CreateClientNumber @SupplierID int
AS
UPDATE Supplier
SET
   @NewClientNumber = LastClientNumber + 1,
   LastClientNumber = LastClientNumber + 1
WHERE
   SupplierID = @SupplierID

RETURN @NewClientNumber

Although using the return value is cheating and you should probably use an OUTPUT variable instead to reserve the return value to indicate whether an error has occurred. But I have cheated like this on really simple procedures and had no problem. You can always RAISERROR().

Fourth, the format function takes whatever format you want:

Format(Date(),"yyyymm")
Heck, you can do Format(Date(), "yymmyymmyymm") if you feel

And finally, to answer the exact question you asked:

Right("00000" & [Forms]![NEW_CLIENT]![Total Clients] + 1, 6)

or

Format([Forms]![NEW_CLIENT]![Total Clients] + 1, "000000")

But like I said do NOT do that.

If most of this isn't making sense then you need to take a class. The Right() and Left() functions are such basics in any programming language that not knowing them (or equivalents) means you're really just getting started. Do yourself a favor and accelerate your process by getting training. You'll save a ton of time and frustration all around.

[COLOR=black #d0d0d0]When I walk, I sometimes bump into things. I am closing my eyes so that the room will be empty.[/color]

 

[rluciano]

Thanks for the info E.
I used the two digit date format because thats how the client id's were formatted prior to me getting there. Granted it was a manual system(paper files, nothing in a pc except for rudimentary client lists in excel and word) and it would have been best to suggest that, and I also agree with the way you suggested the entire naming convention for the id as far as not limiting it like it is , again something I could have advised. Everyone in the company does know the two digit year and month(Small company). I would very much like to think this company would be in danger of having more than 999,999 clients, at this point, I doubt it. Still would be better the other way i agree.

Everything you said I was kind of familiar with but I do need to take a class because as you say I should have known that.

Because I am wet behind the ears, why do you say the way I currently getting the new id is only good for a one user situation? I am assuming you say that because if one user is currently in the process of entering a client and that new Id has not yet been committed, if someone else attempts to enter a client, that macro would issue the same id, thus causing a commit error?
I have a table called Suppliers, do you suggest I add this LastClientNumber column to this table ?
Could you expound on the differences between the two ways when you get a chance?

Thanks for your help in this and the advice you pointed me to about the dcount was helpful as well.

 

[rluciano]

the issue i reported in my first solution was because I forgot to add the +1 to the expression used in my conditions of the macro....(doh)

 

[rluciano]

Let me see if I understand,

in your procedure, it looks like after you define the value of the @NewClientNumber variable, that you insert new client number into the LastClientNumner column, so if someone else would try to add a client, unless they did it nanosconds apart wouldn't cause a commit error by violating the pk constraint for duplicates error?

How about if I were to just save the record after the client id was created the way i did, before the user would continue to input any other information. Would this be equivalent ?

I am not being stubborn in trying to use my own, incorrect way, just trying to understand.

 

[rluciano]

Hey Esquared, how about this.

How about if I run code in this macro prior to using the newly generated Id, that will insert this new Id into the LastCLientNumber table. Will this resolve the issue of the current method only being useful to one user scenario ?