By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,537 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,537 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a

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

P: n/a
<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.