How do I write a query so if a field is blank it won't show

[MJ]

I have 4 pricing levels for products. Each customer has only 1 level.
If a customer is pricing level 1, I don't want pricing levels 2-4 to
show up on the query for each product. How can I accomplish this?

Thanks in advance for your help!

[MGFoster]

MJ wrote:

I have 4 pricing levels for products. Each customer has only 1 level.
If a customer is pricing level 1, I don't want pricing levels 2-4 to
show up on the query for each product. How can I accomplish this?

Thanks in advance for your help!

Put a criteria on the query like

WHERE pricing_level IS NOT NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[MJ]

I have tried that and it didn't work.

I need a formula for each Level in the query that tells it if the
Customer Pricing Level is 1 that it shouldn't show pricing for levels
2, 3, 4. I just don't know how to write the formula. The fields are:

PricingLevel (from the CustomerInfo Table)

PriceLevel1 (from the Product Table)
PriceLevel2 (from the Product Table)
PriceLevel3 (from the Product Table)
PriceLevel 4 (from the Product Table).

If a customer has purchased an item and it level 1, I don't want it to
show the prices for the other 3 levels for the sasme item. I hope
that makes sense.

[Rick Brandt]

MJ wrote:

I have tried that and it didn't work.

I need a formula for each Level in the query that tells it if the
Customer Pricing Level is 1 that it shouldn't show pricing for levels
2, 3, 4. I just don't know how to write the formula. The fields are:

PricingLevel (from the CustomerInfo Table)

PriceLevel1 (from the Product Table)
PriceLevel2 (from the Product Table)
PriceLevel3 (from the Product Table)
PriceLevel 4 (from the Product Table).

If a customer has purchased an item and it level 1, I don't want it to
show the prices for the other 3 levels for the sasme item. I hope
that makes sense.

Sorry, it doesn't. What exactly are you looking at where you don't want to see
the other price levels? Directly at the query? If only one of them will ever
have a price entered why not just add them all together and display the result
in your query? Then you have a single price level field in the query output
regardless of which field in the source was used.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[MJ]

I am looking for the Price Level in a query to pull the correct price
for an item and store it in a table. I also would like to be able to
only see that correct price in a form.

[pietlinden]

sounds like you need to normalize your pricing level information.

tblPricingLevel
ProductID
PricingLevelID
Price

eg
(1,1,50)
(1,2,55)
(1,3,60)
(1,4,65)

Then you could just use a query. if you want to save the value into
your table, you could do that in the afterupdate event of the productID
control on your form.

[Ali]

if PricingLevel is integer use "Choose" function:

choose(PricingLevel,PriceLevel1,PriceLevel2,PriceL evel3,PriceLevel4)
if it is string (such as "PricingLevel1") you should change it to
integer first.