[coachdan32]

I am having problems with a report page that shows monthly stats for our trainers. The numbers are entered into an Access DB weekly and this report should report a running total on the stats, but when the trainer has no stats entered on one of the weeks it is calculating their schedule adhereance as 0. Trainers that have stats on all weeks work fine. The SQL statement I have attempts to take a total of all weeks (production hours) and divide it by a total of all weeks (scheduled hours) - so in theory if one week is null, it shouldn't matter. There must be something wrong in the syntax though because it is not working. Here is the line that calculates that:

SQLSP = SQLSP & " ((Sum([StdtProdAS]![W1ProdHrs]+[StdtProdAS]![W2ProdHrs]+[StdtProdAS]![W3ProdHrs]+[StdtProdAS]![W4ProdHrs]))/(Sum([StdtProdAS]![W1AdjSchedHrs]+[StdtProdAS]![W2AdjSchedHrs]+[StdtProdAS]![W3AdjSchedHrs]+[StdtProdAS]![W4AdjSchedHrs]))) AS SA, "

There is also a data validation check to handle null values:

If MArT(6,x) > 0 Then
varSA = FormatPercent(MArT(6,x))
Else
varSA = 0
End If

Can anyone spot anything wrong or offer a better way to get around this problem?

[Rockn]

You need to find a way to convert your fields to zero if they are null prior to adding them.

[coachdan32]

I changed the SQL statement, but I have a problem with the parenthesis grouping. I did the front half of the statement (before the "/" sign) first and it worked. After I did the other half I got the error message returning the SQL code. Can anyone spot a problem?

SQLSP = SQLSP & " Sum(IIf([StdtProdAS]![W1ProdHrs]>0,[StdtProdAS]![W1ProdHrs],0)+IIf([StdtProdAS]![W2ProdHrs]>0,[StdtProdAS]![W2ProdHrs],0)+IIf([StdtProdAS]![W3ProdHrs]>0,[StdtProdAS]![W3ProdHrs],0)+IIf([StdtProdAS]![W4ProdHrs]>0,[StdtProdAS]![W4ProdHrs],0))/(Sum(IIf([StdtProdAS]![W1AdjSchedHrs]>0,[StdtProdAS]![W1AdjSchedHrs],0))+IIf([StdtProdAS]![W2AdjSchedHrs]>0,[StdtProdAS]![W2AdjSchedHrs],0))+IIf([StdtProdAS]![W3AdjSchedHrs]>0,[StdtProdAS]![W3AdjSchedHrs],0))+IIf([StdtProdAS]![W4AdjSchedHrs]>0,[StdtProdAS]![W4AdjSchedHrs],0))))) AS SA, "

[Rockn]

I thought you were testing for Nulls not >0

Try this:
IIF(ISNull([StdtProdAS]![W1ProdHrs],0,[StdtProdAS]![W1ProdHrs]))

Still seems like a lot of coding for converting Nulls to 0

How about creating a variable for each field in the database and just add up the variables.

[coltsfan]

I am assuming that the data is being entered via an Access Form. If so then the problem is that the form uses a textbox for entry of your numeric value.

Edit the properties of the textbox and set the default value to 0. This will handle the string to single/double conversions which take place and eliminate the storing of null values in your fields.

This should fix the problem. But you should still check the denominator's sum for > 0 to avoid 'divide by 0' errors.