acces sql query, need to change null to 0

[Philomena]

Hi there to anyone reading this,
I am using microsoft access as the database for a website. I am using vbscript as the language. I am querying a table with this sql:
SELECT sum(paid)*0.1
FROM elance
WHERE bidder = 'MMColParam' AND bidderpaid = 'no'

The query returns some null values which is stopping me from being able to display my results as a currency or number format. I need to know how to change the null values to a 0. I have tried using the isnull function but it is not working for me, it seems to have a different meaning in access

I would appreciate any advice from anyone who thinks they may have the solution for me.

Thankyou for reading this

Fil

 

[DSP]

Hi Philomena,

This little snippet should help.
Basically, for the field you want to avoid nulls on, you'll need to modify the query so that if performs this function on the field. This will ensure that nulls are returned as zeros

Function convertnulls(x)
If IsNull(x) Then
convertnulls = 0
Else
convertnulls = x
End If
End Function

You can test this in the immediate window with:

debug.print convertnulls("Hello&quot
debug.print convertnulls(1)
debug.print convertnulls(null)

hope this helps

 

[TheComputerLady]

I don't remember my vb at all well, but in an Access query you can in the QBE pane use the conditional IF statement.

If the original number where you are getting nulls is called [Value], then go to a blank column in the QBE pane and type NewValue: IIF(IsNull([Value]),0,[Value])

This means that if the value of [Value] is null (nothing entered), the program will enter a 0. in this [NewValue] field. If the [Value] field is not null, then it will enter the same value as in the [Value] field.

I hope this is clear.

 

[PWise]

try this
SELECT sum(nz([paid],0))*0.1
FROM elance
WHERE bidder = 'MMColParam' AND bidderpaid = 'no'

 

[cmmrfrds]

I don't believe the NZ function is supported in VB Scripting. You probably need to use the IIF function as was suggested.

IIF(yourfield IS NULL,0,yourfield)

 

[PWise]

cmmrfrds:
This is not VBscripting this MSACCESS

 

[Philomena]

Thankyou everyone for your help and advice. I will try all these possible solutions and let you know which one worked for me

thanks again

fil

 

[Philomena]

This worked thankyou very much
IIF(IsNull([Value]),0,[Value])