Autonumber - with text

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

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
 
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 said:
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 said:
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
 
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

(no private e-mails, please)


Bill said:
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 said:
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 said:
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 said:
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 said:
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 said:
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 said:
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

(no private e-mails, please)


Bill said:
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 said:
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


:

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

(no private e-mails, please)


Bill said:
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 said:
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

(no private e-mails, please)


Bill said:
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

:

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


:

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 said:
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 said:
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 said:
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


:

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 said:
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 said:
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
 
Back
Top