Got these fields:

Band
Product Division
Specials
Sales

All I want to say is if:

Band = N and Product Division <> “Bought-In Furniture”

Or

Band = N and Specials Is Null

Then Sales Else 0

Cant write the expression. Get close but not close enough!!!!!

your logic makes no sense. please try to explain again.

Sorry I'll try again:

I have 4 column headings (that are needed for this problem)

Band ProductDivision Specials Sales

Band can either be "N" or "E"
Product Division contains a record "Bought-In Furniture"
Specials can either be "Y" or Null
Sales is a value

I need to create a 5th column saying:

if Band = N and Product Division <> “Bought-In Furniture”

Or

if Band = N and Specials Is Null

Then Sales Otherwise I want the value to be 0

Any clearer?

I have 4 column headings (that are needed for this problem)

Band ProductDivision Specials Sales

Band can either be "N" or "E"
Product Division contains a record "Bought-In Furniture"
Specials can either be "Y" or Null
Sales is a value

I need to create a 5th column


that bit i understood.


if Band = N and Product Division <> “Bought-In Furniture”

Or

if Band = N and Specials Is Null

Then Sales Otherwise I want the value to be 0


this bit was the 'funny' bit.

why not just add criteria in your query design that goes like


Field [Band] [Product Division] [Specials] [Sales]
Criteria N Not "Bought-In Furniture"
Criteria N Is Null
is that what you mean?

or do you mean



Then Sales = "Y", Otherwise Null



(edit: and do you mean it to be an update query?)

It's very hard to describe when you dont have the full picture. The end game is to apply a % to each sales value depending on 23 different scenarios.

Scenario 15 was the one I described. So my idea was to carry the sales value over to a new column if it was applicable otherwise put a zero. Cant have 0% of 0

So I want to show the sales value where Band = "N" and ProductDivision <>"Bought-In Furniture" etc

My idea was to have my columns of data then have the 23 scenarios on the right of the table either putting in the sales value or putting a zero

I understand this is not a very good explanation but basically I need to write that formula in an expression. This is what I have tried so far:

Rule15: IIf([tbl_RawData]![Band]="N" And ([tbl_RawData]![ProductDivision]<>"Bought-In Furniture"),IIf([tbl_RawData]![Band]="N" And ([tbl_RawData]![Specials] Is Null),[tbl_RawData]![Sales],0))

I am trying to put the part in red to say "Or" but to no avail

IIf(([tbl_RawData]![Band]="N" And ([tbl_RawData]![ProductDivision]<>"Bought-In Furniture") Or (IIf([tbl_RawData]![Band]="N" And ([tbl_RawData]![Specials] Is Null))),[tbl_RawData]![Sales],0))

Must be getting confused with my brackets somewhere as I'm getting a syntax error but this is what I need?

Try making it more readable...


IIf(([tbl_RawData]![Band]="N" And ([tbl_RawData]![ProductDivision]<>"Bought-In Furniture")
Or (IIf([tbl_RawData]![Band]="N" And ([tbl_RawData]![Specials] Is Null))),[tbl_RawData]![Sales],0))

To start with, for the OR aspect you dont need an additional IIF
IIf(([tbl_RawData]![Band]="N" And ([tbl_RawData]![ProductDivision]<>"Bought-In Furniture")
Or (([tbl_RawData]![Band]="N" And ([tbl_RawData]![Specials] Is Null))),[tbl_RawData]![Sales],0)

Now oblidorate (sp?) all suppurfolous ()
IIf( ([tbl_RawData]![Band]="N" And [tbl_RawData]![ProductDivision]<>"Bought-In Furniture")
Or ([tbl_RawData]![Band]="N" And [tbl_RawData]![Specials] Is Null),[tbl_RawData]![Sales],0)

Now take down the duplicate [Band] thing
IIf([tbl_RawData]![Band]="N"
And ( [tbl_RawData]![ProductDivision]<>"Bought-In Furniture"
Or [tbl_RawData]![Specials] Is Null)
,[tbl_RawData]![Sales]
,0)
That should be what your looking for and I think you will agree, its much more readable than your...
IIf( ([tbl_RawData]![Band]="N" And [tbl_RawData]![ProductDivision]<>"Bought-In Furniture") Or ([tbl_RawData]![Band]="N" And [tbl_RawData]![Specials] Is Null),[tbl_RawData]![Sales],0)

Greets from a windy Amsterdam

I agree its much neater! Thanks for your help......

But its now only putting a zero where Specials is Not Null AND ProductDivision = "Bought-In Furniture"

I need this to be either or

You are now basicaly turning around your premisse, make up your mind...

The current IIF will show [sales] if either Division <> "BIF" OR Specials is null

Thus will show 0 only if = BIF AND is not null, it makes sence...