IIF Statement not working

[Jrmrosebud]

This is my Statement:

IIf([TransactionT]![StockLocation]=“D3” And [TransactionT]![TransactionType]=“Adjustment" OR “Usage",[StockLocQtyQ]![D3QtyCalc]-[TransactionT]![Quantity],[StockLocQtyQ]![D3QtyCalc])

When I try to click into another field in the query I get the following error:

The expression you entered contains invalid syntax.

You may have entered an operand without an operator.

What am I doing wrong?

Thank you. :banghead:

 

[plog]

You've missed a comparison in your criteria:

OR “Usage"

You need to compare something to "Usage" you just have that string hanging by itself.

 

[Jrmrosebud]

IIf([TransactionT]![StockLocation]=“D3” And ([TransactionT]![TransactionType]=“Adjustment" OR [TransactionT]![TransactionType] = “Usage"),[StockLocQtyQ]![D3QtyCalc]-[TransactionT]![Quantity],[StockLocQtyQ]![D3QtyCalc])

I changed it to this and I still get the same error message.

 

[plog]

When you have a complex problem, make it simpler until it works. Try this:

IIf(1=1,[StockLocQtyQ]![D3QtyCalc]-[TransactionT]![Quantity],[StockLocQtyQ]![D3QtyCalc])

If that works, start adding back your criteria, piece by piece. If not, the problem is your calculation--strip that out and make it simpler until you find the offending part.

 

[Jrmrosebud]

Thank you, I will give that a try and see what I can come up with.

 

[Rx_]

A quick question, the statement looks like a SQL Statement for the Access Query def.
Just in case someone looks at this code later and tries it in the T-SQL editor.

A query on Access using SQL Linked tables will run this kind of statement.
If the number of records are very large, the IIF won't run efficiently across ODBC.
If your recordset is only in the thousands it isn't as big of a deal as if it was millions.

Otherwise, looks like you are on the right track for a solution.

 

[WayneRyan]

Jim,

I'm thinking that your real problem is that you're trying to do a joined-table update.
Did the answer to your other thread help here?

If not, it would help if you'd post the table defs and a definition of the problem.

Wayne

 

[Galaxiom]

Another point to consider about a better database design practice.

Any queries would be simpler and run faster if TransactionType was encoded as a number rather than text.

eg
TransactionType BETWEEN 2 AND 3
or
TransactionType IN(2,4)

You can still display the text on a form by using a multi-column combobox.

This is also much more flexible when the client asks to change the displayed text. You would only need to change the entry in the table that is the base for the RowSource of the combo.

Image what you would have to do when you are storing the text in the table.

 

[Jrmrosebud]

So if I have a TransactionTypeT

TransactionType
Adjustment
Initial Inventory
Movement
Purchase
Usage

AddRemoveESBuilding
Remove
Add
Remove
Add
Remove

AddRemoveD3
Remove
Add
Add
Add
Remove

You are suggesting that I something like this?

TransactionType
Adjustment
Initial Inventory
Movement
Purchase
Usage

AddRemoveESBuilding
Remove
Add
Remove
Add
Remove

Ar_ESNumber
1
2
1
2
1

AddRemoveD3
Remove
Add
Add
Add
Remove

AR_D3Number
1
2
2
2
1

Where 1 stands for remove and 2 stand for Add? So then instead of basing the change on 'adjustment' or 'usage' it would be based on a value of '1' being present?