Iif Function in a query ...

[valkyry]

Hello,
I can't figure out what's wrong or what else to add to this

Item: IIf([SKU] Like "WS*","LSM" & Right([SKU],3),"SCONCE")

The issue with this is that it's putting everything else to SCONCE even when the SKU isn't like WS*

Can you help?

 

[Remou]

Hi
If SKU is a control on a form, then this works for me. If not, perhaps you can say where this is coming from? For example, query etc.

 

[valkyry]

Hello,
it's from a query ... sorry, i had it in the subject and not in the content ...

 

[Remou]

My fault .
Ok, I tried your line in a query against a table with one field, SKU, and it worked (Access2000).

 

[valkyry]

Do a group with a few other columns

I have a few skus that are WS* and LS* and MO*

The ones with the WS* is suppose to return only

and return the ones with WS* as LS### and SCONCE

but the SCONCE is also coming up for the LS* and the MO*

hope that helps

 

[Lilliabeth]

YOur formula is telling Access to return LS### only if the SkU begins with WS, otherwise it should return SCONCE.


I am unclear as to what it is you want it to do.

 

[Remou]

No, didn't quite catch that. Here is my mock-up:

[tt]Expr1 SKU
LSMdfr WSdfr
SCONCE ABC
LSMdef WSdef
LSMcfg wscfg
SCONCE moasd
SCONCE lsdrf
SCONCE lscde
SCONCE moghr[/tt]

Where Expr1 is your expression and SKU is the data being translated. What should it look like?

 

[PHV]

You wanted this ?
Item: IIf([SKU] Like "WS*","LSM" & Right([SKU],3) & "SCONCE", [SKU])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[valkyry]

great

the 2nd SKU "ABC" and the last 4 should not come up as the SKU does not have WS* in them.

the issue is that I'm thinking I'm missing something else in the formula.

You see in need one to return SCONCE with adding up the WS* skus quantities.

So the 3 WS* SKUS added up to 20 in qty, then one row of SCONCE with qty 20 is what I want it to return.

Hope this helps more

and thank yoU!

 

[PHV]

should not come up as the SKU does not have WS* in them
Item: 'LSM' & Right([SKU],3)

In the criteria cell for [SKU]: Like 'WS*'


The SQL code may be something like this:
SELECT 'LSM' & Right([SKU],3) As Item, qty
FROM yourTable
WHERE SKU Like 'WS*'
UNION ALL SELECT 'SCONCE', Sum(qty)
FROM yourTable
WHERE SKU Like 'WS*'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[valkyry]

oh, that looks better

i'll try it

thank you!

 

[PHV]

oh, that looks better
Surely because the question was better asked ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886