Access Queries

I am working on a very detailed financial database and I am trying to balance
my numbers as I go. One of the queries that I need to write involves a rather
complex calculation. Based on different account numbers, I need to take one
number and subtract the other one from it. For example, I have some accounts
that start with a 175, some that start with a 155 and some with 70. How can I
write my query so that if the account number start with a "175" it should
subtract the corresponding "155" from it and if it doesn't it should just
give me the balance that's there. In Excel I can do this easily with a If and
Mid function, but I'm not sure how that would work in Access. Any help would
be much appreciated!!!! Thank you!

Someone may have a more elegant solution, but if you made a column with IIF
so that your criterion is Account number starts with a 175, your "true part"
is 155 and your "false part" is 0 and then had a second column that
subtracted the IIF column from the account balance, that should do it.
Syntax for If-Then as IIF statement is discussed recently here.

"Ruya" wrote:

I am working on a very detailed financial database and I am trying to balance
my numbers as I go. One of the queries that I need to write involves a rather
complex calculation. Based on different account numbers, I need to take one
number and subtract the other one from it. For example, I have some accounts
that start with a 175, some that start with a 155 and some with 70. How can I
write my query so that if the account number start with a "175" it should
subtract the corresponding "155" from it and if it doesn't it should just
give me the balance that's there. In Excel I can do this easily with a If and
Mid function, but I'm not sure how that would work in Access. Any help would
be much appreciated!!!! Thank you!