I have text box "ID" and it is text and I would like to custom it to be auto
number as the following format:
01/2009
02/2009
.....
and so on.

How to do that???

"Custom" and "Auto" are mutually exclusive. You can write code to assign a
default value to the textbox each time a new record is added to a form. You
might consider whether storing what appears to be a date as a text field is
a good idea. If you want the default value for this field to be the first
day of the current month, you could set a default value in the table design
mode: DateSerial(year(date()), month(date()), 1).

"Jon" <(E-Mail Removed)> wrote in message
news:9DBA4177-0AE0-45CA-88E8-(E-Mail Removed)...
>I have text box "ID" and it is text and I would like to custom it to be
>auto
> number as the following format:
> 01/2009
> 02/2009
> ....
> and so on.
>
> How to do that???

Assuming an ID field (Number) and a MyDate field (Date/Time), you could bind
a text box to the ID field and have something like this as it's Default
Value property:

=Nz(DMax("[ID]","[TableName]","Year([MyDate]) = " & Year(Date())),0) + 1

Hide the text box if you like. In an unbound text box, set the Control
Source to:

=Format([ID],"00") & "/" & Year([MyDate])

You could try something like this instead, which should result in the text
box being blank until ID has a value:

=Format([ID],"00") + "/" + Year([MyDate])

Or you may need to do:

=IIf([ID] Is Null,"",Format([ID],"00") & "/" & Year([MyDate]))

Note that you do not need to add the year to the field if there is a date
field in the record. The DMax statement looks for the largest value in ID
where the year in MyDate (the date field) equals the current year, and adds
one to that value. If it is the first record of the year, the DMax
statement returns Null, which the Nz function converts to 0. Adding one to
that makes ID = 1 for the year's first record. All of this assumes you mean
to start the numbering from 1 each year.

"Jon" <(E-Mail Removed)> wrote in message
news:9DBA4177-0AE0-45CA-88E8-(E-Mail Removed)...
>I have text box "ID" and it is text and I would like to custom it to be
>auto
> number as the following format:
> 01/2009
> 02/2009
> ....
> and so on.
>
> How to do that???
>

Hi,
01 & 02 are not a date, they are a sequence number? Please advice???


"Paul Shapiro" wrote:

> "Custom" and "Auto" are mutually exclusive. You can write code to assign a
> default value to the textbox each time a new record is added to a form. You
> might consider whether storing what appears to be a date as a text field is
> a good idea. If you want the default value for this field to be the first
> day of the current month, you could set a default value in the table design
> mode: DateSerial(year(date()), month(date()), 1).
>
> "Jon" <(E-Mail Removed)> wrote in message
> news:9DBA4177-0AE0-45CA-88E8-(E-Mail Removed)...
> >I have text box "ID" and it is text and I would like to custom it to be
> >auto
> > number as the following format:
> > 01/2009
> > 02/2009
> > ....
> > and so on.
> >
> > How to do that???
>
>

On Tue, 28 Apr 2009 01:11:02 -0700, Jon <(E-Mail Removed)> wrote:

>I have text box "ID" and it is text and I would like to custom it to be auto
>number as the following format:
>01/2009
>02/2009
>....
>and so on.
>
>How to do that???

So you will have no more than 99 records in any year?

As noted by others, this is A VERY BAD IDEA. Storing data (a year) as part of
an ID is not helpful; data is data and should be stored in its own field.

But if you really want this you can do your data entry using a Form; in the
Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strID As String
Dim iNext As Integer
strID = NZ(DMax("[ID]", "[tablename]", "[ID] LIKE '*/' & Year(Date())"),"00/")
iNext = CInt(Left(strID, 2))
If iNext >= 99 Then
Cancel = True
Msgbox "Shut up shop for the year, out of ID values", vbOKOnly
Else
Me!txtID = Format(iNext + 1, "00") & "/" & Year(Date()))
End If
End Sub
--

John W. Vinson [MVP]

Hi John and thanks for reply,

When I applied this code on my form the following error appears:
Run time error ‘94’:
Invalid use of Null

Please advice?




"John W. Vinson" wrote:

> On Tue, 28 Apr 2009 01:11:02 -0700, Jon <(E-Mail Removed)> wrote:
>
> >I have text box "ID" and it is text and I would like to custom it to be auto
> >number as the following format:
> >01/2009
> >02/2009
> >....
> >and so on.
> >
> >How to do that???
>
> So you will have no more than 99 records in any year?
>
> As noted by others, this is A VERY BAD IDEA. Storing data (a year) as part of
> an ID is not helpful; data is data and should be stored in its own field.
>
> But if you really want this you can do your data entry using a Form; in the
> Form's BeforeInsert event put code like
>
> Private Sub Form_BeforeInsert(Cancel as Integer)
> Dim strID As String
> Dim iNext As Integer
> strID = NZ(DMax("[ID]", "[tablename]", "[ID] LIKE '*/' & Year(Date())"),"00/")
> iNext = CInt(Left(strID, 2))
> If iNext >= 99 Then
> Cancel = True
> Msgbox "Shut up shop for the year, out of ID values", vbOKOnly
> Else
> Me!txtID = Format(iNext + 1, "00") & "/" & Year(Date()))
> End If
> End Sub
> --
>
> John W. Vinson [MVP]
>

John's code below should work for assigning the sequence number in your
present scheme. A better design would be to split the sequence number and
the year into two fields. Data fields should be atomic, without any meaning
to sub-sections of the field. So a long integer year field, with a
meaningful name like orderYear or meetingYear or whatever it is (since
'year' is a reserved word and should not be used as a field name) and a
second long integer for annualSequenceNumber. That avoids the issue John
mentioned of being limited to 99 rows per year and programming against the
data is clearer because the data is simpler. You can use an expression (in a
query, form or report) to display your current ID:
OriginalID = Format([annualSequenceNumber], "00") & "/" & [orderYear]

To get the next data values, you could use code like this:
Dim thisYear as Long
Dim seqNumNext as Long
Dim strWhere as string

thisYear = Year(Date())
strWhere = "[orderYear]=" & thisYear
seqNumNext = 1 + nz(dMax("[annualSequenceNumber]", "[tablename]", strWhere),
0)

In the table design mode you could set the default value for orderYear to be
Year(Date()), so that would automatically be entered with the correct value.
I don't think you can use the expression for seqNumNext as a default value,
but you could try a variation like this and see if it works:
1 + nz(dMax("[annualSequenceNumber]", "[tablename]", "[orderYear]=" &
Year(Date())), 0)


"Jon" <(E-Mail Removed)> wrote in message
news:EC604C19-F0CF-4F52-96CE-(E-Mail Removed)...
> Hi,
> 01 & 02 are not a date, they are a sequence number? Please advice???
>
>
> "Paul Shapiro" wrote:
>
>> "Custom" and "Auto" are mutually exclusive. You can write code to assign
>> a
>> default value to the textbox each time a new record is added to a form.
>> You
>> might consider whether storing what appears to be a date as a text field
>> is
>> a good idea. If you want the default value for this field to be the first
>> day of the current month, you could set a default value in the table
>> design
>> mode: DateSerial(year(date()), month(date()), 1).
>>
>> "Jon" <(E-Mail Removed)> wrote in message
>> news:9DBA4177-0AE0-45CA-88E8-(E-Mail Removed)...
>> >I have text box "ID" and it is text and I would like to custom it to be
>> >auto
>> > number as the following format:
>> > 01/2009
>> > 02/2009
>> > ....
>> > and so on.
>> >
>> > How to do that???

Quite similar to what I suggested yesterday. I'm not sure of the exact
limitations of what functions can be used in the Default Value in a table,
but I don't think any domain aggregate functions can be used.

"Paul Shapiro" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> John's code below should work for assigning the sequence number in your
> present scheme. A better design would be to split the sequence number and
> the year into two fields. Data fields should be atomic, without any
> meaning to sub-sections of the field. So a long integer year field, with a
> meaningful name like orderYear or meetingYear or whatever it is (since
> 'year' is a reserved word and should not be used as a field name) and a
> second long integer for annualSequenceNumber. That avoids the issue John
> mentioned of being limited to 99 rows per year and programming against the
> data is clearer because the data is simpler. You can use an expression (in
> a query, form or report) to display your current ID:
> OriginalID = Format([annualSequenceNumber], "00") & "/" & [orderYear]
>
> To get the next data values, you could use code like this:
> Dim thisYear as Long
> Dim seqNumNext as Long
> Dim strWhere as string
>
> thisYear = Year(Date())
> strWhere = "[orderYear]=" & thisYear
> seqNumNext = 1 + nz(dMax("[annualSequenceNumber]", "[tablename]",
> strWhere), 0)
>
> In the table design mode you could set the default value for orderYear to
> be Year(Date()), so that would automatically be entered with the correct
> value. I don't think you can use the expression for seqNumNext as a
> default value, but you could try a variation like this and see if it
> works:
> 1 + nz(dMax("[annualSequenceNumber]", "[tablename]", "[orderYear]=" &
> Year(Date())), 0)
>
>
> "Jon" <(E-Mail Removed)> wrote in message
> news:EC604C19-F0CF-4F52-96CE-(E-Mail Removed)...
>> Hi,
>> 01 & 02 are not a date, they are a sequence number? Please advice???
>>
>>
>> "Paul Shapiro" wrote:
>>
>>> "Custom" and "Auto" are mutually exclusive. You can write code to assign
>>> a
>>> default value to the textbox each time a new record is added to a form.
>>> You
>>> might consider whether storing what appears to be a date as a text field
>>> is
>>> a good idea. If you want the default value for this field to be the
>>> first
>>> day of the current month, you could set a default value in the table
>>> design
>>> mode: DateSerial(year(date()), month(date()), 1).
>>>
>>> "Jon" <(E-Mail Removed)> wrote in message
>>> news:9DBA4177-0AE0-45CA-88E8-(E-Mail Removed)...
>>> >I have text box "ID" and it is text and I would like to custom it to be
>>> >auto
>>> > number as the following format:
>>> > 01/2009
>>> > 02/2009
>>> > ....
>>> > and so on.
>>> >
>>> > How to do that???
>

On Wed, 29 Apr 2009 08:02:47 -0400, "BruceM" <bamoob_at_yawhodotcalm.not>
wrote:

>Quite similar to what I suggested yesterday. I'm not sure of the exact
>limitations of what functions can be used in the Default Value in a table,
>but I don't think any domain aggregate functions can be used.

No domain functions, no user-supplied functions, and no reference to any other
field in the same or any other table. Pretty limiting!
--

John W. Vinson [MVP]

On Tue, 28 Apr 2009 20:59:00 -0700, Jon <(E-Mail Removed)> wrote:

>Hi John and thanks for reply,
>
>When I applied this code on my form the following error appears:
>Run time error ‘94’:
>Invalid use of Null
>
>Please advice?

Sorry, I had a typo (misplaced quote) in my code. Try

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strID As String
Dim iNext As Integer
strID = NZ(DMax("[ID]", "[tablename]", _
"[ID] LIKE '*/' & Year(Date() & "')"),"00/")
iNext = CInt(Left(strID, 2))
If iNext >= 99 Then
Cancel = True
Msgbox "Shut up shop for the year, out of ID values", vbOKOnly
Else
Me!txtID = Format(iNext + 1, "00") & "/" & Year(Date()))
End If
End Sub
--

John W. Vinson [MVP]