Thanks for your response. I apologize for making it hard
to understand.
I am trying to create a query that shows all part#s and
pricing associated with them, but for some clients I need
to exclude specific part numbers based on an affiliation.
I created a query that shows all the fields and I am
trying to build an If statement that says if this client
is affiliated, then just show me Is Null (since I have a
table of Part#s that need to be excluded) and if there are
not affiliated, show me all.
Here is the SQL:
SELECT [Customer Pricing].[Cust #], [Customer Pricing].
[Cust Name], [Sales Alignment].[Sales Rep #], [Sales
Alignment].[Sales Rep], [Master Product Table].[PH Two],
[Master Product Table].[PH Three], [Master Product Table].
[PH Four], [Master Product Table].[PH Five], [Master
Product Table].Material, [Master Product Table].[Catalog
Number], [Premier UPNs].UPN AS [Prem UPN], [Consorta
UPNs].UPN AS [Cons UPN], [Broadlane UPNs].UPN AS [Broad
UPN], [HPG UPNs].UPN AS [HPG UPN], [Master Product Table].
[Material Description], [Customer Pricing].[Cust Price],
[Master Product Table].[2003 List Price], [Master Product
Table].[2004 List Price], [Customer Pricing]![Cust Price]/
[Master Product Table]![2003 List Price] AS [%of03List], [%
of03List]*[Master Product Table]![2004 List Price] AS
NewCustPrice, [Customer Pricing]![Cust Price]/[Master
Product Table]![2004 List Price] AS NoInc, Affiliation.Aff
FROM [HPG UPNs] RIGHT JOIN ([Consorta UPNs] RIGHT JOIN
([Broadlane UPNs] RIGHT JOIN ([Premier UPNs] RIGHT JOIN
((([Customer Pricing] INNER JOIN [Master Product Table] ON
[Customer Pricing].[Cat #] = [Master Product Table].
[Catalog Number]) INNER JOIN [Sales Alignment] ON
[Customer Pricing].[Cust #] = [Sales Alignment].[Cust #])
INNER JOIN Affiliation ON [Customer Pricing].[Cust #] =
Affiliation.[Cust #]) ON [Premier UPNs].UPN = [Master
Product Table].Material) ON [Broadlane UPNs].UPN = [Master
Product Table].Material) ON [Consorta UPNs].UPN = [Master
Product Table].Material) ON [HPG UPNs].UPN = [Master
Product Table].Material
WHERE ((([Sales Alignment].[Sales Rep #])=[Enter Sales
Rep]) AND (([Premier UPNs].UPN)=IIf([Affiliation]![Aff]
="Prem",Null,"*")));
-----Original Message-----
Dear AJ:
I am thinking that Affiliation.Aff is a Table.Column reference.
Right?
And your query has a join to the Affiliation table?
So, what you want is to exclude those rows where the value of this
column is NULL?
That would be simply:
WHERE Affiliation.Aff IS NOT NULL
or in the Design Grid:
Field: Aff
Table: Affiliation
Criterion: IS NOT NULL
But this is a lot of guess work. How about posting your entire query
so far and giving an unambiguous description of what you want it to
do.
However, I do not see where you have given any reason why you would be
using an IIf() here.
So, please help me to be able to help you.
I am writing a query that has an If statement and I can't
get it to work.
I need to be able to run pricing for a customer, but
before running the report, I need to eliminate certain
products based on an affiliation.
I created an if statement that said, if this customer is
affiliated with "x", then just show me the values that
are not in one master product file (IS Null) command.
Othterwise, if the customer is not affiliated, then I
need it to return all values.
It looks like this:
Iif ([Affiliation]![Aff]="Aff","Is Null","*")
I can not get it to work. I am stumped and need this to
be done quickly. Also if there is a better way to do
this, please show me. I would appreciate any help that
anyone can give me.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
