HELP! Convert SQL query to Access?

[Byron]

For some reason the query below returns an error in Access complaing of a
missing operator, but it executes fine in SQL 2000. I don't normally use
Access, so I'm at a loss here. Can anybody tell me what might be wrong with
it in Access's eyes? It just inserts a child node into a tree where the ID
of the parent node = 1.

Schema in brief:
U_ID - primary key (int)
U_Left - Left limit of node (int)
U_Right - Right limit of node (int)
U_name - Name of node (string)

For example, the parent node starts with a U_ID of 1 and U_Left and U_Right
of 1 and 2 respectively. I insert a child node with U_Left and U_Right of 0,
the run this query to adjust the child node to 2 and 3, and the parent node
to 1 (no change) and 4, so that the child nodes U_Left and U_Right fall
between those of the parent. Any other nodes are adjusted by the same call.

UPDATE Unit
SET U_Left = CASE
WHEN U_Left > (SELECT U1.U_Left FROM Unit AS U1 WHERE U1.U_ID = 1) THEN
U_Left + 2
WHEN U_Left = 0 THEN (SELECT U1.U_Left + 1 FROM ExerciseUnit AS U1 WHERE
U1.U_ID = 1)
ELSE U_Left END,
U_Right = CASE WHEN U_Right > (SELECT U1.U_Left FROM Unit AS U1 WHERE
U1.U_ID = 1D) THEN U_Right + 2
WHEN U_Left = 0 THEN (SELECT U2.U_Left + 2 FROM Unit AS U2 WHERE U2.U_ID = 1)
ELSE U_Right
END

 

[John Spencer (MVP)]

Access doesn't support the CASE statement in SQL. You can use nested IIF
functions, the Switch function, or write a user-defined function in VBA. You
might also want to look at the DLookup function.


UPDATE Unit
SET U_Left = CASE
WHEN U_Left > (SELECT U1.U_Left FROM Unit AS U1 WHERE U1.U_ID = 1) THEN
U_Left + 2
WHEN U_Left = 0 THEN (SELECT U1.U_Left + 1 FROM ExerciseUnit AS U1 WHERE
U1.U_ID = 1)
ELSE U_Left END,
U_Right = CASE WHEN U_Right > (SELECT U1.U_Left FROM Unit AS U1 WHERE
U1.U_ID = 1D) THEN U_Right + 2
WHEN U_Left = 0 THEN (SELECT U2.U_Left + 2 FROM Unit AS U2 WHERE U2.U_ID = 1)
ELSE U_Right
END

UPDATE UNIT
SET U_LEFT = SWITCH(U_LEFT >DLookup("U_Left","Unit","U_ID=1"),U_Left+2,
U_LEFT = 0,DLookup("U_Left +1","ExerciseUnit","U_ID=1"),
U_Right= ...