Update using Sum -Error

Question

Keep on getting error

you tried to execute a query that does not include the specified expression

My SQL looks as follows:

UPDATE TblField LEFT JOIN TblTempStats ON TblField.DomainCatID = TblTempStats.DomainCatID 
SET TblTempStats.EmptyFields = Sum(IIf([fieldname] Is Null,1,0));

Any ideas as to why?

Missing a From TblField?

QHarr
– QHarr

2017-10-13 07:31:44 +00:00
Commented Oct 13, 2017 at 7:31 — QHarr
– QHarr, Commented Oct 13, 2017 at 7:31

Erik A · Accepted Answer · 2017-10-13 08:10:27Z

1

You should use a domain aggregate for this in my opinion, it avoids the error:

UPDATE TblTempStats 
SET TblTempStats.EmptyFields = 
    DSum(
       "IIf([fieldname] Is Null,1,0)", 
       "TblField",
       IIf(
          TblTempStats.DomainCatID Is Null,
          "TblField.DomainCatID Is Null",
          "TblField.DomainCatID = " & TblTempStats.DomainCatID
       )
    )

edited Oct 13, 2017 at 8:10

answered Oct 13, 2017 at 7:35

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jext Over a year ago

Hi, get error that I need to use an updateable Query - any other ideas? Thank you

Jext Over a year ago

Works for all values - except if DomainCatID is null, which I can live with, but do you think there is a way to include null domain cat iD - it is present in the tbltempstats (domaincatid null is a value)

Collectives™ on Stack Overflow

Update using Sum -Error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related