Need help inserting comment box into Access invoice report
in [Reports]
|
From: lemagr on 24 Mar 2010 07:38 Hi, I haven't used Access much, but I needed to get my invoices databased and printable instead of handwriting them like I have been. I downloaded the billing and time template that appears on the Access opening page and have figured out everything but this one problem. I have need for a box in which I can detail the work done and provide customers with special instructions. These instructions aren't of much use to me, so I don't need them to be sortable or anything of that nature. I just want to be able to either include the "notes" field that is in the "project details" form, or have a useable text box that I can type (onto the invoice) into once Access completes my invoice report right before I hit print. Here is what I have done so far. I have added a text box in design mode onto the invoice report. I have tried using every occurence of "Notes" in all of the tables and forms. When trying to directly access the field from the "Projects" table, I get the error "The specified field "Projects.Notes" could refer to more than one table listed in the FROM clause of your SQL statement." When I try to reference the form that is used to enter the data to the table, the box I have created does not display the note, but rather "#Name?" I then created a new field in my projects table called "Description". That is how it sets now, but I am still getting the FROM clause popup. I know that is the only table with a field names Descriptions so I'm lost. Any help would be greatly appreciated. Here is my SQL SELECT Projects.ID AS ProjectID, Projects.*, [Customers Extended].*, [Employees Extended].*, [Payments By Project].[Total Payments], [Total Expenses By Project].[Total Expenses], [Billing Hours by Project]. [Total Billables], [Total Billables]+[Total Expenses]-[Total Payments] AS Outstanding, [Customers Extended].[City] & ", " & [Customers Extended].[State/Province] & ", " & [Customers Extended].[Zip/Postal Code] AS CustCityStateZip, Projects.Description FROM ((((Projects LEFT JOIN [Employees Extended] ON Projects.Owner = [Employees Extended].ID) LEFT JOIN [Total Expenses By Project] ON Projects.ID = [Total Expenses By Project].Project) LEFT JOIN [Payments By Project] ON Projects.ID = [Payments By Project].Project) LEFT JOIN [Billing Hours by Project] ON Projects.ID = [Billing Hours by Project].Project) LEFT JOIN [Customers Extended] ON Projects.Customer = [Customers Extended].ID;
From: Jeanette Cunningham on 25 Mar 2010 02:45 It would help if you told us what the template was called and which version of access you are using. Note that it is not possible to type into a report. Any notes about a project need to be entered in a form. If you have a 'notes' field in more than one table, you need a way to let access know which 'notes' field you are referring to. There are 2 ways to do it (that I know of). One way is to change the names of those 'notes' fields in each table, so that each one is different and access won't get confused. The other way is to change the SQL to tell access which 'notes' field to use. In your sql you have Projects.*, Replace Projects.* with [Projects].[ProjectsName], [Projects].[ProjectDate], [Projects].[Notes], . . . etc until you have included the necessary fields from Projects. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "lemagr" <lemagr67(a)gmail.com> wrote in message news:601f6abd-2801-4fa4-8b76-3dfa68a81178(a)x12g2000yqx.googlegroups.com... > Hi, I haven't used Access much, but I needed to get my invoices > databased and printable instead of handwriting them like I have been. > I downloaded the billing and time template that appears on the Access > opening page and have figured out everything but this one problem. I > have need for a box in which I can detail the work done and provide > customers with special instructions. These instructions aren't of much > use to me, so I don't need them to be sortable or anything of that > nature. I just want to be able to either include the "notes" field > that is in the "project details" form, or have a useable text box that > I can type (onto the invoice) into once Access completes my invoice > report right before I hit print. > > Here is what I have done so far. I have added a text box in design > mode onto the invoice report. I have tried using every occurence of > "Notes" in all of the tables and forms. When trying to directly > access the field from the "Projects" table, I get the error "The > specified field "Projects.Notes" could refer to more than one table > listed in the FROM clause of your SQL statement." > > When I try to reference the form that is used to enter the data to the > table, the box I have created does not display the note, but rather > "#Name?" > > I then created a new field in my projects table called "Description". > That is how it sets now, but I am still getting the FROM clause > popup. I know that is the only table with a field names Descriptions > so I'm lost. > > Any help would be greatly appreciated. > Here is my SQL > > SELECT Projects.ID AS ProjectID, Projects.*, [Customers Extended].*, > [Employees Extended].*, [Payments By Project].[Total Payments], [Total > Expenses By Project].[Total Expenses], [Billing Hours by Project]. > [Total Billables], [Total Billables]+[Total Expenses]-[Total Payments] > AS Outstanding, [Customers Extended].[City] & ", " & [Customers > Extended].[State/Province] & ", " & [Customers Extended].[Zip/Postal > Code] AS CustCityStateZip, Projects.Description > FROM ((((Projects LEFT JOIN [Employees Extended] ON Projects.Owner = > [Employees Extended].ID) LEFT JOIN [Total Expenses By Project] ON > Projects.ID = [Total Expenses By Project].Project) LEFT JOIN [Payments > By Project] ON Projects.ID = [Payments By Project].Project) LEFT JOIN > [Billing Hours by Project] ON Projects.ID = [Billing Hours by > Project].Project) LEFT JOIN [Customers Extended] ON Projects.Customer > = [Customers Extended].ID;
|
Pages: 1 Prev: automatically naming a report Next: Displaying date parameter on a multiple page report |