Need help inserting comment box into Access invoice report

  • Thread starter Thread starter lemagr
  • Start date Start date
L

lemagr

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;
 
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 said:
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;
Click to expand...
 
Back
Top