DSum in Update Query 1

[incagold]

Hi,

I am a relative newbie to MS ACESSS and I am having a terrible time trying to use DSUM in a SQL Statement to update a table. No matter what I do my answer gives me 0's for result. I am sure it is my newness and the answer is probably quite simple. But I am struggling.

This is the code:

UPDATE SUMMARY SET SN4_BE = DSum('BOE','SUMMARY_TMP','SER_NO'='SN4'), SN4_PO = DSum('O_CST','SUMMARY_TMP','SER_NO'='SN4'), SN4_RC = DSum('A_CST','SUMMARY_TMP','SER_NO'='SN4'), SN4_PF = DSum('PJ_FNL_CST','SUMMARY_TMP','SER_NO'='SN4');

SUMMARY is the final table and SUMMARY_TMP holds the data which needs to be evaluated.

Any suggestions and help would be sincerely appreciated.

BEF

 

[PaulBricker]

If you are using this in a module try printing the sql statement to the debug window. It may help get you straightened out. It looks like the syntax in the Where argument is not right.

'SER_NO'='SN4'

Depending on what SN4 is, it could be
'SER_NO = SN4' - if SN4 is an actual value
or
'SER_NO = ' & SN4 - SN4 is a variable.

Also, I assume you are using single quotes because you have this whole thing wrapped in a string.

Paul

 

[GoDawgs]

Another problem...the field names (the first and third arguments in dsum) need to be in brackets too I believe...something like this:

DSum("[A_CST]","SUMMARY_TMP","[SER_NO]='SN4'&quot

Hope that helps.

Kevin

 

[incagold]

Hi,

Thanks guys for the quick response. Am still having a problem. Tried to put in an ACCESS Update query and still got zeros. I know the table (SUMMARY_TMP) has values because if I do a query and sum these fields I get values of BOE = $7,286,103.15; O_CST = 2,483,712.26; A_CST = 62,446.99; PJ_FNL_CST = 6,750,942.86. The SQL for the test query is as follows:

UPDATE SUMMARY
SET SUMMARY.SN4_BE = DSum("[BOE]","SUMMARY_TMP","[SER_NO]='SN4'&quot,
SUMMARY.SN4_PO = DSum("[O_CST]","SUMMARY_TMP","[SER_NO]='SN4'&quot,
SUMMARY.SN4_RC = DSum("[A_CST]","SUMMARY_TMP","[SER_NO]='SN4'&quot,
SUMMARY.SN4_PF = DSum("[PJ_FNL_CST]","SUMMARY_TMP","[SER_NO]='SN4'"

What am I missing? Again you help and suggestions are sincerely appreciated.

BEF

 

[GoDawgs]

Why do you need to do a dsum on this? Set up a query that gives you those four sums, then run an append query to append the data to the summary table. Instead of an update you can run a delete and then an append. Hope that helps...let me know what I missed (it's always something).

 

[incagold]

Thanks GoDawgs,

That is what I did. I guess being new sometimes we try to make things more difficult than they need to be. The solution works very well and resolves the problem. Thanks again for taking the time to reply and for your help.

An appreciative newbie,

BEF