adding name /creating field/query?

Hello,

I can create an invoice_number field in a query using the primary
field ID from the main table as

invoice_number: ID

but if ID say is 100, I cannot work out how to create

renewal_invoice_100

Cheers

Geoff

0
Geoff
12/1/2007 11:46:16 AM
access 16762 articles. 3 followers. Follow

7 Replies
667 Views

Similar Articles

[PageSpeed] 57

Geoff

We aren't there.  We can't see what you're looking at.

Where did "renewal_invoice_100" come from and what does it mean?

Please post the SQL statement of the query you are trying to use.

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"Geoff Cox >" <<> wrote in message
news:16i2l3dor7b3ma0hem6aql215jjnf04ekp@4ax.com...
> Hello,
>
> I can create an invoice_number field in a query using the primary
> field ID from the main table as
>
> invoice_number: ID
>
> but if ID say is 100, I cannot work out how to create
>
> renewal_invoice_100
>
> Cheers
>
> Geoff
>

0
Jeff
12/1/2007 1:55:26 PM
On Sat, 01 Dec 2007 11:46:16 +0000, Geoff Cox <<>> wrote:

>Hello,
>
>I can create an invoice_number field in a query using the primary
>field ID from the main table as
>
>invoice_number: ID

If the primary key is an Autonumber, do be aware that there will be gaps in
the numbering - any invoice that's deleted will leave a gap; even hitting
<Esc> after starting an invoice will leave  a gap; adding invoices using an
Append query may leave a gap, often a huge one; replicating the database will
make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and
particularly not for consumption by accountants and auditors. Invoice
sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such
people get very nervous.

>but if ID say is 100, I cannot work out how to create
>
>renewal_invoice_100

Don't store the fixed text in your table at all. Instead use a Format property
of

"renewal_invoice_#"

to *display* the text.

             John W. Vinson [MVP]
0
John
12/1/2007 5:20:27 PM
On Sat, 1 Dec 2007 05:55:26 -0800, "Jeff Boyce"
<JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote:

>Geoff
>
>We aren't there.  We can't see what you're looking at.
>
>Where did "renewal_invoice_100" come from and what does it mean?

Jeff,

This was just an example of a more meaningful  invoice name, rather
than having it named simply by the ID value, i.e. renewal_invoice_100
rather than just 100.

>Please post the SQL statement of the query you are trying to use.

I haven't been able to get beyond having the ID value as the name so
no sql worth showing.

Cheers

Geoff

PS John not very impressed by the idea of using an autonumber ID
anyway!


0
Geoff
12/1/2007 10:41:29 PM
On Sat, 01 Dec 2007 10:20:27 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Sat, 01 Dec 2007 11:46:16 +0000, Geoff Cox <<>> wrote:
>
>>Hello,
>>
>>I can create an invoice_number field in a query using the primary
>>field ID from the main table as
>>
>>invoice_number: ID
>
>If the primary key is an Autonumber, do be aware that there will be gaps in
>the numbering - any invoice that's deleted will leave a gap; even hitting
><Esc> after starting an invoice will leave  a gap; adding invoices using an
>Append query may leave a gap, often a huge one; replicating the database will
>make your invoice numbers random.
>
>In short... it's best NOT to use Autonumbers for human consumption, and
>particularly not for consumption by accountants and auditors. Invoice
>sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such
>people get very nervous.
>
>>but if ID say is 100, I cannot work out how to create
>>
>>renewal_invoice_100
>
>Don't store the fixed text in your table at all. Instead use a Format property
>of
>
>"renewal_invoice_#"
>
>to *display* the text.
>
>             John W. Vinson [MVP]

John,

I see what you mean re the use of the autonumber ID!

I'm not clear how to use renewal_invoice_#?

Incidentally apart from the autonumber ID value the only other unique
field value is the post code - this would look rather odd as an
invoice number and would not give a series of consecutive values - any
suggestions?

Cheers

Geoff
0
Geoff
12/1/2007 10:45:09 PM
On Sat, 01 Dec 2007 22:45:09 +0000, Geoff Cox <<>> wrote:

>I see what you mean re the use of the autonumber ID!
>
>I'm not clear how to use renewal_invoice_#?

Just what I say. There is NO need to store 16 identical text characters
renewal_invoice_ in every InvoiceID field in every record in your table. Use a
Long Integer number and use a Format property to *display* the text (without
storing it); the Format property

"renewal_invoice_#"

will do just that - display the text followed by a number.

>Incidentally apart from the autonumber ID value the only other unique
>field value is the post code - this would look rather odd as an
>invoice number and would not give a series of consecutive values - any
>suggestions?

Use a Long Integer number field instead of an Autonumber; and use VBA code on
the Form which (I hope!!) you're using to enter the invoices. Depending on how
many people are entering data concurrently, this can be very simple or fairly
complex. The simple end would work for one user (or for very low probability
of two users entering new invoices simultaneously); put code in the Form's
BeforeInsert event like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!InvoiceID = DMax("[InvoiceID]", "[Invoices]") + 1
End Sub


             John W. Vinson [MVP]

0
John
12/2/2007 1:40:25 AM
On Sat, 01 Dec 2007 18:40:25 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Sat, 01 Dec 2007 22:45:09 +0000, Geoff Cox <<>> wrote:
>
>>I see what you mean re the use of the autonumber ID!
>>
>>I'm not clear how to use renewal_invoice_#?
>
>Just what I say. There is NO need to store 16 identical text characters
>renewal_invoice_ in every InvoiceID field in every record in your table. Use a
>Long Integer number and use a Format property to *display* the text (without
>storing it); the Format property
>
>"renewal_invoice_#"
>
>will do just that - display the text followed by a number.
>
>>Incidentally apart from the autonumber ID value the only other unique
>>field value is the post code - this would look rather odd as an
>>invoice number and would not give a series of consecutive values - any
>>suggestions?
>
>Use a Long Integer number field instead of an Autonumber; and use VBA code on
>the Form which (I hope!!) you're using to enter the invoices. Depending on how
>many people are entering data concurrently, this can be very simple or fairly
>complex. The simple end would work for one user (or for very low probability
>of two users entering new invoices simultaneously); put code in the Form's
>BeforeInsert event like
>
>Private Sub Form_BeforeInsert(Cancel as Integer)
>Me!InvoiceID = DMax("[InvoiceID]", "[Invoices]") + 1
>End Sub
>
>
>             John W. Vinson [MVP]

Thanks again John - you are a little ahead of me with some of the
above!

Cheers

Geoff
0
Geoff
12/2/2007 10:07:22 AM
I've found Autonumbers to be generally unfit for human consumption.  They
work for what they were designed to do, provide a unique row identifier.

-- 
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"Geoff Cox >" <<> wrote in message
news:6ho3l3d7fjet405vggcv3j3tngu6orj4sh@4ax.com...
> On Sat, 1 Dec 2007 05:55:26 -0800, "Jeff Boyce"
> <JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote:
>
> >Geoff
> >
> >We aren't there.  We can't see what you're looking at.
> >
> >Where did "renewal_invoice_100" come from and what does it mean?
>
> Jeff,
>
> This was just an example of a more meaningful  invoice name, rather
> than having it named simply by the ID value, i.e. renewal_invoice_100
> rather than just 100.
>
> >Please post the SQL statement of the query you are trying to use.
>
> I haven't been able to get beyond having the ID value as the name so
> no sql worth showing.
>
> Cheers
>
> Geoff
>
> PS John not very impressed by the idea of using an autonumber ID
> anyway!
>
>

0
Jeff
12/2/2007 1:27:44 PM
Reply:

Similar Artilces:

Re: Extracting Exchange User Data From AD 02-24-10
If you do not like scripting, you can try GAL Exporter or Fast User Manager & Reports from IMIBO - http://www.imibo.com > > "Ringholz, Blake" <bringholz@nospam.com> wrote in message > news:76AEFC2F-85A7-4666-8262-27FB0737D09A@microsoft.com... >> Hello All - >> >> I need to get an Excel Spreadsheet that lists everyone first name, last >> name, email address, job title, etc pulled from Active Directory. Is >> there an easy way to do this? >> >> Thanks, >> Blake > > > > > ...

Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the...

Excel 2002
Have several spreadsheet files I use routinely. Three have recently crashed after I added another sheet. In each case the document recovery created a file missing all the color and text formats that the file contained before the crash. Not sure what other changes may have occurred. Is there something wrong with the copy of Excel on my PC? Could these three files be corrupt? Is there a procedure to "clean-up" these files? Thanks in advance for any suggestions. Mark Hi sounds like they are corrupted. I would suggest to copy the data + formats to a new, 'fresh' workbook. ...

This query not giving correct results
I am trying to find the date when we had the most rainfall out of 3234 records, so with the first query to get the maximum rainfall in a month I get 110 records with one null and one '0' value. So this query is saying that out of 3234 records there has only been 108 days when we had rain. We probaly had more than that in one year never mind in 10 years. This cannot be right because we had 24 days of rain in November 2009 but the query only shows 16 for that month!. So how does it actually work? SQL for this below: SELECT Max(tblWXDataLeeds.ReadingDate) AS MaxOfReadin...

adding a leading 0 to only the numbers with 4 digits
I'm converting a bunch of data and during the import Excel dropped the leading zero from all the lip codes. Since this is about 15,000 records, I'd like a way to add a leading zero to only the zip codes with 4 characters. Any ideas? thanks Swamp, Format/Cells/Special/Zip Code Beege "SwampYankee" <johndillworth@gmail.com> wrote in message news:1147451549.294898.147230@v46g2000cwv.googlegroups.com... > I'm converting a bunch of data and during the import Excel dropped the > leading zero from all the lip codes. Since this is about 15,000 > records, I&...

Saving a calculated field
First, yes I have read the threads on storing a calculated field and that it is bad mojo to do that. However, I have pay data that I calculate and input to a database and it must be able to be reconciled with our ADP data. So I need the ability to change and fix the data so it does not change as a result of recalculations. I have a form with a field that calculates the pay based on hours and pay rate. I have another field (the "copy" field) next to that one that has the control source set to the database field. I have set the default value of that field to be equal to the...

What does #REF! mean in the Name Manager
I have a 2007 workbook with 6-7 sheets. I made some changes to cell names and apparently made some mistakes. When I open the Name Manager, the last entry looks like this: Name Value Refers to Scope Comments x #REF! =#REF!$C$25 Workbook I cannot find that name anywhere. I have looked in C25 on every sheet. What does this mean? >Name Value Refers to Scope Comments > x #REF! =#REF!$C$25 Workbook #REF! means it's an invalid reference. Did you delete a sheet? If so, that's probably why you're getting those. -- Biff Micro...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row I have 12 fields for 12 months of data, I need the average of the sum of all non blank entries. For example 3 months completed, the solution in Excel is (field1+field2+field3)/3 I am looking for method to average the sum in Access One way if you can't change your data is to use a VBA function. I've posted one below. You would call it in a calculated field in a query. Assuming your field names are the abbreviated month names the expression might look like the following. Field: fRow...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

Adding attachments to email merges
I am trying to create an email merge in Outlook, with a PDF attachment. It all works ok, and the message sends but the attaachment is always stripped off the message - I have tried different files and the attachement never comes through, or even gets mentioned in the received message. What am I doing wrong? Any suggestions gratefully received. Many thanks m.nutt1 Not supported. You'd need to use third party software for that. http://www.slipstick.com/addins/mail.htm#massmail -- Russ Valentine [MVP-Outlook] "m.nutt1" <mark@emqc.co.uk> wrote in message news:%23qnq...

getting added to other peoples meetings in shared calendar
I opened shared calendars in my group by choosing their name and then the email messages went out giving them access to my calendar and requesting to view theirs. Ever since, I have been copied in as a "required attendee" on all meetings for everyone. I get the calendar items on my calendar and emails requesting Accept or Decline in my inbox. When others view view thru their outlook, they do not see me as a required attendee? What am I doing wrong? are you adding them as delegates or just giving them permission to view the calendar (by right clicking on the calendar fold...

How do insert name automatically atop of each pg of doc?
Hello, How do I automatically insert name on each page of document like inserting page numbers? My professor told me not to type it in but to insert and I have not been able to do so. This is a new program for me, Word 2007. I have to submit my paper in MLA form and do not wish to lose points because for this. Thanks for your help. Click on the Insert tab of the Ribbon and then in the Header and Footer section, click on Header and make your choice from the options presented or click on the Edit Header button to get a blank header pane in which to insert the name in the...

Create incident on website : localhost ok
Hello everybody, I'am using /service/serviceportal.aspx when i open a new case in http://localhost:1917/service/serviceportal.aspx it creates the "incident" normally but when i open a new case in http://danubecrm:1917/service/serviceportal.aspx -> Operation failed due to a SQL integrity violation. Platform System.Web.Services.Protocols.SoapException: Server was unable to process request. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.W...

Adding Members- Dist. Lists
Not sure if I should be posting this here or in the SBS group. Does anyone know if there is a way to assign a permission to a user in our office to be able to update membership lists for distribution groups while she is in Outlook? Currently, when she clicks the "To:" button in a new email, right-clicks the distribution group, selects properties, goes to "modify members", it lets her select the new member but then when she clicks ok she will get an error mssg saying she doesn't have permission to do this. thx! on the security tab of the DL, does she have the &qu...

Creating a Check Box
I would like to learn how to create a check box. Is there a way to do that? The simplest checkbox (I think) is from the Forms toolbar. So View|tool bars and show that Forms toolbar. Click on the checkbox and then click on the topleft corner where it should be placed. Then draw to the opposite lowerright corner. When you've placed it correctly, right click on it and select "Format Control" On the Control Tab, you can assign a cell link that you can use to determine if the checkbox is checked or not. =if(A1=True,"It's checked","It's not checked") ...

Subform doesn't work once added to main form
Hi, I've been searching around looking for some help and can't find anyone that has had quite the same problem. I have a main form called 'Sites' and have added a subform called 'Previous Year Flows' into the main form. I do not enter data into this subform. Instead, data on previous years is drawn through from a table called prev yr flows into this subform. When I open the subform on its own it draws through all the necessary data, but when it is embedded within the main form, none of the actual data is pulled through at all, although the linked fields, Site ID and A...

need to make a formula that would add a field value to current dat
I have made a form in which I input different values. On of the values is (How Many Days). Now I need to a assign a default value, or expression (not sure which way to go about this) that will take the date value for (Date) and add the value (How Many Days) I figured that the formula should read =sum([Date]+[How Many Days]) But that is not giving me any results, thanx for your help in advance =DateDiff("d", Date(), [How Many Days]) -- Wayne Manchester, England. "J Man" wrote: > I have made a form in which I input different values. On of the values is ...

adding date/time picker to inputbox control
Hi all,is there a way to add a date/time picker control to inputbox?...

Ad Hoc Reporting #2
Hi, I am putting together an ad hoc report in Excel. My query is in the VB editor. I am trying to specify criteria Select x,y,z where... in the WHERE, I am struggling. I want to take a range of ids specified in the worksheet, F2:F55. So Select x, y, z where IDs IN ( F2:F55 ) I tried to use WHERE IN ( Range(F2:F55) ) but it did not like it. What syntax should I use to make this work? Thanks! ...

odd files created
Every time I open and edit an excel spreadsheet on a network share, small odd files get created. They are usually no larger than 25-40k and don't have any extensions to them. Looking at the properties page for any file, the file description says File. Anyone know what this is from or how to get rid of them? Permissions are setup correctly for me, Word files don't have this behavior. Excel 2000 SP3 Thanks, Hi Brian, A file the same size as the workbook would be created in the same directory as the workbook. The filename would be nonsensical (or appear to be random) character...

Creating Charts for Use in Powerpoint
I have a series of charts that I need to create for a Powerpoin presentation. I thought it would be easy to just create the charts/graphs in Exce and then just cut and paste to the PP-Slides. I'm finding that it isn' quite that easy. Here are some of my problems that I hope someone might be able to she some light on and give me some direction. 1. In Excel how do you control the size of the chart? - When I create a chart 1 and define the data ranges I get a goo looking chart then I thouht, why reinvent the wheel so I just copy th chart to create chart 2 and change the data range, but...

Create Login with T-SQL
Hello, I am creating, using T-SQL, a database with its file groups, tables, constraints, etc. Can I also create, using T-SQL, a Login with Username and Password to access that database? Usually, I go to Security > Logins of SSMS to manually create the login. However, if I could do the same using T-SQL I would have it all made automatically. Thanks, Miguel EVERYTHING that the GUI does winds up as tsql executions on the server. :-) Most of them you can generate a script for using the Script menu in the upper left of the current dialog window. This is a great way to ...

Create Calendar mtg invite template to avoid retyp'g conf no's?
I want to create a calendar template so that I can avoid retyping my conference call details in the location field everytime I create a new invite. How do I do this? In what version of OUtlook? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 "GM" wrote: > I want to create a calendar template so that I can avoid retyping my > conference call details in the location field everytime I create a new > invite. How do I do this?...

Evaluate Yes/No Field Based on User Input
Hi. I have a field that is set to Yes/No. I want to ask the user a question and based on their response (whether they type yes or no) I want the query to check the field and return all records marked yes is they type yes and all other records if they type no. How can I do this? Also, could I present them with a simple text box (having yes and no choices) or maybe a check box so they won't have to type anything? If you help me with the first part, this question is a bonus. I'll be happy with just the first question answered. Thanks! A Yes/No field actually stores -...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...