Calculating Sum of Values, Problem encountered

[Waheed2008]

In an access form, I am using a combo box and a label object. I want that when I select any value in combo box, its value be picked up and match in a table, and add corresponding values of all the entries with the same combo box value.

I am using this function to calculate SUM of values from a table:

Me.lblTotalExpense.Caption = DSum("[Expenses]", "Issues Log", "[CostCodes] =""" & Forms![B]Issues Log[/B]!cmbCodes & """")

Issues Log in Forms!Issues Log!cmbCodesis a form name and it is not being accepted because of space charcter in name. I cannot change form name because it is being used at many other places and I dont know how many places are there for which changes will be required.

Is there any solution for this problem.

I 'll be realy very thankfull for this help.

 

[pbaldy]

Try bracketing the table name just as you bracketed the field name.

 

[Waheed2008]

Dear Paul,
I have bracketed the field name and this is my line of code:

Me.lblTotalExpense.Caption = DSum("[Expense]", "Issues Log", "[CostCodes] =""" & Forms![Issues Log]!cmbCodes & """")

But now it is showing the following message:

Run-time error '94':
Invalid Use of Null

What could be the problem?
Plz Help me...
Thanks

 

[pbaldy]

I also meant here:

Me.lblTotalExpense.Caption = DSum("[Expense]", "[Issues Log]", "[CostCodes] =""" & Forms![Issues Log]!cmbCodes & """")

Have you reviewed this, to make sure your syntax is appropriate for the data type?

http://www.mvps.org/access/general/gen0018.htm

 

[Waheed2008]

Thanks for reply.

I have used the above syntax. Actualy the problem was that I was summing up the field which has null values except few cells. So, DSum returned Null after summing all nulls of the set. It is not allowed and DSum cannot return Null value. Now I have put 0 at every null and problem is resolved.

Thanks again

 

[khawar]

you can also use this
nz(DSum("[Expense]", "[Issues Log]", "[CostCodes] =""" & Forms![Issues Log]!cmbCodes & """"),0)
Instead of keying in 0 in every null field

 

[Waheed2008]

Thank you very much for this information. It realy work well...
Now I am using Dsum with nz
Thanks