Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

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?
0
SteveL13
Asked:
SteveL13
  • 6
  • 4
  • 3
  • +1
3 Solutions
 
Gustav BrockCIOCommented:
You can try with:

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

/gustav
0
 
SteveL13Author Commented:
Still get type mismatch.
0
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 FyeCommented:
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
 
mbizupCommented:
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now