459,568 Members | 1,688 Online
Need help? Post your question and get tips & solutions from a community of 459,568 IT Pros & Developers. It's quick & easy.

# Access expression builder syntax and null value issue

 P: 2 Hi all, XP, office 2003 Pro, MS access. Fairly newby. I'd like to run simple calculations on multiple and specific colums like avg and sd. The problem is that the empty cells cause the functions avg, sum et cetera to fail. Even a simple sum fails, if one of the columns has an empty cell. The cells are blank, because a certain parameter has been measured, but has given no response. Entering zero is not possible, since likely number. Entering null gives weird results; sum instead of average and only for one row (sample). bac: [ratioPOM_06]![i_C14_0]+[ratioPOM_06]![i-C15_0] only returns a value if both i-14 and 1-15 are valid. I wrote the expression like this: Avg([concentration zoo mit 0]![i-C14:0] , [concentration zoo mit 0]![i-C15:0] , [concentration zoo mit 0]![ai-C15:0] , [concentration zoo mit 0]![i-C16_0]) I would be grateful for suggestions and a correction of my syntax. Cheers Benjen Feb 27 '08 #1
4 Replies

 P: 83 The Nz function will change all null valuies to a zero so that your calculations can be determined. The below is an example if you were to add 2 fields together. [PHP]Total Worked': Nz([Total Worked],0)+Nz([Worked'],0)[/PHP] Let me know if this helps. Feb 27 '08 #2

 P: 2 The Nz function will change all null valuies to a zero so that your calculations can be determined. The below is an example if you were to add 2 fields together. [PHP]Total Worked': Nz([Total Worked],0)+Nz([Worked'],0)[/PHP] Let me know if this helps. Well, thanks for the quick reply. Though, I have written now: avg(Nz( [ratioPOM_06]![i_C14_0] , 0) +Nz ( [ratioPOM_06]![i-C15_0] ,0)) but access then denies calculation, because of invalid syntax. Can you see the mistake? ratioPOM_06 is the table and i_C14_0 one of the column header names. Hope this is not to embarrassing. Cheers. Feb 27 '08 #3

 P: 83 [PHP]Average: Avg(Nz([i_C14_0],0)+Nz([i_C15_0],0))[/PHP] This works perfectly for me. There is no need to include the Table name if the fields are pulling from the same table. Try this exact code in your query...then let me know what happens. Thanks Feb 28 '08 #4

 P: 83 I also just noticed that one field is displayed like this: i_C14_0 and the other like this i-C15_0... Feb 28 '08 #5