Autonumber - with text

Any way to create a auto number field with a text field in it? I have a
field called "RecordID" which is formated as XX-XXX (08-111). Since I need
the '-' dash in the field, it must be a text field. But I want it to
autonumber and I want to pick the number where it starts.

Any chance of this??

Thanks,

Bill

Not directly, but you can make it appear that way to users on reports and
forms.

I had similar issue with a quote program. Put two field in table:

QuoteYear, Default Value = Year(Date())
This plugs the year when record is added.

QuoteNumber, Autonumber

Then on form (or report), use text box with Control Source =
=Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))

This would show, for example, 080251

In your case, add & "-" & , and you would get 08-0251


--
Dave Thompson
Allen, TX
US


"Bill" wrote:

> Any way to create a auto number field with a text field in it? I have a
> field called "RecordID" which is formated as XX-XXX (08-111). Since I need
> the '-' dash in the field, it must be a text field. But I want it to
> autonumber and I want to pick the number where it starts.
>
> Any chance of this??
>
> Thanks,
>
> Bill

Thanks Dave. I kinda figured I would have to do it that way. The only other
part of this is that I want to start counting at a certain number. Each time
I make it a autonumber it resets to 1 and adds from there. How can I set the
first number (I want to start around 08-158

Thanks

"DaveT" wrote:

> Not directly, but you can make it appear that way to users on reports and
> forms.
>
> I had similar issue with a quote program. Put two field in table:
>
> QuoteYear, Default Value = Year(Date())
> This plugs the year when record is added.
>
> QuoteNumber, Autonumber
>
> Then on form (or report), use text box with Control Source =
> =Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))
>
> This would show, for example, 080251
>
> In your case, add & "-" & , and you would get 08-0251
>
>
> --
> Dave Thompson
> Allen, TX
> US
>
>
> "Bill" wrote:
>
> > Any way to create a auto number field with a text field in it? I have a
> > field called "RecordID" which is formated as XX-XXX (08-111). Since I need
> > the '-' dash in the field, it must be a text field. But I want it to
> > autonumber and I want to pick the number where it starts.
> >
> > Any chance of this??
> >
> > Thanks,
> >
> > Bill

On Jul 4, 4:59 pm, Bill <B...@discussions.microsoft.com> wrote:
> Any way to create a auto number field with a text field in it? I have a
> field called "RecordID" which is formated as XX-XXX (08-111). Since I need
> the '-' dash in the field, it must be a text field. But I want it to
> autonumber and I want to pick the number where it starts.
>
> Any chance of this??
>
> Thanks,
>
> Bill

you mean like this?
http://www.mvps.org/access/strings/str0007.htm

Sounds like you're trying to use a so-called "smart key" (it's not a
complimentary description...)

Let me guess. The 08 is because this is 2008: next year you're going to want
09. You should have multiple fields in the table, and concatenate them
together in a query, rather than try to store them as a single field.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Bill" <Bill@discussions.microsoft.com> wrote in message
news:21B932C6-733E-4A4B-B662-AF600884187C@microsoft.com...
> Thanks Dave. I kinda figured I would have to do it that way. The only
> other
> part of this is that I want to start counting at a certain number. Each
> time
> I make it a autonumber it resets to 1 and adds from there. How can I set
> the
> first number (I want to start around 08-158
>
> Thanks
>
> "DaveT" wrote:
>
>> Not directly, but you can make it appear that way to users on reports and
>> forms.
>>
>> I had similar issue with a quote program. Put two field in table:
>>
>> QuoteYear, Default Value = Year(Date())
>> This plugs the year when record is added.
>>
>> QuoteNumber, Autonumber
>>
>> Then on form (or report), use text box with Control Source =
>> =Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))
>>
>> This would show, for example, 080251
>>
>> In your case, add & "-" & , and you would get 08-0251
>>
>>
>> --
>> Dave Thompson
>> Allen, TX
>> US
>>
>>
>> "Bill" wrote:
>>
>> > Any way to create a auto number field with a text field in it? I have
>> > a
>> > field called "RecordID" which is formated as XX-XXX (08-111). Since I
>> > need
>> > the '-' dash in the field, it must be a text field. But I want it to
>> > autonumber and I want to pick the number where it starts.
>> >
>> > Any chance of this??
>> >
>> > Thanks,
>> >
>> > Bill

Bill,
There are ways to manipulate the starting number through VBA, but I find it
easier to do this (does vary slightly if the data table has data you want to
keep; here I'll assume the data table is empty and you want the first record
added to start at a specific number):

tblDATA

Copy to tblTMP

tblTMP: Change Autonumber field to long int

Add one record to tblTMP with, for example, ID = 157 (one less than where
you really want to start)

Append tblTMP tp tblDATA (all fields)

Leave the record with ID = 157 (this is autonumber field in tblDATA) until
you add the first production record (ID = 158). Then go back and delete the
ID = 157 record.



--
Dave Thompson
Allen, TX
US


"Bill" wrote:

> Thanks Dave. I kinda figured I would have to do it that way. The only other
> part of this is that I want to start counting at a certain number. Each time
> I make it a autonumber it resets to 1 and adds from there. How can I set the
> first number (I want to start around 08-158
>
> Thanks
>
> "DaveT" wrote:
>
> > Not directly, but you can make it appear that way to users on reports and
> > forms.
> >
> > I had similar issue with a quote program. Put two field in table:
> >
> > QuoteYear, Default Value = Year(Date())
> > This plugs the year when record is added.
> >
> > QuoteNumber, Autonumber
> >
> > Then on form (or report), use text box with Control Source =
> > =Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))
> >
> > This would show, for example, 080251
> >
> > In your case, add & "-" & , and you would get 08-0251
> >
> >
> > --
> > Dave Thompson
> > Allen, TX
> > US
> >
> >
> > "Bill" wrote:
> >
> > > Any way to create a auto number field with a text field in it? I have a
> > > field called "RecordID" which is formated as XX-XXX (08-111). Since I need
> > > the '-' dash in the field, it must be a text field. But I want it to
> > > autonumber and I want to pick the number where it starts.
> > >
> > > Any chance of this??
> > >
> > > Thanks,
> > >
> > > Bill

Doug,

You are correct the 08 is for the year and I dont want to be changing it
each year. Also the numbers start over year after year. So we have a 08-001
and next year we will start with 09-001.

Not sure I'm how to accomplish this.


"Douglas J. Steele" wrote:

> Sounds like you're trying to use a so-called "smart key" (it's not a
> complimentary description...)
>
> Let me guess. The 08 is because this is 2008: next year you're going to want
> 09. You should have multiple fields in the table, and concatenate them
> together in a query, rather than try to store them as a single field.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Bill" <Bill@discussions.microsoft.com> wrote in message
> news:21B932C6-733E-4A4B-B662-AF600884187C@microsoft.com...
> > Thanks Dave. I kinda figured I would have to do it that way. The only
> > other
> > part of this is that I want to start counting at a certain number. Each
> > time
> > I make it a autonumber it resets to 1 and adds from there. How can I set
> > the
> > first number (I want to start around 08-158
> >
> > Thanks
> >
> > "DaveT" wrote:
> >
> >> Not directly, but you can make it appear that way to users on reports and
> >> forms.
> >>
> >> I had similar issue with a quote program. Put two field in table:
> >>
> >> QuoteYear, Default Value = Year(Date())
> >> This plugs the year when record is added.
> >>
> >> QuoteNumber, Autonumber
> >>
> >> Then on form (or report), use text box with Control Source =
> >> =Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))
> >>
> >> This would show, for example, 080251
> >>
> >> In your case, add & "-" & , and you would get 08-0251
> >>
> >>
> >> --
> >> Dave Thompson
> >> Allen, TX
> >> US
> >>
> >>
> >> "Bill" wrote:
> >>
> >> > Any way to create a auto number field with a text field in it? I have
> >> > a
> >> > field called "RecordID" which is formated as XX-XXX (08-111). Since I
> >> > need
> >> > the '-' dash in the field, it must be a text field. But I want it to
> >> > autonumber and I want to pick the number where it starts.
> >> >
> >> > Any chance of this??
> >> >
> >> > Thanks,
> >> >
> >> > Bill
>
>
>

As I said, you should have two separate fields (although it's not necessary
to have Year field if there's some other date field already there that you
can use as the basis for the year information). Let's assume that you do
have two fields, though. I'll call them YearField and IncrementalNumber.
(Note that you should NOT name the field Year, as that's a reserved word.
For a comprehensive list of field names to avoid, see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html )

In your form's BeforeUpdate event, use code like:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me.IncrementalNumber = _
Nz(DMax("[IncrementalNumber]", "[MyTable]", _
"[YearField] = " & Me.YearField), 0) + 1
End If

End Sub

(use your actual table name instead of MyTable)

Now, create a query that returns all the rows in MyTable. Add a computed
field to that query that concatenates the two fields for display purposes:

Right(CStr([YearField]), 2) & "-" & Format([IncrementalNumber], "000")

Use that query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Bill" <Bill@discussions.microsoft.com> wrote in message
news:A654FEB7-EBAC-48F2-BF1B-C6808BB311CE@microsoft.com...
> Doug,
>
> You are correct the 08 is for the year and I dont want to be changing it
> each year. Also the numbers start over year after year. So we have a
> 08-001
> and next year we will start with 09-001.
>
> Not sure I'm how to accomplish this.
>
>
> "Douglas J. Steele" wrote:
>
>> Sounds like you're trying to use a so-called "smart key" (it's not a
>> complimentary description...)
>>
>> Let me guess. The 08 is because this is 2008: next year you're going to
>> want
>> 09. You should have multiple fields in the table, and concatenate them
>> together in a query, rather than try to store them as a single field.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Bill" <Bill@discussions.microsoft.com> wrote in message
>> news:21B932C6-733E-4A4B-B662-AF600884187C@microsoft.com...
>> > Thanks Dave. I kinda figured I would have to do it that way. The only
>> > other
>> > part of this is that I want to start counting at a certain number.
>> > Each
>> > time
>> > I make it a autonumber it resets to 1 and adds from there. How can I
>> > set
>> > the
>> > first number (I want to start around 08-158
>> >
>> > Thanks
>> >
>> > "DaveT" wrote:
>> >
>> >> Not directly, but you can make it appear that way to users on reports
>> >> and
>> >> forms.
>> >>
>> >> I had similar issue with a quote program. Put two field in table:
>> >>
>> >> QuoteYear, Default Value = Year(Date())
>> >> This plugs the year when record is added.
>> >>
>> >> QuoteNumber, Autonumber
>> >>
>> >> Then on form (or report), use text box with Control Source =
>> >> =Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))
>> >>
>> >> This would show, for example, 080251
>> >>
>> >> In your case, add & "-" & , and you would get 08-0251
>> >>
>> >>
>> >> --
>> >> Dave Thompson
>> >> Allen, TX
>> >> US
>> >>
>> >>
>> >> "Bill" wrote:
>> >>
>> >> > Any way to create a auto number field with a text field in it? I
>> >> > have
>> >> > a
>> >> > field called "RecordID" which is formated as XX-XXX (08-111). Since
>> >> > I
>> >> > need
>> >> > the '-' dash in the field, it must be a text field. But I want it
>> >> > to
>> >> > autonumber and I want to pick the number where it starts.
>> >> >
>> >> > Any chance of this??
>> >> >
>> >> > Thanks,
>> >> >
>> >> > Bill
>>
>>
>>

Ok, I can follow just not sure where to start or how to accomplish this. I'm
a newbie and wrote this database to help my team complete things quicker and
easier. Can you give me an example of how to do this?

Thanks

"DaveT" wrote:

> Bill,
> There are ways to manipulate the starting number through VBA, but I find it
> easier to do this (does vary slightly if the data table has data you want to
> keep; here I'll assume the data table is empty and you want the first record
> added to start at a specific number):
>
> tblDATA
>
> Copy to tblTMP
>
> tblTMP: Change Autonumber field to long int
>
> Add one record to tblTMP with, for example, ID = 157 (one less than where
> you really want to start)
>
> Append tblTMP tp tblDATA (all fields)
>
> Leave the record with ID = 157 (this is autonumber field in tblDATA) until
> you add the first production record (ID = 158). Then go back and delete the
> ID = 157 record.
>
>
>
> --
> Dave Thompson
> Allen, TX
> US
>
>
> "Bill" wrote:
>
> > Thanks Dave. I kinda figured I would have to do it that way. The only other
> > part of this is that I want to start counting at a certain number. Each time
> > I make it a autonumber it resets to 1 and adds from there. How can I set the
> > first number (I want to start around 08-158
> >
> > Thanks
> >
> > "DaveT" wrote:
> >
> > > Not directly, but you can make it appear that way to users on reports and
> > > forms.
> > >
> > > I had similar issue with a quote program. Put two field in table:
> > >
> > > QuoteYear, Default Value = Year(Date())
> > > This plugs the year when record is added.
> > >
> > > QuoteNumber, Autonumber
> > >
> > > Then on form (or report), use text box with Control Source =
> > > =Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))
> > >
> > > This would show, for example, 080251
> > >
> > > In your case, add & "-" & , and you would get 08-0251
> > >
> > >
> > > --
> > > Dave Thompson
> > > Allen, TX
> > > US
> > >
> > >
> > > "Bill" wrote:
> > >
> > > > Any way to create a auto number field with a text field in it? I have a
> > > > field called "RecordID" which is formated as XX-XXX (08-111). Since I need
> > > > the '-' dash in the field, it must be a text field. But I want it to
> > > > autonumber and I want to pick the number where it starts.
> > > >
> > > > Any chance of this??
> > > >
> > > > Thanks,
> > > >
> > > > Bill

Dave,

I'm following your example and on the form I'm getting an error. Where does
it refer to the table? I've created a table (Table1) with two columns -
IR_Year and IR_Number. Then I created a form with a text box and placed the
following in Control Source

=Right(CStr([IR_Year]),2) & CStr(Format([IR_Number],"0000"))

thx

"DaveT" wrote:

> Not directly, but you can make it appear that way to users on reports and
> forms.
>
> I had similar issue with a quote program. Put two field in table:
>
> QuoteYear, Default Value = Year(Date())
> This plugs the year when record is added.
>
> QuoteNumber, Autonumber
>
> Then on form (or report), use text box with Control Source =
> =Right(CStr([QuoteYear]),2) & CStr(Format([QuoteNumber],"0000"))
>
> This would show, for example, 080251
>
> In your case, add & "-" & , and you would get 08-0251
>
>
> --
> Dave Thompson
> Allen, TX
> US
>
>
> "Bill" wrote:
>
> > Any way to create a auto number field with a text field in it? I have a
> > field called "RecordID" which is formated as XX-XXX (08-111). Since I need
> > the '-' dash in the field, it must be a text field. But I want it to
> > autonumber and I want to pick the number where it starts.
> >
> > Any chance of this??
> >
> > Thanks,
> >
> > Bill