Can someone help me with this update query?

[netbytes]

Hi,

I have a problem with this query.


UPDATE MorningBite SET MorningBite.hideproduct =
IIf(MorningBite.customfield1='OUT OF STOCK',"Y","N");

My main purpose in this query is to make the field
customfield1 as the reference and hideproduct will be
the output for Y and N.

Example.

Table: Morningbite
Field: customfield1 and hideproduct

customfield1 hideproduct
OUT OF STOCK Y
out of stock Y
Out Of Stock! Y
IN STOCK! N

how to use case sensitive here? because i have in the
code customfield1 = 'OUT OF STOCK" only. How will i put
other options?
because its not possible to have like these out of
stock, Out Of Stock!,etc.

customfield1 is not only equal to "OUT OF STOCK"
but many like "OUT OF STOCK!" , "Out of Stock" etc

I want to add them but how?

Thank you.

 

[Alc]

Would this work

UPDATE MorningBite SET MorningBite.hideproduct = 
IIf(Instr(UCase(MorningBite.customfield1),'OUT OF STOCK')<>0,"Y","N");

 

[netbytes]

nice. it works.. thx yah bro!
btw.. may i know whats the use of

Instr and the UCase? here

UPDATE MorningBite SET MorningBite.hideproduct = IIf(Instr(UCase(MorningBite.customfield1),'OUT OF STOCK')<>0,"Y","N");

uhmm! is this true about your code? any OUT OF STOCK word will have Y right? any case?

example: OUT of STock, OuT of stOck, OUT OF STOCK!, etc.

thx again

 

[netbytes]

Would this work

UPDATE MorningBite SET MorningBite.hideproduct = 
IIf(Instr(UCase(MorningBite.customfield1),'OUT OF STOCK')<>0,"Y","N");

How to put a NULL here?
coz if ever there's a NULL value and bro one more thing customfield1 = "OUT OF STOCK" and "DISCONTINUED"?
how to write ?

thx

 

[Alc]

Got lucky first time!

UCase(some string) converts it all to upper case e.g. 'test', 'Test', 'TeSt' would all be converted to 'TEST'. Useful for comparisons, as you desfibed.
There are other functions in Help that convert to lower case (e.g. test) and proper case (Test).

Instr() looks for one string in another e.g. Instr("THISISATEST","TEST") would return a number, specifically the character at which the word "TEST" starts, while Instr("THISISATEST","LOST") would return a zero, as the second string isn't in the first.

 

[Alc]

How to put a NULL here?
coz if ever there's a NULL value and bro one more thing customfield1 = "OUT OF STOCK" and "DISCONTINUED"?
how to write ?

Sorry, a null where? You mean if customfield1 is null? Try

UPDATE MorningBite SET MorningBite.hideproduct = 
IIf(Instr(UCase(Nz(MorningBite.customfield1)),'OUT OF STOCK')<>0,"Y","N");

I forget the exact explanation of Nz, but it's covered in Help.

As far as the Discontinued thing goes, you could use nested If statements
e.g. if you wanted to use "A" where 'Out of Stock' applied, "B" where 'Discontinued' applies, and "C" where neither does

UPDATE MorningBite SET MorningBite.hideproduct = 
IIf(Instr(UCase(Nz(MorningBite.customfield1)),'OUT OF STOCK')<>0,"A",IIf(Instr(UCase(Nz(MorningBite.customfield1)),'OUT OF STOCK')<>0,"B","C");

You're essentially saying
If condition one is true, value is A.
If not, but condition two is true, value is B.
If not, value is C.

 

[netbytes]

actually,

customfield1 is not only equivalent to OUT OF STOCK but so many cases.
customfield1, the field in my table, has a lot of words like IN STOCK, OUT OF STOCK, DISCONTINUED, LOW STOCK, etc.

so what im trying to find out is how to write if customfield1 = OUT OF STOCK and also DISCONTINUED.

so that their field which is hideproduct will be Y.

so these are the cases.

If customfield1 = "OUT OF STOCK" and "DISCONTINUED" then hideproduct = "Y"

If customfield1 = "IN STOCK" and "LOW STOCK" then hideproduct = "N"

.. so hows this? combining two options..


and also If customefield has a null value?

 

[Rabbie]

In Access you need to say something like

If customfield1 like "*OUT OF STOCK*" and customfield1 like "*DISCONTINUED*" then hideproduct = "Y"

but perhaps you are looking for

If customfield1 like "*OUT OF STOCK*" OR customfield1 like "*DISCONTINUED*" then hideproduct = "Y"

 

[netbytes]

Sorry, a null where? You mean if customfield1 is null? Try

UPDATE MorningBite SET MorningBite.hideproduct = 
IIf(Instr(UCase(Nz(MorningBite.customfield1)),'OUT OF STOCK')<>0,"Y","N");

I forget the exact explanation of Nz, but it's covered in Help.

>>>>>>>>>>>> Nz not working. anyway IsNull maybe.. but still not running..

yah there's a null in the customfield1 field.

thankx again,!

btw where r u from?

 

[WayPay]

In Access you need to say something like

If customfield1 like "*OUT OF STOCK*" and customfield1 like "*DISCONTINUED*" then hideproduct = "Y"

Rabbie, don't you mean
If customfield1 like "*OUT OF STOCK*" OR customfield1 like "*DISCONTINUED*" then hideproduct = "Y"?

 

[netbytes]

In Access you need to say something like

If customfield1 like "*OUT OF STOCK*" and customfield1 like "*DISCONTINUED*" then hideproduct = "Y"

but perhaps you are looking for

If customfield1 like "*OUT OF STOCK*" OR customfield1 like "*DISCONTINUED*" then hideproduct = "Y"

ok thank you sir

let me try that..