[coachdan32]

There is a problem with my syntax in the following SQL statement:

SQLSP = SQLSP & " Sum(IIf([StdtProdAS]![W1AHT]="",0,[StdtProdAS]![W1AHT])+IIf([StdtProdAS]![W2AHT]="",0,[StdtProdAS]![W2AHT])+IIf([StdtProdAS]![W3AHT]="",0,[StdtProdAS]![W3AHT])+IIf([StdtProdAS]![W4AHT]="",0,[StdtProdAS]![W4AHT])) AS AHTSum, "

I am trying to test for a null value before performing the addition and if it contains null, use "0" as the number in the addition.

[coachdan32]

Have also tried:

SQLSP = SQLSP & " Sum(IIf(IsNull([StdtProdAS]![W1AHT],0,[StdtProdAS]![W1AHT])+IIf(IsNull([StdtProdAS]![W2AHT],0,[StdtProdAS]![W2AHT])+IIf(IsNull([StdtProdAS]![W3AHT],0,[StdtProdAS]![W3AHT])+IIf(IsNull([StdtProdAS]![W4AHT],0,[StdtProdAS]![W4AHT]))))) AS AHTSum, "

[Rockn]

What are you getting for a result and what do you want the null values to be 0? It looks like you need to seperate each value and then add it together instead of in one grouping. I am starting to think that they really aren't NULL values at all.

[Rockn]

Try this and see if it works. After you create your recordset create some string variables based on the values you are testing for NULL, something like this:

strW1AHT = Iif(InNulll([StdtProdAS]![W1AHT],0,[StdtProdAS]![W1AHT]))

and do the same for the other three fields you want to add up. Now just add up the variables. You can now use them in other areas of the page again as long as the recordset is open.

Add up the strings:

Sum strW1AHT + strW2AHT + strW3AHT + strW4AHT

[coachdan32]

I am getting a Provider for ODBC Drivers error '80040e14' - Syntax error in query expression and then it is regurgitating the SQL code in question as posted above. I do want it to return 0 if there is no data present. I will give your suggestion a try also.

[Rockn]

BTW, you cannot use the IIF in ASP, you have to use the IF THEN and END IF