Error due to Null Values - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Error due to Null Values

Walter Hamilton

5 years ago

I have a calculated field in a query based off of two other calculated fields which get data from another query. I'm getting #Error on rows that have null values in the fields used for the calculation.

Example: Funded Remaining: [Funded]-[Cumulative Invoice]

Both [Funded] and [Cumulative Invoice] get their values from an underlying expression.

Is there something I can add to the Funded Remaining Expression to have the returned value as 0 if there is a NULL value in the [Funded] filed.

	Alex Hedley @Reply 5 years ago
NZ Function

Walter Hamilton OP @Reply

5 years ago

Alex,

Thanks. That worked. My expression is now:

Total Value: Format(Nz(DSum("[Total Value]","[qry_Customer_ChargeCodes]","[Major Command_ID]=" & [Major Command_ID]),0),"Currency")

I now have another issue: I'm calculating [Burn Rate] using the following:

Burn Rate: [Funded Remaining]/[Funded]

This works for the rows with values but the rows that have $0.00 in them due to the Nz funtion return a #Num!

	Adam Schwanz @Reply 5 years ago
I'm assuming you're dividing by 0 now. You could just make an if statement. If Funded = 0 Then Burn Rate: [Funded Remaining]/1 Else Burn Rate: [Funded Remaining]/[Funded] End If

	Adam Schwanz @Reply 5 years ago
Don't even need to divide by 1 I guess, morning brain. If Funded = 0 Then [Burn Rate]= [Funded Remaining] Else [Burn Rate]= [Funded Remaining]/Funded End If

	Adam Schwanz @Reply 5 years ago
And I'm just reading your field names and see that wouldn't really make sense for trying to get a percentage. I'm just going to go get some coffee. If Funded = 0 Then [Burn Rate]= 0 Else [Burn Rate]= [Funded Remaining]/Funded End If

	Alex Hedley @Reply 5 years ago
IIF Function

	Scott Axton @Reply 5 years ago
A Google search for "MS ACCESS #NUM ERROR" gives the following results #error search Basically it indicates text where a number is expected. you may have to convert the result to a number.

	Richard Rost @Reply 5 years ago
Throw some VAL or CLNG functions in there. Awesome tips, guys.

	Walter Hamilton OP @Reply 5 years ago
Thank you all. The winning expression is: Burn Rate: IIf([Funded]=0,0,[Funded Remaining]/[Funded])

	Richard Rost @Reply 5 years ago
Told ya my boys are the best. :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View