HELP! Convert SQL query to Access?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Byron (Byron_at_discussions.microsoft.com)
Date: 10/09/04 

Date: Sat, 9 Oct 2004 09:07:02 -0700

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

Relevant Pages

  • Re: Count all nodes in a treeview
    ... ItemIndex of the nodes in the tree. ... Parent Node: 0 ... Child Node: 0 ... a friend who codes in Delphi and has been using C# for quite a while. ...
    (microsoft.public.dotnet.languages.csharp)
  • Delete Child Nodes In TreeView?
    ... Assume that a TreeView has 2 Parent nodes & each Parent node has 4 ... If all the 4 Child nodes are deleted one by one under a Parent node, ... items" should get added as a Child node under that Parent node. ... Set objNode = TreeView1.SelectedItem ...
    (microsoft.public.vb.general.discussion)
  • Re: I want a right click to cause a treenode to become selected, how?
    ... >> Okay, new to win forms; I have a treeview with several root nodes, each ... I also havea context ... >> This happening becuase the right click on said child node does not cause ... the parent node remains the selected node! ...
    (microsoft.public.dotnet.general)
  • Re: Treeview - Identify top level node
    ... ' Now once you got your parent node you can use tag/text/name properties ... ' or any other means to determine if this is NodeA or NodeB ... What I want is to be able to identify whether the child node I select ...
    (microsoft.public.dotnet.languages.vb)