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
1579 Views

Similar Articles

[PageSpeed] 1

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:

page numbering #19
My page numbers don't show up when article is printed. I like them on the bottom of the page. queen of flowers wrote: > My page numbers don't show up when article is printed. I like them on the > bottom of the page. Do other objects in the same vertical position show up? It's conceivable that you have placed them in your printer's non-printable area. See http://ed.mvps.org/Static.aspx?=Publisher/horidiag -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Win 7 HDMI and poor text clarity
I am not sure if this would be considered a hardware or software issue so I will double post. Please forgive me. I am using a 19" HD flat panel TV as an external monitor for my laptop (Win 7). Text was crisp and clear with the RGB connection but when I switched to HDMI (to get the audio also) the text got pretty blurry (graphics are still pretty good). I've tried adjusting the resolution, the Clear Type text, and the Smaller 100% and Medium 125% settings but nothing seems to help. I've also tried various adjustments on the TV to no avail. Suggestions please. ...

adding duplicate text to multiple cells
I have alist of about 700 names that I need to turn into E-mail addresses. For instance I need to add @***.net to each user name. Is there a way to do this without typing it 700 times. =a1&"@***.net " and copy the formula down 700 rows, "beardic" <beardic@discussions.microsoft.com> wrote in message news:3D47D872-7BCA-42C3-82EB-46CC5F528024@microsoft.com... >I have alist of about 700 names that I need to turn into E-mail addresses. > For instance I need to add @***.net to each user name. Is there a way to > do > this without typing it 700 time...

How to round/truncate a calculated number in a receipt?
Hi, I implemented POS in Mexico, and to make a receipt call Factura I need to do the following calculation that is printed in currency format (xx.xxx): - <SUB name="DrawLastFooter"> - <MARGIN> <BOTTOM>MarginBottom</BOTTOM> </MARGIN> - <TABLE> <TOP>PaperHeight - MarginBottom - FooterHeight</TOP> <FONT>"Small"</FONT> <BORDER>tbNone</BORDER> - <COLUMNHEADER> <ALIGNMENT>">~"</ALIGNMENT> <WIDTH>PageWidth</WIDTH> <TEXT>" "</...

Auto filter- Highlighting active filter
Hi, I appreciate that when a filter is active, the arrow turns blue. However, is it possible to make this more visible, ie: make the actual button yellow for example? Or even shade the cell which the button sits in, to yellow? Trivial I know, but it would help if the active filter was more visible! Many thanks, MarkO Hi Mark Debra Dalgleish has an example workbook where she colours the cells with the active dropdown http://www.contextures.com/FilterColour.zip -- Regards Roger Govier "Marko" <Marko@discussions.microsoft.com> wrote in message news:82A736CD-029E-4AAB-...

Text Box help
Can someonee help me with how to assign the text boxes (Varibles), so that when I fll in all the text boxes I can use a command button to send the information to the various Workbooks. The Workbooks are going to be Titled as follows: WorkBook Name Command Button Name -------------------------------------------------------------------------- Engineering Spec Workbook = Update Engineerring Spec Sheet Installer Forms = Update Installer Forms Job Folder Label = Job Folder Label Thanks Brian ...

Number of Multi-users
What is the number of people who can share an excel spreadsheet and edit at the same time? In Excel's Help, under "Excel specifications and limits", it says: Users who can open and share a shared workbook -- 256 SW wrote: > What is the number of people who can share an excel spreadsheet and edit at > the same time? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

1-52 pages, 8,000 employee numbers
Dear Excel, I have the ultimate challenge: to do job books for 8,000 employees. The form is completed, but the request is to make booklets of 52 pages for each employee. Each page of the booklet must also have each employee's number on it (0001 to 8000). Each page o fthe booklet must be numbered with the week number from 01 to 52. Therefore I need to auto generate 416,000 pages. Ridiculous I know but nobody listens to us! How can I export the above scenario to PDF from Excel? Thanks On Apr 19, 2:17=A0pm, gatecrasherg13 gatecrasherg13 <gatecrasher...@gmail.com> wr...

Rank and row number in Access?
I am trying to general a query where by their is ranking within groups like the table below: firm-id rank 1 1 1 2 2 3 want: firm-id rank 1 1 1 2 1 3 2 1 2 2 3 1 DP file are row number and rank not valid functions in the ADP file? On Mon, 12 Apr 2010 16:52:01 -0700, Steven Cheng <StevenCheng@discussions.microsoft.com> wrote: >I am trying to general a query where by their is ranking within groups like >the table below: > > firm-id rank >1 >1 >1 >2 >2 >3 > >want: > >firm-id rank ...

Running Total of Random Number
Hi, I am trying to create a probablity simulation for a high school lesson. It involves rolling 1/2 dice using the randbetween function to create the die value. I want to keep a running total of the outcomes 1-6 in a cell for each which is linked to a chart. Is this possible? Do you mean a running total of all the values from the dice, if so do tools>options>calculation and check iteration and change from 100 to 1, assume randbetween formula is in A1 and you want the running total in B1, in B1 put =A1+B1, press F9 to calculate Regards, Peo Sjoblom "Mr H" wrote: &...

axis text formatting
Hi again Posted this to the wrong forum, so am posting again. I have formatted my Y axis text as Trebuchet MS regular 8. When I go t change the alignment of the text, the text becomes distorted an blurred. I cannot find a way to stop it doing this. Thanks Emm -- Message posted from http://www.ExcelForum.com Hi Emma - That's a problem with trying to resolve fonts on a screen made up of discrete pixels. If you deviate from horizontal or vertical, the fonts don't look as good. Do they improve if you print them using a high resolution printer (300+ dpi)? - Jon ------- Jon Pelt...

auto configuring outlook 2003 for exchange.
I want to autoconfigure outlook 2003 with the help of administrative templeates for outlook 2003 that i got with office resource kit for my local exchange server is it possible to do so ? rather then deploying outlook on all network by visiting each user & pc do it auto all .. sounds good right ? thanks take care Yes, if Outlook/Office is not yet installed, use the Custom Installation = Wizard. If it is, use the Custom Maintenance Wizard.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers ...

How to update 'Edit Control' text from Timer Callback.
Hi, I need a way to update an Edit control control in MFC from a timer callback function. How can that be done as we cannot reference dialog box controls from callbacks? Even the following code gives me problems: VOID CALLBACK TimerProc ( HWND hParent, UINT uMsg, UINT uEventID, DWORD dwTimer ) { //Add the text CEdit *ptrtxtActivity; ptrtxtActivity = reinterpret_cast<CEdit *>(GetDlgItem(hParent,txtboxActivity)); ptrtxtActivity->SetWindowText(text); } Please let me know, Thank you, IdleBrain "IdleBrain" <indianmostwanted@yahoo.com> ha scritto nel messa...

Cleaning up Imported Text
I wrote a web query to get text data from a Federal Reserve Bank site. Here's the site: http://federalreserve.gov/releases/h10/Summary/indexnc_m.txt The query window #1 contains the above address, window #2 is checked for "Only the tables", #3 is set at "Full HTML formatting" (when set at "None", everything went to one column, when set to "Full HTML formatting" results were same as for Rich text). Under the "Advanced" button I left only "Import <PRE> blocks into columns checked. The net result is that everthing flowed into the s...

Auto Filter Entries
Is there any way to extract the detail listed in drop down auto filters? I work with files where there are numerous entries of the same customer name for each customerustomer in the one list along with other data in the file. When you select the drop down box in the auto filter you get a list of customers. It's that list that I want to put on another worksheet so that I have only one listing per customer rather than 20 or 30 of them. Or, maybe there is a better way you fine people might suggest. Cheers Mark Advanced filter will do that for you. Start the Advanced filter in the ...

Serial Number Report
Is there a report that will give a list of all serial or lot numbers available at a given point in time. I know we can get this for items but haven't found anything that will give the acutal serials at a specific date in the past. ...

Spell checking original text
Within Tools, Options, Spelling, I have checked the box "Ignore original message text in Reply of Forward". Despite this, when I click send and SpellChecker does its stuff, it continues to deal with the original email. Is there another setting that I need to invoke to stop it from worrying about the original email text. -- Regards, Darren In replies do you have the original message prefixed with a character (typically '>')? If so, spell checking will check the original message, too. "Darren" <Darren@discussions.microsoft.com> wrote in message news:...

Spreading text into multiple rows
I am trying to split lengthy text in a cell into multiple rows so that it is roughly the same length in each row. E.g., I have pasted the following text from a web page into Excel 97 cell B5: Information and interactive calculators are made available to you as self-help tools for your independent use and are not intended to provide investment advice. We can not and do not guarantee their applicability or accuracy in regards to your individual circumstances. All examples are hypothetical and are for illustrative purposes. We encourage you to seek personalized advice from qualified professional...

EFT For Payables Needs To Include Invoice Number In Transmission
We use EFT For Payables. The problem with it is that the invoices number(s) paid do not get transmitted to the vendors. We therefore have to send them an e-mail, which limits our usage of the EFT For Payables. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://w...

org chart shape too large / text too small
I'm a beginner with little experience. My org chart was perfect until I tried to understand the print page and the drawing page. In my experimenting I somehow goofed. Now my org chart shapes are way too large and the test is way to small. Can you helpme get back to "normal"? ...

Auto Loan Calculator
Maybe, someone could parlay their car buying experience with me. My mother is in the market for a new car. We went to the dealership to browse cars and the salesman begins his routine. We were in the initial stages of the car buying process (she wanted to see if she looked good in the car. Really!) The salesman put us on the fast track. He even offers to approach the sales manager for incentives. The car in question is in extreme demand and the dealers only sell them at MSRP. In addition, the cars are often pre-sold with the option she wants. A deposit is in order to reserve a car, but I told ...

Out of office assistant/auto replies
I was wondering if it was possible to set an out of office/auto reply for all exchange user accounts through the exchange server. When our office shuts down unexpectedly, I'd like to be able to set everyone's exchange accounts up to send out automated responses that our office is closed - however, I don't want to have every user do this themselves. Is that possible? Thanks! Not natively. I don't even know of any 3rd party products that would allow you to do that. Best I can think of is some custom event sink that would shoot a message back to the sender after it was r...

How to insert a number sequence in irregular rows or columns of Excel
Hi, I cannot find in the Help files of my Excel '97 how to insert, in effect, a field which would increase by one every time I copy it into a different cell. I only know how to insert such a series over a contiguous set of rows or columns using the Edit>Series menu. Does anyone here please have an answer to this problem? Or is there one?? Thank you for taking the trouble to read this enquiry. avril To create sequential numbers in column A, enter the following formula in cell A2: =MAX($A$1:A1)+1 Copy this formula to another cell in column A, to create the next number. av ...

Possible to link Excel cells to text box in Word using Word's text formatting?
Hi everyone. Here is one of those "is this possible" type situations, which if possible, would make my Augusts less stressful each year. Each August I'm in charge of putting together our annual statistics and producing maps of the state to show activity in each county. Initially my maps were Word documents with individual text boxes for each #. I still have that version saved, but recently I've converted it to a pdf document to make changing the numbers a lot easier (although not being able to add lines of text is a hindrance sometimes). What I'd love to be able to do i...

HTML/plain text settings on replies
Running Outlook 2003 with all the updates. I send emails using rich text and occasionally, when I reply to emails sent in plain text, I have to manually set the outgoing reply to rich text. Is there any way I can set Outlook to always send in rich text, regardless of what format came in? TIA No. On top of it not being technically possible, it's sort of rude. If someone sends you plain text they may not want to or be able to read Rich Text or HTML "Trent SC" <invalid@bogoff.invalid> wrote in message news:%23mQjiTK2FHA.2132@TK2MSFTNGP15.phx.gbl... > Running O...