How to sum up records in Access vba (1 Viewer)

[Ben_Entrew]

Hi all,

I want to sum up specific records in a table called TNS.

All records with Division = Common should be summed up to

the records with Division = AK.

TNS contaıns columns Division, 012013,022013...,122013

I tried following without success:

Public Sub TEST22()

Dim strSQL As String

strSQL = "Update TNS Set [012013] = 0.5 * DLookup([012013], TNS, Division = 'COMMON') " & _
" Where Division IN ('AK') "

DoCmd.RunSQL strSQL
End Sub

It shows me following error:
Access doesn't recognize TNS as a valid field name or expression.
Something is missing in this syntax.

Thanks in advance.

Regards,
Ben

 

[Cronk]

Did you try the suggestions in the responses to your earlier question?

 

[Ben_Entrew]

Hi Cronk,

yes I read the responses and divided the statement assigning the dlookup to x.


x = DLookup("TNS_in_EUR", "TNS", "Division = 'COMMON' )

It tells me invalid use o Null
How can this happen, is there a problem in Access with small numbers?

 

[pr2-eugin]

How can this happen, is there a problem in Access with small numbers?

Uh, it can happen if you are trying to assign a Null to any type other than Variant. The Null return suggests that the function cannot find the data you are trying to. In other words, the value you are Looking for in the table for a specific criteria does not exist. So it throws a Null.

Use the Nz function to capture Null values.