Access database crashes trying to save query

[chris]

When running the following query in Access 2002, I am able to see my results
in the data sheet view, but upon attempting to save the query, the Access
application crashes.

SELECT UIMSMGR_UIBCINV.UIBCINV_CODE, UIMSMGR_UIBCINV.UIBCINV_MODEL,
UIMSMGR_UIBCINV.UIBCINV_CAPACITY, UIMSMGR_UIBCINV.UIBCINV_PTYP_CODE,
Left([UIBCINV_PTYP_CODE],2) AS [ASSET TYPE],
Switch(InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2 And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=3 And
InStr(4,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=4,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],1),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2
And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=3,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],2),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2
And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")<>3,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],3),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"1")=2,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],4)) AS [ASSET NO]
FROM UIMSMGR_UIBCINV
WHERE (((UIMSMGR_UIBCINV.UIBCINV_STUS_CODE)="I"))
ORDER BY Switch(InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2 And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=3 And
InStr(4,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=4,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],1),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2
And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=3,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],2),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2
And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")<>3,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],3),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"1")=2,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],4));

 

[Jerry Whittle]

What a monster! All this just to get data out of one table? Me thinks that
there's some serious normalization issues going on here.

First thing in troubleshooting SQL is to simplify. Try removing the entire
Order By statement and see if it runs better. If so remove the Switch
statement. If not, remove the Switch statement from up in the Select clause.

It's possible that you've run into a length of SQL statement problem
although I seem to remember that it's been greatly increased. Also you are
using the bang ! instead of dot . to identify the table.field combination.
You could simplify things by using an alias for your table like so. Watch out
for word wrapping.

SELECT UIMSMGR_UIBCINV.UIBCINV_CODE,
UIMSMGR_UIBCINV.UIBCINV_MODEL,
UIMSMGR_UIBCINV.UIBCINV_CAPACITY,
UIMSMGR_UIBCINV.UIBCINV_PTYP_CODE,
Left([UIBCINV_PTYP_CODE], 2) AS [ASSET TYPE],
Switch(InStr(2, UU.[UIBCINV_CODE], "0")=2
and InStr(3, UU.[UIBCINV_CODE], "0")=3
and InStr(4, UU.[UIBCINV_CODE], "0")=4,
Right(UU.[UIBCINV_CODE], 1),
InStr(2, UU.[UIBCINV_CODE], "0")=2
and InStr(3, UU.[UIBCINV_CODE], "0")=3,
Right(UU.[UIBCINV_CODE], 2),
InStr(2, UU.[UIBCINV_CODE], "0")=2
and InStr(3, UU.[UIBCINV_CODE], "0")<>3,
Right(UU.[UIBCINV_CODE], 3), InStr(2,
UU.[UIBCINV_CODE], "1")=2,
Right(UU.[UIBCINV_CODE], 4)) AS [ASSET NO]
FROM UIMSMGR_UIBCINV UU
WHERE (((UU.UIBCINV_STUS_CODE)="I"))
ORDER BY Switch(InStr(2, UU.[UIBCINV_CODE], "0")=2
and InStr(3, UU.[UIBCINV_CODE], "0")=3
and InStr(4, UU.[UIBCINV_CODE], "0")=4,
Right(UU.[UIBCINV_CODE], 1),
InStr(2, UU.[UIBCINV_CODE], "0")=2
and InStr(3, UU.[UIBCINV_CODE], "0")=3,
Right(UU.[UIBCINV_CODE], 2),
InStr(2, UU.[UIBCINV_CODE], "0")=2
and InStr(3, UU.[UIBCINV_CODE], "0")<>3,
Right(UU.[UIBCINV_CODE], 3),
InStr(2, UU.[UIBCINV_CODE], "1")=2,
Right(UU.[UIBCINV_CODE], 4));

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

When running the following query in Access 2002, I am able to see my results
in the data sheet view, but upon attempting to save the query, the Access
application crashes.

SELECT UIMSMGR_UIBCINV.UIBCINV_CODE, UIMSMGR_UIBCINV.UIBCINV_MODEL,
UIMSMGR_UIBCINV.UIBCINV_CAPACITY, UIMSMGR_UIBCINV.UIBCINV_PTYP_CODE,
Left([UIBCINV_PTYP_CODE],2) AS [ASSET TYPE],
Switch(InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2 And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=3 And
InStr(4,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=4,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],1),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2
And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=3,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],2),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2
And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")<>3,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],3),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"1")=2,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],4)) AS [ASSET NO]
FROM UIMSMGR_UIBCINV
WHERE (((UIMSMGR_UIBCINV.UIBCINV_STUS_CODE)="I"))
ORDER BY Switch(InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2 And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=3 And
InStr(4,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=4,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],1),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2
And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=3,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],2),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")=2
And
InStr(3,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"0")<>3,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],3),InStr(2,[UIMSMGR_UIBCINV]![UIBCINV_CODE],"1")=2,Right([UIMSMGR_UIBCINV]![UIBCINV_CODE],4));