MS Query If..Then

[JGHarvey]

Is it possible to use an if statement in MS Query. My data base has one
column that stores the dollar amounts and another column that tells me if it
is a debit or credit amount. I have tried this:
=if([DORC]='D',[AMT],[AMT]*-1) Doesn't work. Is there another way?

 

[Duane Hookom]

If this is a query in Access then try:
=IIf([DORC]='D',[AMT],[AMT]*-1)

 

[Klatuu]

The correct syntax is IIf
It is the Immediate If statement, which is different from the If statement.

=IIf([DORC]='D',[AMT],[AMT]*-1)

 

[JGHarvey]

This is not done in Access. I am using an ODBC connection in Excel. I have
tried the Access syntax using =IIf() This does not work either. I can use a
formula in Excel to do this for me but my end users are not all that savy
when it comes to replication formulas. I want them to be able to just click

If this is a query in Access then try:
=IIf([DORC]='D',[AMT],[AMT]*-1)


--
Duane Hookom
MS Access MVP

Is it possible to use an if statement in MS Query. My data base has one
column that stores the dollar amounts and another column that tells me if
it
is a debit or credit amount. I have tried this:
=if([DORC]='D',[AMT],[AMT]*-1) Doesn't work. Is there another way?

 

[Duane Hookom]

I'm not sure if there is an expression that will "resolve" in Excel. I
expect you might be able to create a union query like:

SELECT *, AMT as NewAmt
FROM tblYourTable
WHERE DORC = "D"
UNION ALL
SELECT *, -AMT as NewAmt
FROM tblYourTable
WHERE DORC <> "D";


--
Duane Hookom
MS Access MVP

This is not done in Access. I am using an ODBC connection in Excel. I
have
tried the Access syntax using =IIf() This does not work either. I can
use a
formula in Excel to do this for me but my end users are not all that savy
when it comes to replication formulas. I want them to be able to just
click
on the refresh button and be done with it. Can the if statement be used
in
MSQuery?

If this is a query in Access then try:
=IIf([DORC]='D',[AMT],[AMT]*-1)


--
Duane Hookom
MS Access MVP

Is it possible to use an if statement in MS Query. My data base has
one
column that stores the dollar amounts and another column that tells me
if
it
is a debit or credit amount. I have tried this:
=if([DORC]='D',[AMT],[AMT]*-1) Doesn't work. Is there another way?

 

[JGHarvey]

This solution worked perfectly. Thank you very much!!

I'm not sure if there is an expression that will "resolve" in Excel. I
expect you might be able to create a union query like:

SELECT *, AMT as NewAmt
FROM tblYourTable
WHERE DORC = "D"
UNION ALL
SELECT *, -AMT as NewAmt
FROM tblYourTable
WHERE DORC <> "D";


--
Duane Hookom
MS Access MVP

This is not done in Access. I am using an ODBC connection in Excel. I
have
tried the Access syntax using =IIf() This does not work either. I can
use a
formula in Excel to do this for me but my end users are not all that savy
when it comes to replication formulas. I want them to be able to just
click
on the refresh button and be done with it. Can the if statement be used
in
MSQuery?

If this is a query in Access then try:
=IIf([DORC]='D',[AMT],[AMT]*-1)


--
Duane Hookom
MS Access MVP

Is it possible to use an if statement in MS Query. My data base has
one
column that stores the dollar amounts and another column that tells me
if
it
is a debit or credit amount. I have tried this:
=if([DORC]='D',[AMT],[AMT]*-1) Doesn't work. Is there another way?