Type mismatch error on report

I have a report which has two sub-reports.  In the detail section of the main report I have a calculated field which is...

=[subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood]+Abs([subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods])-[Order_Qty]

Obviously the field gets its calculation from the two sub-reports.

But if [subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood] has no value then I get the error: "Type mismatch" when I run the report.  I've tried...

=Nz([subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood],0)+Nz(Abs([subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods])-[Order_Qty],0)

but that didn't work.  I've also tried the Nz "thing in the subreport fields but that didn't work.  

Incidently, the "Abs" in "Abs([subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods])" is there because the real number is a negative value and I need to turn it into a positive value for the report.

Thoughts?
SteveL13Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mbizupConnect With a Mentor Commented:
Hmm...
Give this another try.

The syntax seems okay in the VBA Editor:

= Nz([subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood], 0) + Abs(Nz([subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods], 0)) - Nz([Order_Qty], 0)

Open in new window

0
 
Gustav BrockConnect With a Mentor CIOCommented:
You can try with:

=[subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood]-[subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods]-[Order_Qty]

/gustav
0
 
SteveL13Author Commented:
Still get type mismatch.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mbizupCommented:
= NZ([subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood],0) + Abs(NZ([subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods]),0) - NZ([Order_Qty],0))

Open in new window


The NZs should go inside your ABS function.
0
 
mbizupCommented:
Correction:

= NZ([subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood],0) + Abs(NZ([subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods],0)) - NZ([Order_Qty],0)

Open in new window

0
 
Gustav BrockCIOCommented:
Perhaps counting records:

=IIf(Count([subrptOrdHistReportFINISHEDGOOD].[Report])=0,Null,[subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood]-[subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods]-[Order_Qty])

/gustav
0
 
SteveL13Author Commented:
Getting wrong number of arguments with:

= NZ([subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood],0) + Abs(NZ([subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods],0)) - NZ([Order_Qty],0)
0
 
Dale FyeConnect With a Mentor Commented:
You might find this surprising, but if you are using the NZ( ) method in a query, the results will be a text string, so what I do, in the query is:

Val(NZ([somefield], 0))

If you run a query where on a table where you know you have some NULL values, you can experiment with the optional ValueIfNull argument, but you can provide dates, or numbers and the result will always be left justified (string) rather than the proper data type.

For that reason, I always explicitly convert the result of the NZ function back to the appropriate data type

Val(NZ([somefield],0))
cint(NZ([somefield], 0))
cdate(NZ([datefield], #1/1/12#))
0
 
SteveL13Author Commented:
I tried:

Me.txtExcess = Nz([subrptOrdHistReportFINISHEDGOOD].[Report]![txtTotGood], 0) + Abs(Nz([subrptOrdHistReportFINGOODS].[Report]![txtTotFinGoods], 0)) - Nz([Order_Qty], 0)

in the onprint event of the detail and get "you entered an expression that has no value"
0
 
Dale FyeCommented:
so, this textbox is on the Detail section of the report, and you are trying to refer to these values on your subreport?
0
 
SteveL13Author Commented:
So now I'm trying to narrow this down.  I setup another field in the detail section and in VBA code entered:

Me.txtFinGoodQty = CInt(Nz([subrptOrdHistReportFINISHEDGOOD].[Report]![txtQtyGood], 0))

and also tried

Me.txtFinGoodQty = Val(Nz([subrptOrdHistReportFINISHEDGOOD].[Report]![txtQtyGood], 0))

and removed the control source from the property sheet of the fields in the detail.

Now get "You entered an expression that has no value" error.  When I hover over the "me.txtFinGoodQty" I see "Me.txtFinGoodQty = Null"

??
0
 
SteveL13Author Commented:
Regarding:

so, this textbox is on the Detail section of the report, and you are trying to refer to these values on your subreport?

Yes.  That is correct.
0
 
Gustav BrockCIOCommented:
So you couldn't count records?

/gustav
0
 
SteveL13Author Commented:
Well, I guess I feel really stupid.  The problem was my own.  I was looking at a wrong field for data in the formula to begin with. The best I can do I think is split the points to all that tried to help.  I am truly very sorry for the confusion.
0
 
Gustav BrockCIOCommented:
OK, that explains.

/gustav
0
All Courses

From novice to tech pro — start learning today.