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

[jex57]

Hi,
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? Sorry for all the questions, am battling my way through


Thank you

 

[stumac]

What are you trying to do?

You are joining a table to itself, which isn't that unusual, however you are joining on the same field which is.

You also have a Sum which doesn't make sense without a group query and do you really have a field called [Fieldname] ?

If you explain what you are trying to do (with table and field names) then hopefully someone will be able to help

 

[jex57]

What are you trying to do?

You are joining a table to itself, which isn't that unusual, however you are joining on the same field which is.

You also have a Sum which doesn't make sense without a group query and do you really have a field called [Fieldname] ?

If you explain what you are trying to do (with table and field names) then hopefully someone will be able to help

Hi,

I managed to come right - what I did was:


newSQL1 = "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 ) )"

 

[xenou]

Do you actually have a field in your table called "EmptyFields"? That's awfully awkward SQL but it's hard to tell what you are doing without some example. A little strange to call a table Field also (tblField). Are you sure you know what a field is . I can't help but be a little curious about this one now.