Update query field not displaying

Rats

Registered User.
Local time
Today, 15:38
Joined
Jan 11, 2005
Messages
151
Appreciate some help on a update query if possible. In this query I wish to total two sets of figures in about five seperate queries and then export them to another DB. the Dsum function is fine and will produce a result but that result needs to pick up the record ID (called PlanID) which is the primary key for the records being updated. I have struggled with this a bit as no matter what I did the PlanID field would not even display in the query results.

The section of code that is highlighted will now display an empty field and as such the rest of the query is blank. If I manually insert the PlanID figure into the record that the query is updating the other figures will appear.

How can I change this query to add the Planid to the update after it gets it from the customers form?

Thanks in advance.

Code:
UPDATE Customers INNER JOIN ExportBudgetTotalsTbl ON Customers.PlanID = ExportBudgetTotalsTbl.planid SET
 ExportBudgetTotalsTbl.FoodHouseholdEx = DSum("[totalscash]","[budgetbase1qry]")+DSum("[totalcredit]","[budgetbase1qry]"),
 ExportBudgetTotalsTbl.Lifestyle = DSum("[totalscash]","[budgetbase2qry]")+DSum("[totalcredit]","[budgetbase2qry]"), 
ExportBudgetTotalsTbl.SavEmerg = DSum("[totalscash]","[budgetbase3qry]")+DSum("[totalcredit]","[budgetbase3qry]"), 
ExportBudgetTotalsTbl.BillsFuel = DSum("[totalscash]","[budgetbase4qry]")+DSum("[totalcredit]","[budgetbase4qry]")+DSum
("[totalscash]","[budgetbase5qry]")+DSum("[totalcredit]","[budgetbase5qry]"), ExportBudgetTotalsTbl.Fixedpayments = DSum
("[totalscash]","[budgetbase6qry]")+DSum("[totalcredit]","[budgetbase6qry]"), 
[COLOR="Red"]ExportBudgetTotalsTbl.planid = 
[customers].[planid]

WHERE (((ExportBudgetTotalsTbl.planid)=[forms]![customers].[planid][/COLOR]));
 
Last edited:
I don't understand how you can update PlanID when it is the field you are joining on. The values must already be equal for the join to work.

It probably isn't helping that your form and control have the same names as the fields. Access does this by defaul but it causes all sorts of problems.
 
Thanks Neil. I found I should have using an append query for the task. Changing to that rectified the problem.
 

Users who are viewing this thread

Back
Top Bottom