Converting a postive number to a negative

access09

Registered User.
Local time
Today, 06:44
Joined
Apr 14, 2009
Messages
40
I have a field with amounts in it. For example:

Debit Amount:
00000006223
00000004356
0000000432r
0000000845v
00000006226
0000000198s
0000001638w
0000000911r
00000013667
0000000113q
00000001508

However, when the ‘Debit Amount’ has a letter as the last character,i need to replace the letter with a zero and make the whole number negative.

Anybody have any ideas?

I tried using the replace fuction but then would have to use it for every letter and then I dont know how to make it a negative.
 
Do you want to update the field with this value, or just pull it out in a query?

Here's a query that will return converted values for those inputs (displaying only the selected records). Replacing the letter with a zero effectively multiplies the number by ten, so the query does that directly - I assume that's what you meant...

SELECT IsNumeric(Right([Debit_Amount],1)) AS Expr1, Your_Table.Debit_Amount, Val([debit_amount])*-10 AS Expr2
FROM Your_Table
WHERE (((IsNumeric(Right([Debit_Amount],1)))=False));

(obviously you'll have to amend the field/table names as appropriate to your own data)
 
Last edited:
Thats great. It works. Is there a way I can have all the debit amounts in one field, including positive and negative numbers??
 
are you sure its a zero

I have seen this before, and my understanding of these formats is that the last letter indicates a number in the range 0 to 9

eg

p = -0
q = -1
r = -2
s = -3

etc
 
I see where your coming from but for this no. Its just 0 and turns the number negative.
 
Thats great. It works. Is there a way I can have all the debit amounts in one field, including positive and negative numbers??

Do you mean summarise them into one total, or concatenate them into a single line list?
 
No as in give me a full field back as it was with the changes made.
 
Sorry, I still don't quite understand. Do you mean you want to turn this:
Debit Amount:
00000006223
00000004356
0000000432r
0000000845v
00000006226
0000000198s
0000001638w
0000000911r
00000013667
0000000113q
00000001508

Into this:

Debit Amount:
00000006223
00000004356
-00000004320
-00000008450
00000006226
-00000001980
-00000016380
-0000000911r
00000013667
-00000001130
00000001508

?
 
OK, this will do it:

SELECT Your_Table.Debit_Amount, IIf(IsNumeric(Right([debit_amount],1)),[debit_amount],Val([debit_amount])*-10) AS expr1
FROM Your_Table;

If you need the negative numbers formatted with the leading zeroes, then it's:

SELECT Your_Table.Debit_Amount, IIf(IsNumeric(Right([debit_amount],1)),[debit_amount],Format(Val([debit_amount])*-10,"00000000000")) AS expr1
FROM Your_Table;
 

Users who are viewing this thread

Back
Top Bottom