Convert String to Numeric

[mike60smart]

Hi Everyone

I have tried using the following in a query to display numerical Data on a Continuous Subform.

The values display correctly but when I try to get a Sum of the Royalty field I get the following error:- #Error

Royalty: CDbl(IIf([Source]="Kdp",[Net Pub Comp]*[RoyaltyPercentage],IIf([Source]="L",([Net Pub Comp]*[Rate]),IIf([Source]="Ingra",([Net Pub Comp]*[Rate]),0))))

I have also tried using Val in place of CDbl

Any help appreciated,

 

[Gasman]

So what is meant to be there is any of those IIFs are false?

 

[mike60smart]

So what is meant to be there is any of those IIFs are false?

Hi Gasman

I thought the 0 at the end covered this?

 

[Gasman]

TBH multiple iifs() like that hurt my head?
What is the data you are passing? as L and ingra appear to be the same?
Why all the brackets?

Just reread the post. I do not believe you can refer to an alias in the same query?
Need to use the same calc?

Cannot see the reason to convert to numeric?, values should be numeric surely?

 

[mike60smart]

TBH multiple iifs() like that hurt my head?
What is the data you are passing? as L and ingra appear to be the same?
Why all the brackets?

Just reread the post. I do not believe you can refer to an alias in the same query?
Need to use the same calc?

Cannot see the reason to convert to numeric?, values should be numeric surely?

Hi Gasman

The fields involved in the calculation have all now been converted to numeric.
The data displays as shown.

The reason I split it into 3 as I thought this would be the easiest method.

I could change this to just 2 IIf's as L & Ingra have the same calculation.

 

[CJ_London]

try simplifying your calculation

Royalty: [Net Pub Comp]*IIf([Source]="Kdp",[RoyaltyPercentage],IIf([Source] in ("L","Ingra"),[Rate],0))

However think the issue is the iif since each part is evaluated which may be causing your error , perhaps try switch instead

Royalty: [Net Pub Comp]*switch([Source]="Kdp",[RoyaltyPercentage],[Source] in ("L","Ingra"),[Rate],true,0)

 

[Gasman]

Hi Gasman

The fields involved in the calculation have all now been converted to numeric.
The data displays as shown.

The reason I split it into 3 as I thought this would be the easiest method.

I could change this to just 2 IIf's as L & Ingra have the same calculation.

The fields should be numeric anyway surely, as you are carrying out math on them?

How are you carrying out the sum()?
Show the SQL used.

 

[mike60smart]

try simplifying your calculation

Royalty: [Net Pub Comp]*IIf([Source]="Kdp",[RoyaltyPercentage],IIf([Source] in ("L","Ingra"),[Rate],0))

However think the issue is the iif since each part is evaluated which may be causing your error , perhaps try switch instead

Royalty: [Net Pub Comp]*switch([Source]="Kdp",[RoyaltyPercentage],[Source] in ("L","Ingra"),[Rate],true,0)

Hi Chris
The switch gives me the correct values, but I still can't sum the Control "Royalty" in the Subform Footer?

 

[CJ_London]

I seem to recall there is an issue with summing calculated values. Instead of summing the royalty column i.e. =sum([royalty]) try

=sum( [Net Pub Comp]*switch([Source]="Kdp",[RoyaltyPercentage],[Source] in ("L","Ingra"),[Rate],true,0))

 

[mike60smart]

I seem to recall there is an issue with summing calculated values. Instead of summing the royalty column i.e. =sum([royalty]) try

=sum( [Net Pub Comp]*switch([Source]="Kdp",[RoyaltyPercentage],[Source] in ("L","Ingra"),[Rate],true,0))

Hi Chris

That didn't work either.

I had to change the query fields as I realised IngramRoyalty needed a differnt calculation, so that the calculated fields are as follows:-

KDPRoyalty: [Net Pub Comp]*Switch([Source]="Kdp",[RoyaltyPercentage],True,0)

IngramRoyalty: IIf([Source] In ("L","Ingra"),[Net Pub Comp]/[Rate],0)

Royalty: Nz([KDPRoyalty],0)+Nz([IngramRoyalty],0)

Still won't sum in the footer.

 

[CJ_London]

pretty sure Royalty: Nz([KDPRoyalty],0)+Nz([IngramRoyalty],0) won't work, you would need

Royalty: [Net Pub Comp]*Switch([Source]="Kdp",[RoyaltyPercentage],True,0)+IIf([Source] In ("L","Ingra"),[Net Pub Comp]/[Rate],0)

note nz should not be required since value is either something or 0, never null (based on what you have shown us)




to use one of your phrases - suggest upload your db

 

[mike60smart]

pretty sure Royalty: Nz([KDPRoyalty],0)+Nz([IngramRoyalty],0) won't work, you would need

Royalty: [Net Pub Comp]*Switch([Source]="Kdp",[RoyaltyPercentage],True,0)+IIf([Source] In ("L","Ingra"),[Net Pub Comp]/[Rate],0)

note nz should not be required since value is either something or 0, never null (based on what you have shown us)




to use one of your phrases - suggest upload your db

Hi Chris

Both this:-

Royalty: Nz([KDPRoyalty],0)+Nz([IngramRoyalty],0)

And this work just fine:-

Royalty: [Net Pub Comp]*Switch([Source]="Kdp",[RoyaltyPercentage],True,0)+IIf([Source] In ("L","Ingra"),[Net Pub Comp]/[Rate],0)

When I generate a Report from the same query The Totals work just fine.

Just not able to get the Totals on the Form.

All I can think is that the form is corrupt in some way.

Thanks for looking anyway.

 

[CJ_London]

good luck with your project. If you need more help, upload your database

 

[SHANEMAC51]

I have tried using the following in a query to display numerical Data on a Continuous Subform.

it is unlikely that the footer sees the fields of the subordinate form

the final field should be in the note of the subordinate form