Changes to query not being saved.

[chucktr]

Database is Customers... usual stuff.
A Routes report contains Account #, First and Last names, Address, last serviced date ...etc. Reading 3 tables in query.
Charges, Routes and Employee. Charges contains account info, Routes has location info and Employees contains driver info.

Accnt #, Last, First, Address, City, St, Zip, Phone, Last Srvd

I am trying to add a Price field from Charges in between the Phone and Last Srvd. I edit qryRoutes to insert the Column, save the query and then insert the field in the Report. Upon running the code, it asks for a Paid value. The field is still in the Report but NOT in the Query???? Is there something special that I am missing on saving changes. I have tried a number of times and ways to make this change.
Thanks in advance
Chuck

 

[JerryKlmns]

>it asks for a Paid value
Your query asks for that or your report?

Does your query run fine?

Is the added textbox bound to the new field?
Was there such a field "Paid" before playing with the report?

 

[chucktr]

How to answer... I believe that most is my lack of knowledge.
Charges links to Routes via Acnt# and Routes links to Employees via Route#. Each Column is as follows:
Field:
Table:
Total:
Sort:
Show:
Criteria:
or:
When I insert a Column for Paid, which is from Charges, it automatically places Paid in Field, Charges in Table and Group By in Total. Now then, thru all this I have realized that Group By is wrong for this field. In the table it is currency. I have tried Max and Last but they change the name to MaxOfPaid or LastofPaid in the field list. I place that value in the report field. When I run the query it will ask for the Paid value in a dialog box. If I enter a value, it will run, except it has the same value for every customer. I exit and look at the query and the column that I just inserted is gone! I just can't believe that I am that stupid or that this is all that hard. I just purchased this version of Office 2003. Is there possibly an update that will fix this or am I doing something really stupid?? Thanks

 

[JerryKlmns]

When in design view of the query, go to menu-->Query-->Parameters If you find something there... delete it.

On menu-->View-->SQL View
Copy all that you see and paste it here

If you need an alias in a query field, use
myAlias :[FieldName]

At the text box in your report, right click it, select properties. Go to Data tab and select the field of your query that bounds to the text box

 

[chucktr]

SELECT Employees.RouteNo, Employees.Last, Employees.First, Routes.AccountNo, Routes.Last, Routes.First, Routes.Address, [Routes].[City] & ' ' & [Routes].[St] & ', ' & [Routes].[zip] AS CSZ, Routes.City, Routes.St, Routes.Zip, Routes.Phone, Routes.Address, Max(Charges.Serviced) AS MaxOfServiced, Last(Charges.Paid) AS LastOfPaid
FROM Charges RIGHT JOIN (Routes INNER JOIN Employees ON Routes.RouteNo = Employees.RouteNo) ON Charges.AccountNo = Routes.AccountNo
GROUP BY Employees.RouteNo, Employees.Last, Employees.First, Routes.AccountNo, Routes.Last, Routes.First, Routes.Address, [Routes].[City] & ' ' & [Routes].[St] & ', ' & [Routes].[zip], Routes.City, Routes.St, Routes.Zip, Routes.Phone, Routes.Address, Routes.RouteNo
HAVING (((Routes.RouteNo)=1));

I am presuming that LAST means the last value, which is really what is wanted. As stated in the first post this report is just a display of customer for a Route. I am only trying to insert the last amount that they paid. It is reading and properly displaying the last Serviced date which you will notice is also from charges. However, for some reason they called it the MaxOfServiced. I guess that gets the latest date. I could understand all this if it just complained about values but it doesn't. It removes the code from my query.????

 

[chucktr]

I found the problem. Basically, a Routes form has a print button that displays the data in a List for a selected route. Now then, I was right clicking on the List and found that it was using qryRoutes. So, I was changing qryRoutes and the report Routes. However, after running the code ... the part I added to the query was gone!! Further investigation to the Form for Routes and digging into the Print button I found that there was SQL code tied to the Print button. I added , Last(Charges.Paid) AS LastOfPaid to the embeded code and lo and behold it worked!! I will look for this embededness in the future. Thanks for your help ... and believe it or not you did help.
Thanks,
Chuck

 

[JerryKlmns]

Chuck

Glad to be of help