If Statements in and Expression

[Guest]

So I have a calculated field in an query that looks like this:

Product Thickness: [Depth To Water]-[Depth To Product]

If the value of that calculation is a null value (if [Depth to Product]
contains a null value) I want the field to be 0.0, can I write an if
statement in the expression? Or do I need to do it somewhere in VBA?

Thanks for the help gang!

 

[Guest]

Hi Larry,

Use the Nz function to convert any nulls to zero:

Product Thickness: Nz([Depth To Water],0) - Nz([Depth To Product],0)


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

So I have a calculated field in an query that looks like this:

Product Thickness: [Depth To Water]-[Depth To Product]

If the value of that calculation is a null value (if [Depth to Product]
contains a null value) I want the field to be 0.0, can I write an if
statement in the expression? Or do I need to do it somewhere in VBA?

Thanks for the help gang!

 

[Guest]

Yes,

example of expression:

iif(test criteria,yes part,no part)

you do use two i's.

example:

iif(1>0,"true","false")

result:
true

iif(1<0,"true","false")

result
false

 

[Guest]

Yes I agree with Tom. In fact this is preferred for what you are trying to do.

 

[Guest]

This one didn't work for me, the IIf did work tho.

Thanks!!!!

 

[Guest]

Hi Larry,

There's no reason that the Nz function shouldn't have worked for you....


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

This one didn't work for me, the IIf did work tho.

Thanks!!!!
__________________________________________

:

Hi Larry,

Use the Nz function to convert any nulls to zero:

Product Thickness: Nz([Depth To Water],0) - Nz([Depth To Product],0)


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

So I have a calculated field in an query that looks like this:

Product Thickness: [Depth To Water]-[Depth To Product]

If the value of that calculation is a null value (if [Depth to Product]
contains a null value) I want the field to be 0.0, can I write an if
statement in the expression? Or do I need to do it somewhere in VBA?

Thanks for the help gang!