Create a counter--Please HELP

[Sherly25]

I have the following problem:

(Summary)-- I need to know who to create a counter to count how many invoices were created in a specific month and add (Contatinate) this number to the end of my current invoice number in the following way: for the first invoice: RPI0306001, second, RPI0306002, etc.. Obviously at the beginning of each month this counter must once again reset to zero and start over again)

Details of what I have done :
The value of invoice number is obtained like this "RPI" & Format(Date, "yymm&quot
in a sense I create a string out of my field Date and it outputs (For instance June 2003)= "RPI0306", but I need to concatinate a counter to the end of this value, so that I can have more than one invoice every month. For the example: fist invoice in June: RPI0306001, second RPI0306002, etc..However I am not sure how I can create a counter and apply it.. my pseudocode in C++ looks like this:
(Invoice number and month are both variables that represent the fields on the database)

Invoice Number = current invoice number;
month=previous invoice number;
If Current== Month
month++; //this increments the variable month by one
else
cout<<&quot; &quot;<<current<<endl;
//this outputs the current invoice number

If this is confusing, please contact me for claryfication. If you can help me PLEASE DO...I've been working on this for over three weeks and this is not letting me move on with my work. Please help me if you can..

Thanks

 

[Sherly25]

Sorry I forgot to mention, I am using Access 2000. And obviously need help &quot;translating&quot; my ideas into VBA.. The concept is what I typed, but my experience with VBA is far too limited to understand how to create a counter and use its values for one of my fields...Please try to help me... Thanks in advance.

 

[TonyJollans]

Hi Sherly25,

Use DMax to get the highest number so far this month, and then manipulate it, something like this:

LastInvoiceNumber = Nz(DMax("InvoiceNumber", "InvoiceTable", "InvoiceNumber Like ""RPI" & Format(Now(), "yymm") & "*"""), "RPI0000000")
NewInvoiceNumber = "RPI" & Format(Now(), "yymm") & Right("00" & (Right(LastInvoiceNumber, 3) + 1), 3)

Enjoy,
Tony

 

[Sherly25]

I used your code, but I get RPI00000000 for every single one of the LastInvoiceNumber fields. Any clue as of what might be going on?

Sherly25

 

[TonyJollans]

Hi Sherly25,

Sorry, I posted that code a bit quickly last night and I should have explained it a bit more.

DMax, as posted, should find the maximum of all values in the format &quot;RPIyymm*&quot; in column InvoiceNumber in table InvoiceTable. If there aren't any it will return a null and the Nz function converts the null to &quot;RPI0000000&quot;; it looks like that is what you are getting.

So, assuming you've changed all the names to yours, the question is do you have any invoice numbers in that format for this month - the code doesn't seem to think so.

Enjoy,
Tony

 

[Sherly25]

Ok, I got that to work. However, I don't see the connection. Example: I get &quot;RPI03060001&quot; for lastInvoice number and &quot;RPI03060002&quot; for NewInvoiceNumber. But how do I compare them...?

 

[TonyJollans]

Hi Sherly25,

Isn't that what you wanted? A New Invoice Number one higher than the highest old one within the current month. I am missing something, why do you want to compare them?

Enjoy,
Tony

 

[Sherly25]

This is what the problem is: DMax is NOT WORKING. I get the same invoice number, over and over again like this:

For Example: For three invoices created in June 2003:
Last Invoice Number:
RPI0306001
RPI0306001
RPI0306001

NewInvoiceNumber:
RPI0306002
RPI0306002
RPI0306002
I want to compare them, because Dmax is not working, so I want to obtain the next number and not get stuck. Any clues what might be going wrong?

GF

 

[TonyJollans]

Hi Sherly25,

I've got to guess because it works in my little test, but ... the code I gave you checks for the highest invoice number in the current month. It is now July - in England anyway - so can you tell me what you are doing to work with June's invoices.

Enjoy,
Tony