DaWolf has asked for the wisdom of the Perl Monks concerning the following question:

Greetings.

I'm working with an app that interacts with (ugh!) MS Access and I'm doing a lot of queries in 'Currency' type fields.

When I ask for one of this values, this is the "normal" MS Access behaviour:

I ask for the value in the "account" field.

MS Access returns: 360000
What I want is: 360.000,00


So, I've wrote a sub to to this formatting for me. Here it is: 
sub FormatValue
{
    $c = 0;
    $vl = $_[0];
    
    if ($vl ne "0,00" and $vl ne "0" and $vl ne ",00" and 
$vl ne ".0000")
    {
        if ($vl =~ m/(\.0000)/)
        {
            $vl =~ s/(\.0000)$/,00/;
        }
        else
        {
            $vl .= ",00";
        }
        ($interessa,$zeros) = split (",",$vl);
        $compr = length $interessa;
        
        $c = int ($compr / 3);
        
        if ($c eq 1 and $compr > 3)
        {
            $um = substr($interessa, -3, 3);
            $interessa =~ s/$um/\.$um/;
        }
        elsif ($c eq 2)
        {
            $um = substr($interessa, -6, 3);
            $dois = substr($interessa, -3, 3);
            $interessa =~ s/$um/$um\./;
        }
        $result = $interessa.",".$zeros;
    }
    else
    {
        $result = "0,00";
    }
    
    return $result;
}

[download]
So, it works pretty fine:

Value of AccessReturned by sub
36000.000036.000,00
12725.000012.725,00


But when I get a value like 80000 it returns 8.0000,00 !

Anyone can tell me what's the catch here?

Thanks in advance,

Er Galvão Abbott
 a.k.a. Lobo, DaWolf
Webdeveloper

Replies are listed 'Best First'.
Re: Formatting MS Access Currency Values
by Molt (Chaplain) on Apr 23, 2002 at 15:00 UTC

    Try this.. it seems to work for me. If (as you said in CB) it'll be an integer this should work.

    #!/usr/bin/perl -w

use strict;

foreach (360000, 12725, 80000) {
  print "$_ = ".msaccess($_)."\n";
}

sub msaccess {
  my ($num) = @_;
  $num += 0;
  while ($num =~ s/(\d)(\d{3})\b/$1.$2/) {};
  return "$num,00";
}

    [download]

    Update: The $num += 0; line was added to remove the .0000 off the end. Also tidied up the regexp, thanks to graff's post below for suggesting the \b bit. Never let it be said that I don't maintain my code! :)

    Managed to also eventually find Number::Format which seems to handle this kind of thing rather nicely.

[reply]
[d/l]
Re: Formatting MS Access Currency Values
by graff (Chancellor) on Apr 23, 2002 at 15:31 UTC
    Just in case Access returns fractional values that you would like to reproduce with 2 places of accuracy -- or, heaven forbid, negative values: 
    sub format_float
{
    $val = shift;
    $intgr = int( $val );
    $fract = sprintf( "%2.2d", 100 * abs( $val  - $intgr ));
    if ( length( $intgr ) > 3 ) {
    while ( $intgr =~ /\d{4,}/ ) { $intgr =~ s/(\d{3})\b/.$1/ }
    }
    join( ",", $intgr, $fract );
}

foreach ( 10.5, 4000.8, "100000.000", 80000, "80000", 
      36000.0000, "36000.0000", 12725.0000, "12725.0000" )
{
    print "$_ => " . format_float( $_ ) . "\n";
}

    [download]
[reply]
[d/l]
Re: Formatting MS Access Currency Values
by graff (Chancellor) on Apr 23, 2002 at 15:50 UTC
    Sorry -- you may have wanted to know where your code went wrong. It's here: 
    $interessa =~ s/$um/\.$um/;

    [download]
    The "$um" just contains "000", and when you apply this to a value like "80000", it matches the first three zeroes. You needed something like this: 
    $interessa =~ s/$um\b/\.$um/;

    [download]
    But you also needed to cut down on the amount work you're doing in general.
[reply]
[d/l]
[select]
Re: Formatting MS Access Currency Values
by gryphon (Abbot) on Apr 23, 2002 at 15:39 UTC

    Greetings DaWolf,

    I pulled a lot of this from the Perl Cookbook. Essentually, it's just adding the commas as normal then swapping the comma a period. Depending on your needs for currency, you may want to keep the "sprintf" rounding to round off numbers to two decimal places (like the last element of @data).

    #!/usr/bin/perl -w
use strict;

my @data = (
  '36000.0000',
  '12725.0000',
  '80000',
  '8123.2412'
);

sub fix_number {
  my $number = shift;
  $number = reverse sprintf("%.2f", $number);
  $number =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
  $number =~ tr/.,/,./;
  return scalar reverse $number;
}

foreach (@data) {
  print fix_number($_), "\n";
}

exit;

    [download]

    -gryphon
    code('Perl') || die;

[reply]
[d/l]

Back to Seekers of Perl Wisdom