Access 2010 Iif Query

[PWD]

Good afternoon, I'm trying to put together an update query to populate an 'exclusion' field - "Not on Product Look-Up" and have put this in the Update To line:

IIf([SampleCombined]![excl_prod]="Yes","",IIf([SampleCombined]![no_lookup]="Yes","Yes",""))

What I intend is:-

If field excl_prod = "Yes" then don't put anything in Not on Product Look-Up. If it's not then if field no_lookup = "Yes" then put "Yes".

The database consists of over 112,000 rows and around 100 columns and this query takes a while to run and updates all the rows. Is there a better way to write this?

This is the first of several similar queries that will populate each row of data with only one 'exclusion' field per row. We had been doing this (successfully!) on a weekly basis with Excel but are now having to put together a 6 month summary and there's just too much data for it to cope.

Many thanks,
D€$

 

[dhookom]

I expect this might work. I always run potential update queries as select queries first. Also, I wouldn't store "" in a field. I would use Null. If the fields are Y/N, I would remove the strings "Yes" and replace with -1 or True.

It isn't clear where you are putting the result.

Duane
Hook'D on Access
MS Access MVP

 

[PWD]

Hi Duane, I know it works because I only got the syntax by building an expression in Select first. I like NULL - thanx for that tip. I'll also try the -1. Basically my query just has one field - "Not on Product Look-Up" - and I have the Iif synatx on the line "Update To:" The "Field" is 'Expr1: Not On Product Look-Up'. It just seems to suck the life out of the PC although the processor appears to be only running at 4% I get the message that there's not enough memory or disk space but it trundles along. I have had time to type this reply - a few times over - while watching the query execute. And it's still not finished - in fact it seems worse than it was

Many thanks,
D€$

 

[PHV]

What is the SQL code of your query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Update SampleCombined Set [Not on Product Look-Up] = True where excl_prod = True or no_lookup = True

If these are not boolean fields (which they should be) then replace True with "Yes". This is way more efficient.

 

[PWD]

Hi PHV, this is the SQL:

UPDATE SampleCombined SET SampleCombined.[Not on Product Look-Up] = IIf([SampleCombined]![excl_prod]="Yes",Null,IIf([SampleCombined]![no_lookup]="Yes",-1,Null));

Many thanks,
D€$

 

[PWD]

This seems to work just fine:-

UPDATE SampleCombined SET SampleCombined.[Excluded products] = -1
WHERE (((SampleCombined.[Not on Product Look-Up])<>-1) AND ((SampleCombined.excl_prod)="Yes"));

Must have been over-thinking this one

The fields I've imported are "Yes/No" but I've set up my 'exclusion' fields as boolean.

So this is actually the second query to run after I've set the 'Not on Product Look-Up' flag. I think I have a much clearer vision now.

Many thanks,
D€$