469,160 Members | 2,033 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,160 developers. It's quick & easy.

Unbound Control (if not populated, enter "0")

Probably something simple, but it's driving me up the wall...

In a report, I have an unbound control that is the total of 2 other
bound controls (currency) as follows:

unbound control =cash1 + cash2

However, it is possible that either cash1 or cash2 can be null, in
which case the unbound control displays nothing (apparently, addition
only works with a zero or above). What I want to do is something along
these lines (and for your entertainment at my poor VB, go ahead and
start laughing now):

unbound control =cash1 + cash2
if cash1 = null and cash2 not null, unbound control = cash2
if cash2 = null and cash1 not null, unbound control = cash1
if cash1 and cash2 are null, unbound control = "$0"

Items to ponder:
Cash 1 comes from a linked xls
Cash 2 comes from another linked xls
The idea being that I can save my xls files in a particular
location/name and the d/b is updated. xls changes every 2 or 3 days,
so this is working great, with the exception of the above. Updating the
xls' to show zero's instead of nulls would work, but at more than 500
records per xls (and not every item is in both xls' which is why I have
nulls!), the amount of time it would take to change nulls to zeros just
wouldn't be worth the effort.

There has to be a way to make an unbound control work like above. I
just don't know what it is. Anybody?

Oct 10 '06 #1
3 1858
For this, the Immediate if is the best option

=(iif(isnull([cash1]) = true, 0, [cash1])) + (iif(isnull([cash2]) =
true, 0, [cash2]))

I may have an extra pair of () there, but not 100% sure...

Let me know if that works.

THis works by setting the value of cash1 to 0 if it is null, and to
cash1 if it is not null.

Regards
Anthony

sc*********@japan.com wrote:
Probably something simple, but it's driving me up the wall...

In a report, I have an unbound control that is the total of 2 other
bound controls (currency) as follows:

unbound control =cash1 + cash2

However, it is possible that either cash1 or cash2 can be null, in
which case the unbound control displays nothing (apparently, addition
only works with a zero or above). What I want to do is something along
these lines (and for your entertainment at my poor VB, go ahead and
start laughing now):

unbound control =cash1 + cash2
if cash1 = null and cash2 not null, unbound control = cash2
if cash2 = null and cash1 not null, unbound control = cash1
if cash1 and cash2 are null, unbound control = "$0"

Items to ponder:
Cash 1 comes from a linked xls
Cash 2 comes from another linked xls
The idea being that I can save my xls files in a particular
location/name and the d/b is updated. xls changes every 2 or 3 days,
so this is working great, with the exception of the above. Updating the
xls' to show zero's instead of nulls would work, but at more than 500
records per xls (and not every item is in both xls' which is why I have
nulls!), the amount of time it would take to change nulls to zeros just
wouldn't be worth the effort.

There has to be a way to make an unbound control work like above. I
just don't know what it is. Anybody?
Oct 10 '06 #2

unbound control = val(cash1 & "") + val(cash2 & "")

This works because concatenating "" to a Null returns "" and Val("") = 0.
--

Terry Kreft
<sc*********@japan.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...
Probably something simple, but it's driving me up the wall...

In a report, I have an unbound control that is the total of 2 other
bound controls (currency) as follows:

unbound control =cash1 + cash2

However, it is possible that either cash1 or cash2 can be null, in
which case the unbound control displays nothing (apparently, addition
only works with a zero or above). What I want to do is something along
these lines (and for your entertainment at my poor VB, go ahead and
start laughing now):

unbound control =cash1 + cash2
if cash1 = null and cash2 not null, unbound control = cash2
if cash2 = null and cash1 not null, unbound control = cash1
if cash1 and cash2 are null, unbound control = "$0"

Items to ponder:
Cash 1 comes from a linked xls
Cash 2 comes from another linked xls
The idea being that I can save my xls files in a particular
location/name and the d/b is updated. xls changes every 2 or 3 days,
so this is working great, with the exception of the above. Updating the
xls' to show zero's instead of nulls would work, but at more than 500
records per xls (and not every item is in both xls' which is why I have
nulls!), the amount of time it would take to change nulls to zeros just
wouldn't be worth the effort.

There has to be a way to make an unbound control work like above. I
just don't know what it is. Anybody?

Oct 11 '06 #3
<sc*********@japan.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...
Probably something simple, but it's driving me up the wall...

In a report, I have an unbound control that is the total of 2 other
bound controls (currency) as follows:

unbound control =cash1 + cash2

However, it is possible that either cash1 or cash2 can be null, in
which case the unbound control displays nothing (apparently, addition
only works with a zero or above).
<snip>

How about using the null-to-zero function (Nz)?

Keith.
www.keithwilby.com
Oct 11 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Georges Heinesch | last post: by
reply views Thread by Bob Bykerk | last post: by
3 posts views Thread by Trevor Hughes | last post: by
4 posts views Thread by Vazz | last post: by
3 posts views Thread by Carlos Lozano | last post: by
reply views Thread by Barbara Schmidt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.