Ignore fields without values

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

Nick Mirro

I have several text controls that are added or multiplied. There is a grand
total field which will not calculate unless all of the added fields in the
expression have values.

How can I get the grand total field to ignore fields in its expression
without values?


Total

A A2 A3 A4 =(A2 * A3)
B B2 B3 B4 =(B2 * B3)
....
G = (A4 + B4...)

If all Totals don't have a value, then G won't be calculated. How can I get
G to ignore the fields without values?

Nick
 
Just stuck Nz in front of each total field expression.

....I need to take a course : (
 
That worked even better. Thanks.

Any idea how to get a combo with 3 columns to store a non-bound column as
its control source? I need this column for a report.

If I change the combo's bound column, a vb afterupdate procedure loses
access to the needed bound column.



Mike Suchoff said:
Try val(nz([field1])) + val(nz([field2]))

Nick Mirro said:
I have several text controls that are added or multiplied. There is a grand
total field which will not calculate unless all of the added fields in the
expression have values.

How can I get the grand total field to ignore fields in its expression
without values?


Total

A A2 A3 A4 =(A2 * A3)
B B2 B3 B4 =(B2 * B3)
...
G = (A4 + B4...)

If all Totals don't have a value, then G won't be calculated. How can I get
G to ignore the fields without values?

Nick
Click to expand...
Click to expand...
 
You can get the value of any column in a combo box's row source using this
expression:

ValueWanted = Me.ComboBoxName.Column(x)

where x is the column number *minus 1*.

So, if you want the value of the first column, x is 0. If the second column,
x is 1. Etc.

--
Ken Snell
<MS ACCESS MVP>

Nick Mirro said:
That worked even better. Thanks.

Any idea how to get a combo with 3 columns to store a non-bound column as
its control source? I need this column for a report.

If I change the combo's bound column, a vb afterupdate procedure loses
access to the needed bound column.



Mike Suchoff said:
Try val(nz([field1])) + val(nz([field2]))

Nick Mirro said:
I have several text controls that are added or multiplied. There is a grand
total field which will not calculate unless all of the added fields in the
expression have values.

How can I get the grand total field to ignore fields in its expression
without values?


Total

A A2 A3 A4 =(A2 * A3)
B B2 B3 B4 =(B2 * B3)
...
G = (A4 + B4...)

If all Totals don't have a value, then G won't be calculated. How can
Click to expand...
I
get
G to ignore the fields without values?

Nick
Click to expand...
Click to expand...
Click to expand...
 
Thanks for this help. This is the sub that accesses the combo bound field:

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " & Me![cboCPT1] &
" And [LocaleID] = " & Me.txtLocaleID)


I can change it to something like this: (?)

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " &
Me![ValueWanted = Me.cboCPT1.Column(x)] & " And [LocaleID] = " &
Me.txtLocaleID)

Nick



Ken Snell said:
You can get the value of any column in a combo box's row source using this
expression:

ValueWanted = Me.ComboBoxName.Column(x)

where x is the column number *minus 1*.

So, if you want the value of the first column, x is 0. If the second column,
x is 1. Etc.

--
Ken Snell
<MS ACCESS MVP>

Nick Mirro said:
That worked even better. Thanks.

Any idea how to get a combo with 3 columns to store a non-bound column as
its control source? I need this column for a report.

If I change the combo's bound column, a vb afterupdate procedure loses
access to the needed bound column.



Mike Suchoff said:
Try val(nz([field1])) + val(nz([field2]))

I have several text controls that are added or multiplied. There is a
grand
total field which will not calculate unless all of the added fields
Click to expand...
in
the
expression have values.

How can I get the grand total field to ignore fields in its expression
without values?


Total

A A2 A3 A4 =(A2 * A3)
B B2 B3 B4 =(B2 * B3)
...
G = (A4 + B4...)

If all Totals don't have a value, then G won't be calculated. How
Click to expand...
Click to expand...
can
I
Click to expand...
Click to expand...
 
Almost.

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " &
Me.cboCPT1.Column(x) & " And [LocaleID] = " &
Me.txtLocaleID)

Be sure you use a number and not x!

--
Ken Snell
<MS ACCESS MVP>


Nick Mirro said:
Thanks for this help. This is the sub that accesses the combo bound field:

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " & Me![cboCPT1] &
" And [LocaleID] = " & Me.txtLocaleID)


I can change it to something like this: (?)

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " &
Me![ValueWanted = Me.cboCPT1.Column(x)] & " And [LocaleID] = " &
Me.txtLocaleID)

Nick



Ken Snell said:
You can get the value of any column in a combo box's row source using this
expression:

ValueWanted = Me.ComboBoxName.Column(x)

where x is the column number *minus 1*.

So, if you want the value of the first column, x is 0. If the second column,
x is 1. Etc.

--
Ken Snell
<MS ACCESS MVP>

Nick Mirro said:
That worked even better. Thanks.

Any idea how to get a combo with 3 columns to store a non-bound column as
its control source? I need this column for a report.

If I change the combo's bound column, a vb afterupdate procedure loses
access to the needed bound column.



Try val(nz([field1])) + val(nz([field2]))

I have several text controls that are added or multiplied. There
Click to expand...
Click to expand...
is
a
Click to expand...
fields
in can
Click to expand...
Click to expand...
 
Use the Nz function to replace a Null value with some default value (such as
0):

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " &
Nz(Me.cboCPT1.Column(x), 0) & " And [LocaleID] = " &
Me.txtLocaleID)

You can use any number that you want if 0 is a meaningful number for CPTID
in the table. I would choose a number for which there is and never will be a
record.

--
Ken Snell
<MS ACCESS MVP>

Nick Mirro said:
Thanks Ken. That's helpful.

I'd like to squeeeze in one last question if you wouldn't mind : ) If I
delete the value in the combo, I get a run-time error "missing operator in
query expression." Can I add anything to this that would allow for null in
the combo fields?

Nick


Ken Snell said:
Almost.

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " &
Me.cboCPT1.Column(x) & " And [LocaleID] = " &
Me.txtLocaleID)

Be sure you use a number and not x!

--
Ken Snell
<MS ACCESS MVP>


Nick Mirro said:
Thanks for this help. This is the sub that accesses the combo bound field:

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " &
Click to expand...
Me![cboCPT1]
&
" And [LocaleID] = " & Me.txtLocaleID)


I can change it to something like this: (?)

Me.Fee1 = DLookup("[Fee]", "tblCostByLocale", " [CPTID] = " &
Me![ValueWanted = Me.cboCPT1.Column(x)] & " And [LocaleID] = " &
Me.txtLocaleID)

Nick



You can get the value of any column in a combo box's row source
Click to expand...
using
this
expression:

ValueWanted = Me.ComboBoxName.Column(x)

where x is the column number *minus 1*.

So, if you want the value of the first column, x is 0. If the second
column,
x is 1. Etc.

--
Ken Snell
<MS ACCESS MVP>

That worked even better. Thanks.

Any idea how to get a combo with 3 columns to store a non-bound column
as
its control source? I need this column for a report.

If I change the combo's bound column, a vb afterupdate procedure loses
access to the needed bound column.



Try val(nz([field1])) + val(nz([field2]))

I have several text controls that are added or multiplied.
Click to expand...
There
is
a
grand
total field which will not calculate unless all of the added fields
in
the
expression have values.

How can I get the grand total field to ignore fields in its
expression
without values?


Total

A A2 A3 A4 =(A2 * A3)
B B2 B3 B4 =(B2 * B3)
...
G = (A4 + B4...)

If all Totals don't have a value, then G won't be calculated. How
can
I
get
G to ignore the fields without values?

Nick
Click to expand...
Click to expand...
Click to expand...
 
Back
Top