AutoDate Plus number

[emmim44]

Hi all.
I am trying to create an auto number based on the current date plus a number...Means today is October 18,2007 ..should be 101807-1 ....
If there is request there, the next auto number should be 101807-2 (For the same day)...
I need help...

[Qubit]

oops

[OBP]

This database that I posted before has a form that increments (Autonumber) a special Format number, you should be able to modify it for your use.
See this Thread -
http://vbaexpress.com/forum/showthread.php?t=13669
post #8

[Qubit]

Autonumber fields cannot contain special characters.
However, if you created another field for this requirement, you could do the following:

-new field is a text field
-if the date variable in question is called dteDate and the next numeric variable is called lngNum, then the formula for getting to your format is:
Format$(dteDate, "mmddyy") & "-" & Trim$(Format$(lngnum))

To test this, goto the Immediate Pane (CTRL+G) and type the following three lines [ENTER] (or copy them here and paste them there, position yourself on each line and hit [ENTER])

lngNum = 2
dteDate = Date()
?format$(dteDate, "mmddyy") & "-" & trim$(format$(lngnum))

The Format$ function will not fail on null values and the Trim function ensures no leading spaces. This produced 101807-2 on my machine today.

If you are using a form, you can create a text control that has the above formula for the Data Source, leading with an = sign;
=format$(dteDate, "mmddyy") & "-" & trim$(format$(lngnum))

Note the the dteDate and lngNum variables would need replacing with your variable/field names.

In the case where we need to discover the next NUM to tag on the end, you can make a Public Function that returns just that ( GetNextNum() as Long). Then the formula is: format$(dteDate, "mmddyy") & "-" & GetNextNum() - this way you can contain the logic needed to get the next numeric.

Note that you would use the Trim$(Format$(.... in that function so it can't fail.

Of course, the other option is to eliminate the dash so you could use a Numeric field instead of a Text field:
val(format$(dteDate, "mmddyy") & trim(format(lngnum)))
This produced 1018072 for me.

(I would be inclined to use 4 digits for the year in the Format(.."mmddyyyy") which would give you 101820072.

Note that although you cannot type into an Autonumber field, you can set it in code. If you have a function that inserts records, you can assign a value to an autonumber field, and as long as it's not there, you're ok.


Cheers.
.Q

[emmim44]

It is not working man...
Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next
Me!confnum.DefaultValue = Nz(DMax("[confnum]", "clientrequestqry", "[date]" = "#" & Date & "#"), 0) + 1
Me!confnum.DefaultValue = Format([Date], "mmddyy") & "confnum"
End If
End Sub

[OBP]

emmim, can you post a zipped copy of the database on here, it does not need to have any data in it.
Tell me which Form your VBA code is in and I will take a look.

[emmim44]

i will do it tomorrow...

[emmim44]

The file size exceeded...I cannot upload to here....if u like I will email you...

[Qubit]

Since confnum appears to be both a field on the form/in the table, you should likely replace the quotes with square brackets.
Me!confnum.DefaultValue = Format([Date], "mmddyy") & [confnum]
Otherwise you'll get something like 10242007confnum.

Q.

[BlueTick]

Here is a sample to modify. It is set a bit different but you should grasp the idea.

Seq_Number field is A05-0001 where A is the based on the value selected in the Item Type combo box and 05 is the last 2 digits of the year (system date). I modified it to the date format you requested. The code can be modified to further suit your needs.