Currency/text manipulation (remove decimal) 1

[demunchkin]

I'm attempting some text manipulation for export.

The input is currency with two decimal places. What I did was sum based on employee number. An additional step that I can't figure out is, I need to remove the decimal altogether, push it into a table, and add a prefix. The ending table value must be text (which isn't a problem). I don't have any problems with the prefix and the sum; it's the decimal I'm having issues with.

The field must be right justified with zeros filling the remaining spaces. Field length is 6 characters.

Example: employee 1234567890 $176.52 needs to read
1234567890 017652
employee 2468246810 $5.34 needs to read
2468246810 000534

I'm sure there is an easy way to multiply the fields by 100, but I'm having a hard time figuring it out. When I try an update or append query (after the sum is completed and before zero addition), a box pops up asking for a parameter value.

I've tried using various combinations of Mid(), Right(), and Left() functions also to pull it out, but I have not been successful. Any ideas would be greatly appreciated.

Thanks in advance for your help.

Devin

 

[Tranman]

Devin,
It's a little hard to understand what you're asking, but here's a shot:

mystr = Format(Str(Val(Str(curnbr)) * 100), "000000&quot

if the value $5.34 is in the field curnbr, the above let command will populate mystr with 000534, which can then be appended to the 12345.... string.


Tranman

 

[PaulBricker]

Assuming you are using Totals query and grouping by Employee you could try this in a new column of your query
MyValue:Format(Sum(YOurField)*100,"000000&quot
Then on the Totals line put Expression.

Paul

 

[demunchkin]

Paul,

That did exactly what I needed it to. Thanks!

Devin