Nz function question, will it work for my problem???

[Alberta Rose]

Good morning. I have a database that I've taken over and have a problem with
an existing reports' VBA code. In this code, I need to know if I can add
instructions so that if there is not a matching value, a zero is populated on
my report. Otherwise the report subtotals do not show up. This is part of
the code:

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]")
Me.txtCraftLabEst = ab

If there is no value in the table for this CraftLabEst, I want a zero
populated on my report. We are constantly having cost code/cost type changes
and additions, and at times I have to change the report to include new cost
code/cost types. The new records in the table will include this field, but
the old records do not, so when I run my report, the older ones that don't
have that cost code/cost type combos, do not show up but then my subtotals do
not appear. I've tested adding the missing lines in the table and then the
subtotals and total appear when I run the closeout report.

Thanks....Laurie

 

[Ken Snell [MVP]]

Yes, the Nz function will work for you in this situation.

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = Nz(DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]"),0)
Me.txtCraftLabEst = ab

 

[Paolo]

Hi Laurie,

in my timezone the morning is already past but good morning anyway
Try in this way
Me.txtCraftLabEst = nz(ab,0)

HTH Paolo

 

[JimBurke via AccessMonster.com]

Yes, use Nz for that. You also have a mistake in that DLookup - for the
criteria you want

"[ContractNumber]= " & [Forms]![frmViewCloseout]![cmboContractNumber]

The form control value shouldn't be inside the quotes. This format is
assuming that the contract # is a numeric type - if it's a text field you'd
need to surround the value with quotes:

"[ContractNumber]= '" & [Forms]![frmViewCloseout]![cmboContractNumber] & "'"

That's a single-quote after the = and a single quote inside double quotes at
the end

You don't really need the ab variable, either. You could just use

Me.txtCraftLabEst = Nz(DLookup(....),0)

Good morning. I have a database that I've taken over and have a problem with
an existing reports' VBA code. In this code, I need to know if I can add
instructions so that if there is not a matching value, a zero is populated on
my report. Otherwise the report subtotals do not show up. This is part of
the code:

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]")
Me.txtCraftLabEst = ab

If there is no value in the table for this CraftLabEst, I want a zero
populated on my report. We are constantly having cost code/cost type changes
and additions, and at times I have to change the report to include new cost
code/cost types. The new records in the table will include this field, but
the old records do not, so when I run my report, the older ones that don't
have that cost code/cost type combos, do not show up but then my subtotals do
not appear. I've tested adding the missing lines in the table and then the
subtotals and total appear when I run the closeout report.

Thanks....Laurie

 

[Alberta Rose]

Thanks for that. The reason a variable is assigned is because it is included
in the following:

' field txtCraftSuprvTotalAct (Craft Supervision TOTAL Actual) is the sum
of (abd) Craft Supervision Labour Actual + (abf) Clerical Labour Actual +
' (abh) QAQC Supervision Labour Actual and (abj) Safety Supervison Labour
Actual

txtCraftSuprvTotalAct = abd + abf + abh + abj

I think that is why my predecessor used variables, as there are multiple
examples of this through the VBA coding. Is this the proper way to handle
these?

Yes, use Nz for that. You also have a mistake in that DLookup - for the
criteria you want

"[ContractNumber]= " & [Forms]![frmViewCloseout]![cmboContractNumber]

The form control value shouldn't be inside the quotes. This format is
assuming that the contract # is a numeric type - if it's a text field you'd
need to surround the value with quotes:

"[ContractNumber]= '" & [Forms]![frmViewCloseout]![cmboContractNumber] & "'"

That's a single-quote after the = and a single quote inside double quotes at
the end

You don't really need the ab variable, either. You could just use

Me.txtCraftLabEst = Nz(DLookup(....),0)

Good morning. I have a database that I've taken over and have a problem with
an existing reports' VBA code. In this code, I need to know if I can add
instructions so that if there is not a matching value, a zero is populated on
my report. Otherwise the report subtotals do not show up. This is part of
the code:

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]")
Me.txtCraftLabEst = ab

If there is no value in the table for this CraftLabEst, I want a zero
populated on my report. We are constantly having cost code/cost type changes
and additions, and at times I have to change the report to include new cost
code/cost types. The new records in the table will include this field, but
the old records do not, so when I run my report, the older ones that don't
have that cost code/cost type combos, do not show up but then my subtotals do
not appear. I've tested adding the missing lines in the table and then the
subtotals and total appear when I run the closeout report.

Thanks....Laurie

 

[Alberta Rose]

This worked wonderful! Now a 0 shows up on that line in my report. The
subtotal still is not there tho and I think I may have to add additional
information. The summary coding is:

' field txtCraftSuprvTotalAct (Craft Supervision TOTAL Actual) is the sum
of (abd) Craft Supervision Labour Actual + (abf) Clerical Labour Actual +
' (abh) QAQC Supervision Labour Actual and (abj) Safety Supervison Labour
Actual

txtCraftSuprvTotalAct = abd + abf + abh + abj

Any ideas?

Laurie

Yes, the Nz function will work for you in this situation.

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = Nz(DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]"),0)
Me.txtCraftLabEst = ab

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Good morning. I have a database that I've taken over and have a problem
with
an existing reports' VBA code. In this code, I need to know if I can add
instructions so that if there is not a matching value, a zero is populated
on
my report. Otherwise the report subtotals do not show up. This is part
of
the code:

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]")
Me.txtCraftLabEst = ab

If there is no value in the table for this CraftLabEst, I want a zero
populated on my report. We are constantly having cost code/cost type
changes
and additions, and at times I have to change the report to include new
cost
code/cost types. The new records in the table will include this field,
but
the old records do not, so when I run my report, the older ones that don't
have that cost code/cost type combos, do not show up but then my subtotals
do
not appear. I've tested adding the missing lines in the table and then
the
subtotals and total appear when I run the closeout report.

Thanks....Laurie

 

[Alberta Rose]

Hi Paolo This also worked great!! As per my reply to Ken, my subtotal is
still not showing up. This is the existing coding for that including the
commented areas as to what the variables stand for:

' field txtCraftSuprvTotalAct (Craft Supervision TOTAL Actual) is the sum
of (abd) Craft Supervision Labour Actual + (abf) Clerical Labour Actual +
' (abh) QAQC Supervision Labour Actual and (abj) Safety Supervison Labour
Actual

txtCraftSuprvTotalAct = abd + abf + abh + abj

Ideas?

Laurie

Hi Laurie,

in my timezone the morning is already past but good morning anyway
Try in this way
Me.txtCraftLabEst = nz(ab,0)

HTH Paolo

Good morning. I have a database that I've taken over and have a problem with
an existing reports' VBA code. In this code, I need to know if I can add
instructions so that if there is not a matching value, a zero is populated on
my report. Otherwise the report subtotals do not show up. This is part of
the code:

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]")
Me.txtCraftLabEst = ab

If there is no value in the table for this CraftLabEst, I want a zero
populated on my report. We are constantly having cost code/cost type changes
and additions, and at times I have to change the report to include new cost
code/cost types. The new records in the table will include this field, but
the old records do not, so when I run my report, the older ones that don't
have that cost code/cost type combos, do not show up but then my subtotals do
not appear. I've tested adding the missing lines in the table and then the
subtotals and total appear when I run the closeout report.

Thanks....Laurie

 

[JimBurke via AccessMonster.com]

Where do you have the code that uses the Nz function? And where do you have
this:

txtCraftSuprvTotalAct = abd + abf + abh + abj

Can't tell what you need to do without knowing where those pieces of code are.
They must be in events within the report, but don't know anything about how
the report is set up.

Thanks for that. The reason a variable is assigned is because it is included
in the following:

' field txtCraftSuprvTotalAct (Craft Supervision TOTAL Actual) is the sum
of (abd) Craft Supervision Labour Actual + (abf) Clerical Labour Actual +
' (abh) QAQC Supervision Labour Actual and (abj) Safety Supervison Labour
Actual

txtCraftSuprvTotalAct = abd + abf + abh + abj

I think that is why my predecessor used variables, as there are multiple
examples of this through the VBA coding. Is this the proper way to handle
these?

Yes, use Nz for that. You also have a mistake in that DLookup - for the
criteria you want

[quoted text clipped - 36 lines]

 

[Ken Snell [MVP]]

Make sure that you use Nz on the calculation of the other variables (abd,
abf, abh, abj), if they too might be Null result. You should then be able to
add the results and see your correct answer.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

This worked wonderful! Now a 0 shows up on that line in my report. The
subtotal still is not there tho and I think I may have to add additional
information. The summary coding is:

' field txtCraftSuprvTotalAct (Craft Supervision TOTAL Actual) is the sum
of (abd) Craft Supervision Labour Actual + (abf) Clerical Labour Actual +
' (abh) QAQC Supervision Labour Actual and (abj) Safety Supervison Labour
Actual

txtCraftSuprvTotalAct = abd + abf + abh + abj

Any ideas?

Laurie

Yes, the Nz function will work for you in this situation.

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = Nz(DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]"),0)
Me.txtCraftLabEst = ab

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Good morning. I have a database that I've taken over and have a
problem
with
an existing reports' VBA code. In this code, I need to know if I can
add
instructions so that if there is not a matching value, a zero is
populated
on
my report. Otherwise the report subtotals do not show up. This is
part
of
the code:

'Assign the variable "ab" to txtCraftLabEst"
Dim ab As Variant
ab = DLookup("[EstimatedHours]", "qryCraftLabHrs", "[ContractNumber]=
[Forms]![frmViewCloseout]![cmboContractNumber]")
Me.txtCraftLabEst = ab

If there is no value in the table for this CraftLabEst, I want a zero
populated on my report. We are constantly having cost code/cost type
changes
and additions, and at times I have to change the report to include new
cost
code/cost types. The new records in the table will include this field,
but
the old records do not, so when I run my report, the older ones that
don't
have that cost code/cost type combos, do not show up but then my
subtotals
do
not appear. I've tested adding the missing lines in the table and then
the
subtotals and total appear when I run the closeout report.

Thanks....Laurie