RE: Access database crashes trying to save query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.


"chris" wrote:

> When running the following query in Access 2002, I am able to see my results
> in the data *** 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));
>
>
> --
> Thanks,
> Chris
.

Quantcast