Using CASE in an access Update statement

[Azzna]

I am trying to do a conditional update in access using a case
statement. I have done this in Oracle, and I know it works, but I
can't get it to work in Access. When I run the statement it tells me I
have a syntax error (Missing Operator) in my query. I would appreciate
any help as I have been beating my head against this for about two
hours now and I can't seem to see the problem.

Here is my code:

update Import
Set Import.ArtGrp = Case( when Desc15 < 201
Then ArtGrp = Desc15
When Desc15 < 1000
Then ArtGrp = Right( Desc15,2)
When Desc15 >9999
Then ArtGrp = Right( Desc15,3))
END
WHERE Desc15 IS NOT NULL;

I keep finding conflicting information via searches on the web as to if
I can even do a case statement in an update in access. If I can't, is
there another way to do this short of creating three seperate queries?


Thank you!

 

[Jeff Boyce]

Have you tried using the IIF() function in the query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Hairgrove]

I am trying to do a conditional update in access using a case
statement. I have done this in Oracle, and I know it works, but I
can't get it to work in Access. When I run the statement it tells me I
have a syntax error (Missing Operator) in my query. I would appreciate
any help as I have been beating my head against this for about two
hours now and I can't seem to see the problem.

Here is my code:

update Import
Set Import.ArtGrp = Case( when Desc15 < 201
Then ArtGrp = Desc15
When Desc15 < 1000
Then ArtGrp = Right( Desc15,2)
When Desc15 >9999
Then ArtGrp = Right( Desc15,3))
END
WHERE Desc15 IS NOT NULL;

I keep finding conflicting information via searches on the web as to if
I can even do a case statement in an update in access. If I can't, is
there another way to do this short of creating three seperate queries?


Thank you!

You can do this in Access using nested IIf() statements. However, you
need to watch out for order of evaluation here; if Desc15 < 201, then
it is also < 1000. I always get tired of second-guessing Access when
it comes to these things; better to use ranges instead of less-than. A
naive translation of the above to nested IIf() will probably cause
Access to evaluate the conditions backwards to what you need.

Also, this smells of a non-normalized design; Desc15 appears to carry
more than one item of information. But since the table name is
"Import", maybe this an update query to normalize data imported from
some flat-file database?

 

[Azzna]

Yeah, the data is non normalized. It comes from another source and I
have to normalize it before I can use it. So this update is to
actually normalize the data so I can use it. It's part of a larger
procedure to normalize the whole file I receive.

I will have to try the nested iifs. Right now I am working with test
data, so I can mess it up till I get the right series of iifs down.

Thanks for the help!

 

[Bob Hairgrove]

I will have to try the nested iifs. Right now I am working with test
data, so I can mess it up till I get the right series of iifs down.

Actually, after giving it some thought, I think the order of
evaluation is always left-to-right, so it is similar to CASE.

However, there is no short-cut of evaluation as in C or C++ ... each
expression is always evaluated, so you cannot do something like this
without getting a "Division by 0" error when x=0:

IIf(x=0, 0, 3/x)

 

[John Nurick]

Might be worth trying SWITCH() instead.

Case( when Desc15 < 201
Then ArtGrp = Desc15
When Desc15 < 1000
Then ArtGrp = Right( Desc15,2)
When Desc15 >9999
Then ArtGrp = Right( Desc15,3))
END

SWITCH(Desc15 < 201, Desc15,
Desc15 < 1000, Right(Desc15,2),
Desc15 > 9999, Right(Desc15,3))

But what do you want to happen when 1000<=Desc15<=9999 ?

 

[Bob Hairgrove]

Might be worth trying SWITCH() instead.


SWITCH(Desc15 < 201, Desc15,
Desc15 < 1000, Right(Desc15,2),
Desc15 > 9999, Right(Desc15,3))

But what do you want to happen when 1000<=Desc15<=9999 ?

Who knows? I wasn't the OP.

Access doesn't support SWITCH, either, AFAIK.

 

[John Nurick]

Access doesn't support SWITCH, either, AFAIK.

Do you think I would have suggested using it if I didn't know it worked?
(Not just in Access, btw: it's in Jet SQL, though it's hard to find the
documentation.)

 

[Azzna]

Theoretically the data should never go above 9999, however, I put an
error value into the field in case there was a number higher then 9999
(Value of field should never be 0, so I set it to 0 and then flagged it
as an error) I made an nested if statement work to get the answer I
needed. I am glad to know that switches work in access though, Thank
you!

 

[Bob Hairgrove]

Do you think I would have suggested using it if I didn't know it worked?
(Not just in Access, btw: it's in Jet SQL, though it's hard to find the
documentation.)

Since when is Switch "in Jet SQL"? Last time I looked, Switch is/was a
VBA function.

 

[John Nurick]

Since when is Switch "in Jet SQL"? Last time I looked, Switch is/was a
VBA function.

Switch is a VBA function, but it's one of many that are supported by the
Jet database engine's VBA expression service and are always available in
Jet SQL regardless of whether Access (or any other VBA app) is in the
picture.

F'rinstance, I've just used a little Perl script to have Jet execute
this SQL statement to export data from Nortwind to a CSV file:

SELECT LastName,
SWITCH(TitleOfCourtesy = 'Ms.', 'Mizz',
TitleOfCourtesy = 'Dr.', 'Doctor',
-1, 'Mr or Mrs') AS Title
INTO [Text;HDR=Yes;Database=C:\Temp\;].TestSwitch#txt
FROM Employees;


##########DAO_Execute.pl##################################
use strict;
use Win32::OLE;

die "syntax:
perl DAO_Execute.pl mdbfile query [[query]...]

mdbfile: filespec of mdb database file
query: name of a stored action query, or a SQL statement
" unless $ARGV[1];

my $Jet; #DAO.DatabaseEngine
my $DB ; #DAO.Database
my $mdb = shift @ARGV; #database file to work on
my $SQL;

$Jet = Win32::OLE->CreateObject('DAO.DBEngine.36')
or die "Can't create Jet database engine.";

$DB = $Jet->OpenDatabase($mdb)
or die "Can't open mdb file $mdb)";

foreach $SQL (@ARGV) {
$DB->Execute($SQL, 128); #128=DBFailOnError
my $recordsAffected = $DB->RecordsAffected;
warn "Query '$SQL': $recordsAffected records affected.\n";
} ;

$DB->Close;
#################################################