Convert String to Numeric (1 Viewer)

mike60smart

Registered User.
Local time
Today, 17:31
Joined
Aug 6, 2017
Messages
1,899
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

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
So what is meant to be there is any of those IIFs are false? :(
 

mike60smart

Registered User.
Local time
Today, 17:31
Joined
Aug 6, 2017
Messages
1,899
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

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
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?
 
Last edited:

mike60smart

Registered User.
Local time
Today, 17:31
Joined
Aug 6, 2017
Messages
1,899
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.
 

Attachments

  • Display.JPG
    Display.JPG
    50.4 KB · Views: 248

CJ_London

Super Moderator
Staff member
Local time
Today, 17:31
Joined
Feb 19, 2013
Messages
16,553
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

Enthusiastic Amateur
Local time
Today, 17:31
Joined
Sep 21, 2011
Messages
14,044
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

Registered User.
Local time
Today, 17:31
Joined
Aug 6, 2017
Messages
1,899
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

Super Moderator
Staff member
Local time
Today, 17:31
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 17:31
Joined
Aug 6, 2017
Messages
1,899
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

Super Moderator
Staff member
Local time
Today, 17:31
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 17:31
Joined
Aug 6, 2017
Messages
1,899
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

Super Moderator
Staff member
Local time
Today, 17:31
Joined
Feb 19, 2013
Messages
16,553
good luck with your project. If you need more help, upload your database
 

SHANEMAC51

Active member
Local time
Today, 20:31
Joined
Jan 28, 2022
Messages
310
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
 

Users who are viewing this thread

Top Bottom