Converting a postive number to a negative

[access09]

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.

 

[Atomic Shrimp]

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)

 

[access09]

Thats great. It works. Is there a way I can have all the debit amounts in one field, including positive and negative numbers??

 

[gemma-the-husky]

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

 

[access09]

I see where your coming from but for this no. Its just 0 and turns the number negative.

 

[Atomic Shrimp]

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?

 

[access09]

No as in give me a full field back as it was with the changes made.

 

[Atomic Shrimp]

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

?

 

[access09]

Yes. Thats exactly it.

 

[Atomic Shrimp]

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;

 

[access09]

Thanks. Thats great