Jump to content

Number format for summary field


enquirerfm

This topic is 2216 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I have created a summary field which shows a number - say $434,567. I want the field to return $435 eg thousands of dollars. Not really clear how to do this... many thanks for any help.

Link to comment
Share on other sites

Hi enquirerfm,

This will work for the data you presented.

Left ( number ; Position (  amount ; "," ; 1 ; 1 )  - 1 )

or

Right( Left( Round( amount ; 0 ); Length( Round( amount ; 0 )) - 3 ); 3)

Try this Calculation, it was provide by comment.

It's very versatile, all you have to do is change "n = 1" (n = 2 or n = 3 etc. ) to extract a different part.

Let ( [
YourField = amount ;
n = 1 ;
t = "," & YourField & "," ;
start = Position ( t ; "," ; 1 ; n ) + 1 ;
end = Position ( t ; "," ; 1 ; n + 1 ) 
] ;
Middle ( t ; start ; end - start )
)

HTH

Lee

Link to comment
Share on other sites

With the reasonable assumption that the Summary field is a number field  (with the comma inserted by the Inspector for display), you could use

Int(YourSummaryField/1000) 

Lee's answers are all based on the field being a text field with the comma being actually part of the data, which I would not expect in a Summary field.

Link to comment
Share on other sites

This topic is 2216 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.