Query works when run, but not when previewed

[melaniecarr23]

I have an update query that does not calculate correctly when I preview it in datasheet view (it shows 0 for the field padjs), however when I run the query - it updates with the correct number according to the calculation I'm using to update the field. WHY? Here is the query giving me the issues:

UPDATE costs, (plans INNER JOIN dClient ON plans.pid = dClient.ClientId) INNER JOIN plan_calculations ON plans.plan_id = plan_calculations.plan_id SET plan_calculations.padjs = [planv]-[pexams], plan_calculations.dmv = IIf(Round(([max_amt]/[98940])-0.6,0)<0,0), plan_calculations.lmv = IIf([dmv]>[max_visits],[max_visits],[dmv]), plan_calculations.maxv = [lmv]-[used_visits], plan_calculations.pmaxv = IIf(([pocket_max]/[98940])<[maxv],[pocket_max]/[98940],0)
WHERE (((plans.plan_id)=DMax("[plan_id]","plans")) AND ((costs.cost_id)=1));

I thought I would need to put planv and pexams in the query so it could pull that information to do the calculation, but it's not showing correctly in datasheet view. Works, though when I run it. WEIRD!

Any insight as to WHY it's doing this would be wonderful. You'd be saving a girl from going batty at her PC.

Thanks,
Melanie

 

[melaniecarr23]

Never mind!!! I got it!!! YAY! I can't base one calculation on a calculation made from a previous field in the query - THAT was my problem. So, I'm going to make a few update queries and use a macro to sequentially update the table I need to run my reports. WOOHOO!!!

Thanks so much for having this board.

Melanie

 

[PHV]

It's normal behaviour as it shows the data that'll be affected if you run the action query, not the result.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886