SQL Sum with optional fields

[ClariceT]

Hi!

I'm new here and I have a problem: in a query I need to add fields that are in a linked xls. I used the code below and it worked:

SELECT [tblReferences].Cod, [tblReferences].Name, Nz([Ref_1])+Nz([Ref_2])+Nz([Ref_3])+Nz([Ref_4])+Nz([Ref_5])+Nz([Ref_6]) AS Ref_Total
FROM [tblReferences]
ORDER BY [tblReferences].Name;

The problem is that the fields 'Ref_5' and 'Ref_6' will not always be present in the linked file, and when this happens it gives an error.

What I need: That the inclusion of these fields in the sum is conditioned to their presence in the linked table, therefore being ignored if they are not in the file.



Can someone help me?

[CJ_London]

you'll need to use some vba code to check for the existence of the field(s) and then dynamically build your query

perhaps something like

dim fld as DAO.field
dim sqlstr as string


sqlStr="SELECT [tblReferences].Cod, [tblReferences].Name"
for each fld in currentdb.tabledefs("name of your linked table").fields
    if fld.name like "ref_" then sqlstr = sqlstr & ", nz(" & fld.name & ")"
next fld

sqlstr=sqlstr & " AS Ref_Total FROM [tblReferences] ORDER BY [tblReferences].Name;"
debug.print sqlstr 'to check it has been correctly created

currentdb.querydefs("name of your query").sql=sqlstr

'or if you have a form
forms!myform.recordsource=sqlstr

this is aircode so their may be typos but should get you on your way