Increment custom value

[msuguy71]

Hi all,

Table: CCTLog


Form: CCTLog
Text box: Run#

I am trying to increment a custom incident number in the form of YY-XXXX (ie: 13-0011). I need to increment the number part by 1 each time a new record is made, and the two digit year part needs to reflect the current year.

I have the following formula in the default value entry of the data tab of the text box 'Run#'

=Format$(Right$(Year(Date())),("[Run#]","CCTLog"),2) & "-" & Format$(Val(Right$(DLast("[Run#]","CCTLog"),4)+1),"0000")

I get an error saying "The expression you entered has a function containing the wrong number of arguments."

I can't figure out what I am doing wrong.

Can someone help?

Thanks,

Rick

[lfpm062010]

You are close. Two things.
1) I would use "DMax" instead of "DLast".
2) I put extra code to check for Null if the table has no data or when DMax returns Null value.

=Format$(Right$(Year(Date()),2),"00") & "-" & Format$(Val(Right$(IIf(IsNull(DMax("[Run#]","CCTLog")),"0000",DMax("[Run#]","CCTLog")),4))+1,"0000")

[msuguy71]

Ifpm062010,

Hi, thanks for the reply. Sorry about not getting back sooner. I put your formula in the default value entry but I am still getting #Error when starting a new record. Do I have to change another entry in the form properties? The property sheet text format is set to plain text and I left the input mask blank thinking what I had in there was causing my problem.

Rick

[June7]

Do you want 0000 entered if this is the very first record of table? or should it be 0001?

Part of the issue with IIf expression is that every part must be able to be evaluated, even the part that doesn't return value.

The = sign is not needed in DefaultValue property.

Do you want the sequence to start over each year?

I do something very similar to this but I wrote a VBA function to handle.

Generating a custom unique identifier is common topic in forum.

http://forums.aspfree.com/microsoft-...ta-403208.html

https://www.accessforums.net/access/...ers-21361.html

[msuguy71]

June7,

Hi, thanks for the reply. The sequence should start with YY-0001 at each year and increment by 1 each call. I think I had looked at similar posts before, and was not sure how to adapt it for my situation (I am still learning VBA). In the first link, do I put both sets of the VBA code in or do they each do the same thing in a different way. I think I can adapt the second one. I am going to work on that.

I removed the = sign and I made the first 0000 into 0001 and I am still getting the error.

Rick

[June7]

The first procedure in the first link is to demonstrate how I use the function that generates the unique ID. It is the function you can adapt to your situation. Where you use it is up to you.

Not sure where your expression errors, but try:

=Format$(Right$(Year(Date()),2),"00") & "-" & Format$(Val(Right$(Nz(DMax("[Run#]","CCTLog"),"0000"),4))+1, "0000")

[msuguy71]

The first procedure in the first link is to demonstrate how I use the function that generates the unique ID. It is the function you can adapt to your situation. Where you use it is up to you.

Not sure where your expression errors, but try:

=Format$(Right$(Year(Date()),2),"00") & "-" & Format$(Val(Right$(Nz(DMax("[Run#]","CCTLog"),"0000"),4))+1, "0000")

So...I feel like an idiot. I mis-spelled CCTLog in my table and form names. I spelled it CTTLog by mistake. The first formula works great. Sorry about the confusion, and thanks for all the help everyone.

Rick