Genrating text and auto number

[EMILYTAN via AccessMonster.com]

How to generate auto number but i want it to be combination with text
Example L0001, THEN L0002 and so on..how?

 

[Dale Fye]

Emily,

What does the 'L' stand for ? Will this field contain other series of
numbers that start with some other letter but which must also start
sequencing at 00001? By storing
more than one piece of information in a single database field, you are
violating the first normal form of database design. I would recommend you
store the letter designator in a separate field.

Assuming that you have other letters, that must also be numbered starting at
1, you will not be able to use an autonumber datatype for your field, so use
a LongInteger. I work around this by creating a couple of queries.

1. Start out by creating a new table (I call mine tbl_Numbers). Give it one
field (lngValue) with a datatype of long. Then fill it in with values from
zero to nine. You could just fill this with values to 1000 or 10000 or some
other obscenely large number, but why bloat your database.

2. Create a query (qry_Numbers) that looks something like the following.
This query will give you all the number from 0 to 9,999. Depending on how
high you expect your numbers in you field to get, you may have to add a some
more instances of tbl_Numbers for TensOfThousands..

SELECT Thousands.lngValue * 1000 + Hundreds.lngValue * 100 + Tens.lngValue *
10 + Ones.lngValue as lngNumber
FROM tbl_Numbers as Thousands, tbl_Numbers as Hundreds, tbl_Numbers as Tens,
tbl_Numbers as Ones

3. Create another query (qry_NextValue) that joins qry_Numbers to a
subQuery of your table (this assumes you have multiple letter values)

Sample #1: This query will actually give you the minimum unused value, so if
you have deleted a record, it will reuse that value
SELECT MIN(qry_Numbers.lngNumber) as NextValue
FROM qryNumbers
LEFT JOIN (SELECT NumberFieldName From yourTable WHERE LetterFieldName =
[Forms]![FormName]![TextControl]) as subQry
ON qryNumbers.lngNumber = subQry.NumberFieldName
WHERE subQry.NumberFieldName IS NULL

Sample #2: This query will actually give you the minimum value greater than
the larges value already in use (my quess is that this is what you want to
use)
SELECT MIN(qry_Numbers.lngNumber) as NextValue
FROM qryNumbers
WHERE qry_Numbers.lngNumber > (SELECT MAX(NumberFieldName) From yourTable
WHERE LetterFieldName = [Forms]![FormName]![TextControl])

This query assumes you have a form where you have entered the letter that
you want to preceed your number. Change "[FormName]" above to the name of
that form, and "[TextControl] " to the name of the control.

4. You could then use a DLOOKUP function as the control source of the next
control on your form and in the AfterUpdate event of the control that
contains the letter, you could requery this control.

HTH
Dale

 

[EMILYTAN via AccessMonster.com]

Thanks for helping ya..

Emily,

What does the 'L' stand for ? Will this field contain other series of
numbers that start with some other letter but which must also start
sequencing at 00001? By storing
more than one piece of information in a single database field, you are
violating the first normal form of database design. I would recommend you
store the letter designator in a separate field.

Assuming that you have other letters, that must also be numbered starting at
1, you will not be able to use an autonumber datatype for your field, so use
a LongInteger. I work around this by creating a couple of queries.

1. Start out by creating a new table (I call mine tbl_Numbers). Give it one
field (lngValue) with a datatype of long. Then fill it in with values from
zero to nine. You could just fill this with values to 1000 or 10000 or some
other obscenely large number, but why bloat your database.

2. Create a query (qry_Numbers) that looks something like the following.
This query will give you all the number from 0 to 9,999. Depending on how
high you expect your numbers in you field to get, you may have to add a some
more instances of tbl_Numbers for TensOfThousands..

SELECT Thousands.lngValue * 1000 + Hundreds.lngValue * 100 + Tens.lngValue *
10 + Ones.lngValue as lngNumber
FROM tbl_Numbers as Thousands, tbl_Numbers as Hundreds, tbl_Numbers as Tens,
tbl_Numbers as Ones

3. Create another query (qry_NextValue) that joins qry_Numbers to a
subQuery of your table (this assumes you have multiple letter values)

Sample #1: This query will actually give you the minimum unused value, so if
you have deleted a record, it will reuse that value
SELECT MIN(qry_Numbers.lngNumber) as NextValue
FROM qryNumbers
LEFT JOIN (SELECT NumberFieldName From yourTable WHERE LetterFieldName =
[Forms]![FormName]![TextControl]) as subQry
ON qryNumbers.lngNumber = subQry.NumberFieldName
WHERE subQry.NumberFieldName IS NULL

Sample #2: This query will actually give you the minimum value greater than
the larges value already in use (my quess is that this is what you want to
use)
SELECT MIN(qry_Numbers.lngNumber) as NextValue
FROM qryNumbers
WHERE qry_Numbers.lngNumber > (SELECT MAX(NumberFieldName) From yourTable
WHERE LetterFieldName = [Forms]![FormName]![TextControl])

This query assumes you have a form where you have entered the letter that
you want to preceed your number. Change "[FormName]" above to the name of
that form, and "[TextControl] " to the name of the control.

4. You could then use a DLOOKUP function as the control source of the next
control on your form and in the AfterUpdate event of the control that
contains the letter, you could requery this control.

HTH
Dale

How to generate auto number but i want it to be combination with text
Example L0001, THEN L0002 and so on..how?

 

[Arvin Meyer [MVP]]

That's not really a number. If it is always an "L" the expression would
read:

="L" & Format([AutonumberFieldName], "0000")

if you want a number to go to L9999, then go to M0001 Here's some code
(untested) that should do that. Remember to seed the table with the first
"number"

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMaxNo As String
Dim intCharNo As Integer
Dim strNo As String * 4
Dim intNo As Integer
Dim strLetter As String * 1

strMaxNo = DMax("IDField", "tblSeed")
strLetter = Left(strMaxNo, 1)
strNo = Right(strMaxNo, 4)
intNo = CInt(strNo)
If intNo = 9999 Then
intNo = 1
strLetter = Chr(Asc(strLetter) + 1)
Else
intNo = intNo + 1
End If
strNo = Format(intNo, "0000")
Me.IDField = strLetter & strNo

End Sub

 

[EMILYTAN via AccessMonster.com]

Where should i put this-->="L" & Format([AutonumberFieldName], "0000")?

That's not really a number. If it is always an "L" the expression would
read:

="L" & Format([AutonumberFieldName], "0000")

if you want a number to go to L9999, then go to M0001 Here's some code
(untested) that should do that. Remember to seed the table with the first
"number"

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMaxNo As String
Dim intCharNo As Integer
Dim strNo As String * 4
Dim intNo As Integer
Dim strLetter As String * 1

strMaxNo = DMax("IDField", "tblSeed")
strLetter = Left(strMaxNo, 1)
strNo = Right(strMaxNo, 4)
intNo = CInt(strNo)
If intNo = 9999 Then
intNo = 1
strLetter = Chr(Asc(strLetter) + 1)
Else
intNo = intNo + 1
End If
strNo = Format(intNo, "0000")
Me.IDField = strLetter & strNo

End Sub

How to generate auto number but i want it to be combination with text
Example L0001, THEN L0002 and so on..how?

 

[EMILYTAN via AccessMonster.com]

Where should I put this ->="L" & Format([AutonumberFieldName], "0000")?
I dont need to go to M0001 from L0001..
Thank You....
Hopefully you can help me...

That's not really a number. If it is always an "L" the expression would
read:

="L" & Format([AutonumberFieldName], "0000")

if you want a number to go to L9999, then go to M0001 Here's some code
(untested) that should do that. Remember to seed the table with the first
"number"

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMaxNo As String
Dim intCharNo As Integer
Dim strNo As String * 4
Dim intNo As Integer
Dim strLetter As String * 1

strMaxNo = DMax("IDField", "tblSeed")
strLetter = Left(strMaxNo, 1)
strNo = Right(strMaxNo, 4)
intNo = CInt(strNo)
If intNo = 9999 Then
intNo = 1
strLetter = Chr(Asc(strLetter) + 1)
Else
intNo = intNo + 1
End If
strNo = Format(intNo, "0000")
Me.IDField = strLetter & strNo

End Sub

How to generate auto number but i want it to be combination with text
Example L0001, THEN L0002 and so on..how?

 

[EMILYTAN via AccessMonster.com]

Hey! thanks to Dale and Arwin for willing to help me and provide me with the
solution k..
Thanks..

Where should I put this ->="L" & Format([AutonumberFieldName], "0000")?
I dont need to go to M0001 from L0001..
Thank You....
Hopefully you can help me...

That's not really a number. If it is always an "L" the expression would
read:

[quoted text clipped - 28 lines]

 

[Arvin Meyer [MVP]]

As the controlsource of a textbox used to display the true autonumber.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Where should i put this-->="L" & Format([AutonumberFieldName], "0000")?

That's not really a number. If it is always an "L" the expression would
read:

="L" & Format([AutonumberFieldName], "0000")

if you want a number to go to L9999, then go to M0001 Here's some code
(untested) that should do that. Remember to seed the table with the first
"number"

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strMaxNo As String
Dim intCharNo As Integer
Dim strNo As String * 4
Dim intNo As Integer
Dim strLetter As String * 1

strMaxNo = DMax("IDField", "tblSeed")
strLetter = Left(strMaxNo, 1)
strNo = Right(strMaxNo, 4)
intNo = CInt(strNo)
If intNo = 9999 Then
intNo = 1
strLetter = Chr(Asc(strLetter) + 1)
Else
intNo = intNo + 1
End If
strNo = Format(intNo, "0000")
Me.IDField = strLetter & strNo

End Sub

How to generate auto number but i want it to be combination with text
Example L0001, THEN L0002 and so on..how?