Genrating text and auto number

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

-- 
Message posted via http://www.accessmonster.com

0
EMILYTAN
5/7/2007 12:09:48 AM
access.formscoding 7493 articles. 0 followers. Follow

7 Replies
1389 Views

Similar Articles

[PageSpeed] 25

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" <u33296@uwe> wrote in message 
news:71cb63be39507@uwe...
> How to generate auto number but i want it to be combination with text
> Example L0001, THEN L0002 and so on..how?
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Dale
5/7/2007 1:47:46 AM
Thanks for helping ya..

Dale Fye wrote:
>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?

-- 
Message posted via http://www.accessmonster.com

0
EMILYTAN
5/7/2007 2:38:27 AM
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
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"EMILYTAN via AccessMonster.com" <u33296@uwe> wrote in message 
news:71cb63be39507@uwe...
> How to generate auto number but i want it to be combination with text
> Example L0001, THEN L0002 and so on..how?
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Arvin
5/7/2007 2:53:36 AM
Where should i put this-->="L" & Format([AutonumberFieldName], "0000")?

Arvin Meyer [MVP] wrote:
>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?

-- 
Message posted via http://www.accessmonster.com

0
EMILYTAN
5/7/2007 5:39:36 AM
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...

Arvin Meyer [MVP] wrote:
>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?

-- 
Message posted via http://www.accessmonster.com

0
EMILYTAN
5/7/2007 5:52:18 AM
Hey! thanks to Dale and Arwin for willing to help me and provide me with the
solution k..
Thanks..

EMILYTAN wrote:
>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]
>>> How to generate auto number but i want it to be combination with text
>>> Example L0001, THEN L0002 and so on..how?

-- 
Message posted via http://www.accessmonster.com

0
EMILYTAN
5/8/2007 12:45:40 AM
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

"EMILYTAN via AccessMonster.com" <u33296@uwe> wrote in message 
news:71ce44b2f03f2@uwe...
> Where should i put this-->="L" & Format([AutonumberFieldName], "0000")?
>
> Arvin Meyer [MVP] wrote:
>>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?
>
> -- 
> Message posted via http://www.accessmonster.com
> 


0
Arvin
5/8/2007 3:42:52 AM
Reply:

Similar Artilces:

Limiting Size of Text Fields
Is there a way to limit the size of a text field? I know the max in Excel is 255 characters, but I have been asked to find a way to limit a field to 50 characters (or identify a way to notify the user that they have exceeded 50 characters). I found a function (LEN) that counts the characters, but it looks like this would have to be completed manually each time and so would not be effective? Any ideas would be appreciated... Cynthia --- Message posted from http://www.ExcelForum.com/ Hi Cynthia! You can use data validation to limit the number of characters entered in a cell. Select the ...

Exchange 2003 rich text html settings
Ok so I have read some posts about changing the option to allow the user to choose to send using html or rich text, and every post says go to Global settings / Internet messaging formats properties and then advanced. Well either Im a idiot or something is very wierd, when I go to the properties of internet messaging formats all I get is a generel tab with a list of mime types ( I can add, remove and move them but no options to change them) and I get a details tab but I have no advanced button or anyhting that other people are describing. PLEASE HELP I know I have found this area before but n...

anchor graphic in column 1 to text in column 2 in frontpage
as above Float graphic right in column 1 and float text left in column 2? "cog" wrote: > as above "cog" <cog@discussions.microsoft.com> wrote in message news:DC361C24-771F-4C19-B48A-F86AE8421394@microsoft.com... > as above I wrote this years ago for fp98 http://accessfp.net/how-to.htm Don't know what your version is but still holds true I presume you mean bookmarks? btw best to put the question in the body of the page for all sorts of reasons. If you want to bookmark within a page. Say you want to make a 'bottom of page' and ...

Text Baloons
I know that with other web design software you can add text or "word" baloons that pop-p when you highlight the text - Can this be done in Publisher? ...

How can you rotate the text within the text box so that the text .
Help. Need advice on how to move my text within a text box to align with the box's direction. For example, if I have an arrow pointing at an angle from left to right, how do I ensure that the text move with my box? Thanks in advance. The only way I now to achive this effect is to type the text directl into a cell of the spreadsheet. In the cell you can go to the cell' properties and adjust the text alignment and then you can put the arro over the text at the same angle. Just remember to make the text bo transparent. Hope that help -- intelgi ---------------------------------...

changing the text of error messages
hi there, is there a way to change the text of an error message in MS CRM? Some of them are simply confusing for the user and I'd like to make them more understandable. I have no language pack installed and only need one language. Any ideas? Regards, Ludger ...

how to activate text to speech
dear friends how to activate the text to speech bar ???????? -- frsm ------------------------------------------------------------------------ frsm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31231 View this thread: http://www.excelforum.com/showthread.php?threadid=519641 Pll-down: View > Toolbars and check text to speach This requires a recent version of Excel -- Gary's Student "frsm" wrote: > > dear friends > how to activate the text to speech bar ???????? > > > -- > frsm > ---------------------------...

phone numbers
Hello: You know how you cannot integrate employees into Employee Maintenance in Integration Manager if the employee's phone number has parentheses and dashes in it? Well, I found a great TechKnowledge article that walks you through how to replace the parentheses and dashes and successfully integrate. But, if you have one employee in your integration file that does not have a phone number, then Integration Manager throws out the employee record altogether and says "Invalid use of Null: 'Replace'". So, I tried the code below. I added some lines to the TechKnowledg...

Select Text with Similar Formatting is not enabled
In Word 2007, I have several instances of text formatted with small caps. I should be able to click inside one of the formatted words and from the Select menu, Select Text with Similar Formatting. However, that option is greyed out / disabled. Does anyone know why and what the fix is? Thank you! I found the answer: Keep Track of Formatting must be enabled in Word Advanced Options. "Trudy" wrote: > In Word 2007, I have several instances of text formatted with small caps. I > should be able to click inside one of the formatted words and from the Select ...

text on picture with no white text box
This may be a stupid question but how do i get text to run over a picture without it going in a white text box??? i have tried everthing and can not seem to get my text to run over my pictures without altering its background! please help me, many thanks kd <kd@discussions.microsoft.com> was very recently heard to utter: > This may be a stupid question but how do i get text to run over a > picture without it going in a white text box??? i have tried > everthing and can not seem to get my text to run over my pictures > without altering its background! Create a text box,...

Set Combo Based on Text Field
I want my Combo Box to be set by the value in my Text field. I thought this would work but it doesn't. The first field in the combo box and the text field should match. Me.CmbLife.Value = Me.TxtLifeID Thanks DS Do you really mean that you want the combo box to be set by the text field or do you mean the opposite (and more usual scenario) - you want the text field to be set by the combo. If you want them always to match then you can bind them, so whatever value you put into one, will change the other one. Is that what you want? Where have you typed your function - in the After Up...

Genrate random numbers using the binomial distribution
How can I generate random numbers using the binomial distribution and an excel spreadsheet. If possible please do in a step-by-step guide. Hi funkysunflower How about a video http://tinyurl.com/yj69stn or Google links http://www.google.ca/#hl=en&q=binomial+distribution+in+excel&meta=&aq=f&oq=binomial+distribution+in+excel&fp=7ad0076c90c94111 HTH John "funkysunflower" <funkysunflower@discussions.microsoft.com> wrote in message news:BCB4FC4E-46C6-41D2-BDB5-0C0173073CA1@microsoft.com... > How can I generate random numbers using the binomia...

Text box and formatting?
I am working on class schedule and have one text box which spans a few pages. Pages are double columns. I have a header for each class and paragraph given for class description. On the last page, last column, one description appears at the top with empty space for the rest of the column. I would like to change the formatting of the text in the whole text-box so that most of the last column is used. I can select the text in the text box which expands over the few pages and play with the font size, but that would mean that all the text would be the same size. Is there a way that text...

rich text fills in multiple sheets
I am creating a 6 page document where several rich text fields need to be filled out on the first sheet that also fills out the fields in the other sheets This should answer your question http://gregmaxey.mvps.org/Repeating_Data.htm -- Terry Farrell - MSWord MVP "DaveLopez" <DaveLopez@discussions.microsoft.com> wrote in message news:D4EEC35D-812A-45E0-AA0B-099C3030B2DC@microsoft.com... > I am creating a 6 page document where several rich text fields need to be > filled out on the first sheet that also fills out the fields in the other > sheets ...

Moving text only
is the a way to move text only? If i right click on the border area to move it to another cell (location) a window pops up but the option to MOVE value only does not show, only COPY value only and a list of other things, Say I have a persons name (Mike Smith) in a cell filled with color and a bold border around it and I only want to move the "Mike Smith" and leave everything else then I want to move another name into that spot with the color filled and border around the new name. I only want to move the text, quick and easy, I hope. Right now I need to highlight the text then do a C...

Auto Archive Secondary Mailbox
Hi all, We are running Exchange 2003 with Outlook XP clients. We would like to know if theres any way to auto archive a secondary mailbox? Instead of archive your own mailbox, can it be done to another mailbox you have setup in Outlook? At the moment the normal auto archive settings do not seem to work. Thanks Justin C ...

Changing the scale to custom text
Hello, I am trying to change my scale from say 5000 to 5k, is there any way to do this? -- Thank You, Steve Hi, Use as custom number format #,k Cheer Andy Steve D wrote: > Hello, I am trying to change my scale from say 5000 to 5k, is there any way > to do this? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info I can't get this to work it just brings up k instead of the number with K after it do you know what I am doing wrong? "Andy Pope" wrote: > Hi, > > Use as custom number format #,k > > Cheer > Andy > > Steve D wrote:...

Replace Quote Number
How can I change the next Quote Number that generates in CRM 3.0. It's already been started and I want to chane it. In the Settings area you'll see the "Auto-Numbering" link which has tabs for the entities that have auto-generated numbers. You'll find that you can modify the prefix & suffix length, but you don't have much else control. More complex, logical or business specific requests have been made, and there is usually another field involved which is driven by either client side scripting or plug-in logic. Michael D. Mayo http://www.mscrmtrainer.com &...

Auto filling Form AfterUpdate question
I have a database for a reference in my office's library. It has a table with the following fields: Title: title of the book FirstName: of author LastName: of author PID: Letter code pertaining to subject matter (i.e. W for wildlife) BID: autonumber key for database AuthorID: Initials of the author which is what im trying to autofill in my form ok so my problem is I want someone to be able to enter the first name and last name in the form and have the AuthorID automatically put the initials in. Can anyone help me? I have some very limited VBA experience and would really appreciate any h...

set up auto delete of inbox messages after message is moved
When I move a message (by dragging and dropping) to a folder outside of outlook, I want it to disappear from my inbox. You will need to press the Delete key. Gwen wrote: > When I move a message (by dragging and dropping) to a folder outside of > outlook, I want it to disappear from my inbox. I realize that pressing the delete key will delete the message. However, in Outlook 2003 I was able to set it up so that moving a message would delete it from the inbox. Just wondering if there's a way to do that in 07. "Bob I" wrote: > You will need ...

How to Round Whole Numbers?
What is the formula for rounding whole numbers down (ie not decimals to whole numbers)? I want to round numbers like 14427 to 14420. The numbers are in a formula such as: =SUM(C6:C22)*.08 and I want to round the result to the nearest 10. Thanks for any help. =FLOOR(SUM(C6:C22)*.08 ,10) -- HTH Bob Phillips "blank" <blank@void.net> wrote in message news:428af534$0$25599$5a62ac22@per-qv1-newsreader-01.iinet.net.au... > What is the formula for rounding whole numbers down (ie not decimals to > whole numbers)? > I want to round numbers like 14427 to 14420. The num...

Formatting text...
Every time I insert a text box it has Times New Roman as the default font. I wish to make Arial as the default, but darned if I know how to do it. The font schemes confuse the heck outta me. RiggerPJ <RiggerPJ@discussions.microsoft.com> was very recently heard to utter: > Every time I insert a text box it has Times New Roman as the default > font. Format > Styles and Formatting Edit the Normal style. (Font schemes are unnecessary for this simple task) If you want to set the default for all new blank publications, see http://66.249.93.104/search?q=cache:JmHrnaE32L8J:www.p...

Sorting imported text
I have a spreadsheet into which I've imported a great deal of data in two columns only. I set out rows one, three and five of hundreds of such rows. The cells in column A consist of place names. (The "C" following the place name stands for "City", the "A" for "Area".) The cells in column C represent the population of the city or area, as the case may be. I've formatted the cells in column C as numbers. Albury (C) 44887 Armidale Dumaresq (A) 24596 Ashfield (A) 40258 I want to sort the placenames in order of their po...

More Vertical Text Problems
I am starting to have problems with vertical text in my reports when previously it was working fine. My reports were printed and saved as a PDF file using the Adobe Acrobat 6.0 driver. I never had any trouble at that point. My office upgraded to Adobe Acrobat 7.0 about 2 months ago, but until now I am printing a report that has labels in a vertical arrangement, and the report creates an Adobe error print log. After testing different reports, I noticed that only the reports with vertical text were not printing and error print logs were created. I also noticed that printing to my local ...

I want to conditionally auto-populate a summary worksheet
I want to make a worksheet that will summarize the data from two spreadsheets, called "cars" and "parts", which have three columns each, one for description, one for SKU, and one for quantity. I'm trying to make a third worksheet display only those rows with a quantity of greater than 0. I want to split the third summary worksheet into two vertically split sections, one for cars, one for parts. I need to subtotal the "parts" and subtotal the "cars" sections, and then make a grand total. I want this to be a dynamic worksheet, so if you change the qu...