Null Values...

[ukgthor]

I have a series of values within a table that includes a number of null values. I would like to include this field within a query, however if the value is null I would like to show a 0, if the field is not null, I would like it to show the original value.

I have tried to use the IsNull in an iif formula without luck. Can anybody help me ?.

Thanks in advance.

PS: If I was conducting this in Excel I would use the following =IF(J3="",0,J3)

 

[John_W]

Include a calculated field in your query with an expression in the form:

nz([MyFieldname],0)

 

[ukgthor]

John_W - Thanks very much. This has solved my issue.

Just for learning purposes, what does nz represent ?

Thanks again.

 

[ukgthor]

John_W - This has worked by giving me the value of 0 for every null value, however on further investigation these values are now exported as text and not values and there is not an option to format this field as a number in Access within the properties tab.

Any ideas on how to resolve this ?

 

[John_W]

nz is a function which does just what you're using it for - there's more specific detail in Access Help.

You could try wrapping it inside the CLng function to convert to a number - so CLng(nz([MyFieldname],0)). Haven't actually tried this, but it should work I think...

 

[ukgthor]

John_W, thanks again. It has worked a treat.