Query Causes Hard Crash

**Tom Wickerath** · Nov 12 '05, 08:47 PM

Re: Query Causes Hard Crash

Adam,

This is one UGLY query! Using Access 2002 (SP2), with the Windows 2000 Server operating
system (512 MB RAM with SP4), I was able to initially save all three of your queries
without a problem. I then fired up my copy of Access 2000 (SP3) on the same PC. I was
able to reproduce your hard crash when attempting to save the humongous query. Now for
the really interesting part: After crashing in Access 2000, I started experiencing the
same symptom in Access 2002. I had to reboot my machine before it would let me save this
query again in 2002 without crashing!

Another observation: After saving the file in Access 2002, I opened it in Access 2000. It
opened ok, but as soon as I clicked on Save, it hard crashed. However, when I reopened
the same file in Access 2002, the query was still intact and could be saved without a
problem.

So far, I haven't had any luck in finding a specification relating to how many nested IIF
functions one can have. I suspect that this mess can be rewritten a WHOLE lot more efficiently
in a VBA procedure, using an IF...THEN....EL SE IF construct or a SELECT CASE construct.
Incidentally, in case anyone is interested, both Access 2000 and 2002 list the following
specification:

Number of characters in a cell in the query design grid 1,024

I created a quickie procedure to count the number of characters and it came back with 955.

Sub test()
Dim strSQL As String

strSQL = "Huge string from cell in query design pasted here"
MsgBox Len(strSQL)

End Sub

In Access 2002, if I click into the cell in query design view, and bring up the zoom window using
Shift F2, I get an error message informing me that "The expression you entered exceeds the
1,024-character limit for the query design grid." after dismissing the zoom dialog and attempting
to click out of the cell. Very strange. This only happens if I bring up the zoom window.

Also, shortening the table name "AnnualSurgeryA ndFollowupPSA" to less characters has not stopped
the hard crash in Access 2000. If you can send me a private e-mail message (note: return address
needs to be edited to remove 4 words from username) with the actual tables with data, I will look
further into this for you. Of course, you can use fictitious patient names (Mickey Mouse, Donald
Duck, etc.). It would be helpful to send an indication of what results you expect in the
recordset for various starting and ending years.

Tom
_______________ _______________ ______

"Adam Louis" <farkallyoupigf arkers@hotmail. com> wrote in message
news:26c90fcd.0 402181204.42e72 297@posting.goo gle.com...

I would like help resolving this problem. I'm a novice who's been
hired to query a hospital database and extract useful information,
available to me only in a dynamically generated, downloadable .mdb.
The query below query runs correctly and without error, but any
attempt to save it causes Access to crash without a message, leaving
the .ldb file. Opening the DB reveals it saved a blank "query1".
I've upgraded to Jet SP 8, and I'm running Office 2000 on WinXP.
Access runs fine, other than this. Any insight?

The query (counts the total number of PSA tests taken in 6-month
intervals of time after RP surgeries):

SELECT
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=0,'',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=6,'a
0-6',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=12,'b
6-12',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=18,'c
12-18',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=24,'d
18-24',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=30,'e
24-30',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=36,'f
30-36',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=42,'g
36-42',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=48,'h
42-48',
'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM AnnualSurgeryAn dFollowupPSA
GROUP BY
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=0,'',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=6,'a
0-6',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=12,'b
6-12',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=18,'c
12-18',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=24,'d
18-24',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=30,'e
24-30',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=36,'f
30-36',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=42,'g
36-42',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=48,'h
42-48',
'i 48+')))))))));

Other queries it calls:

AnnualSurgeryAn dFollowupPSA (lists each individual PSA test taken for
each patient after their RP surgery, where their surgery occurred
between given years):

SELECT patient.lastNam e, patient.firstNa me, RP.date,
IIf(PSA.totalPS A<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
PSA.totalPSA
FROM patient, RP, PSA
WHERE patient.patient ID=RP.patient And RP.date Between
DateValue('1/1/'+[StartingYear]) And DateValue('31/12/'+[EndingYear])
And patient.patient ID=PSA.patient And PSA.date>RP.dat e
ORDER BY patient.lastNam e, patient.firstNa me, RP.date, PSA.date;

RP (unifies the unique patient ID and RP surgery date of each patient
from 2 source tables):

SELECT RPRecord.patien t AS [patient], RPRecord.startD ate AS [date]
FROM RPRecord
UNION SELECT RP_Pathology.pa tient, RP_Pathology.pr ocedureDate FROM
RP_Pathology;

I'm inclined to blame Microsoft (I wouldn't have touched Access if I
didn't have to provide a simple interface for the Doctors) for YAFB
(Yet Another Fatal Bug), and put yet another batshit crazy, waving the
dead chicken voodoo curse on Bill Gates, but I'm open to the
possibility that this is my fault (being utterly 'teh n00b'), or that
this is easily fixable with settings changes or patches. The point
is, I really need this to work.

Thank you, in advance, for your help

**Tom Wickerath** · Nov 12 '05, 08:47 PM

Re: Query Causes Hard Crash

I just noticed your post on microsoft.publi c.access, dated 2/19 with subject = "Major, Crippling
Bug", where you report the same crash when using the switch function. FYI -- I am able to save
this query in Access 2000 without experiencing the same crash.

_______________ _______________ _______________ ___

"Tom Wickerath" <AOS168RemoveTh isSpamBlock@com cast.net> wrote in message
news:ctadnSb_pb CK0qrdRVn-jw@comcast.com. ..

Adam,

This is one UGLY query! Using Access 2002 (SP2), with the Windows 2000 Server operating
system (512 MB RAM with SP4), I was able to initially save all three of your queries
without a problem. I then fired up my copy of Access 2000 (SP3) on the same PC. I was
able to reproduce your hard crash when attempting to save the humongous query. Now for
the really interesting part: After crashing in Access 2000, I started experiencing the
same symptom in Access 2002. I had to reboot my machine before it would let me save this
query again in 2002 without crashing!

Another observation: After saving the file in Access 2002, I opened it in Access 2000. It
opened ok, but as soon as I clicked on Save, it hard crashed. However, when I reopened
the same file in Access 2002, the query was still intact and could be saved without a
problem.

So far, I haven't had any luck in finding a specification relating to how many nested IIF
functions one can have. I suspect that this mess can be rewritten a WHOLE lot more efficiently
in a VBA procedure, using an IF...THEN....EL SE IF construct or a SELECT CASE construct.
Incidentally, in case anyone is interested, both Access 2000 and 2002 list the following
specification:

Number of characters in a cell in the query design grid 1,024

I created a quickie procedure to count the number of characters and it came back with 955.

Sub test()
Dim strSQL As String

strSQL = "Huge string from cell in query design pasted here"
MsgBox Len(strSQL)

End Sub

In Access 2002, if I click into the cell in query design view, and bring up the zoom window using
Shift F2, I get an error message informing me that "The expression you entered exceeds the
1,024-character limit for the query design grid." after dismissing the zoom dialog and attempting
to click out of the cell. Very strange. This only happens if I bring up the zoom window.

Also, shortening the table name "AnnualSurgeryA ndFollowupPSA" to less characters has not stopped
the hard crash in Access 2000. If you can send me a private e-mail message (note: return address
needs to be edited to remove 4 words from username) with the actual tables with data, I will look
further into this for you. Of course, you can use fictitious patient names (Mickey Mouse, Donald
Duck, etc.). It would be helpful to send an indication of what results you expect in the
recordset for various starting and ending years.

Tom
_______________ _______________ ______

"Adam Louis" <farkallyoupigf arkers@hotmail. com> wrote in message
news:26c90fcd.0 402181204.42e72 297@posting.goo gle.com...

I would like help resolving this problem. I'm a novice who's been
hired to query a hospital database and extract useful information,
available to me only in a dynamically generated, downloadable .mdb.
The query below query runs correctly and without error, but any
attempt to save it causes Access to crash without a message, leaving
the .ldb file. Opening the DB reveals it saved a blank "query1".
I've upgraded to Jet SP 8, and I'm running Office 2000 on WinXP.
Access runs fine, other than this. Any insight?

The query (counts the total number of PSA tests taken in 6-month
intervals of time after RP surgeries):

SELECT
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=0,'',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=6,'a
0-6',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=12,'b
6-12',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=18,'c
12-18',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=24,'d
18-24',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=30,'e
24-30',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=36,'f
30-36',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=42,'g
36-42',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=48,'h
42-48',
'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM AnnualSurgeryAn dFollowupPSA
GROUP BY
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=0,'',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=6,'a
0-6',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=12,'b
6-12',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=18,'c
12-18',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=24,'d
18-24',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=30,'e
24-30',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=36,'f
30-36',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=42,'g
36-42',
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=48,'h
42-48',
'i 48+')))))))));

Other queries it calls:

AnnualSurgeryAn dFollowupPSA (lists each individual PSA test taken for
each patient after their RP surgery, where their surgery occurred
between given years):

SELECT patient.lastNam e, patient.firstNa me, RP.date,
IIf(PSA.totalPS A<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
PSA.totalPSA
FROM patient, RP, PSA
WHERE patient.patient ID=RP.patient And RP.date Between
DateValue('1/1/'+[StartingYear]) And DateValue('31/12/'+[EndingYear])
And patient.patient ID=PSA.patient And PSA.date>RP.dat e
ORDER BY patient.lastNam e, patient.firstNa me, RP.date, PSA.date;

RP (unifies the unique patient ID and RP surgery date of each patient
from 2 source tables):

SELECT RPRecord.patien t AS [patient], RPRecord.startD ate AS [date]
FROM RPRecord
UNION SELECT RP_Pathology.pa tient, RP_Pathology.pr ocedureDate FROM
RP_Pathology;

I'm inclined to blame Microsoft (I wouldn't have touched Access if I
didn't have to provide a simple interface for the Doctors) for YAFB
(Yet Another Fatal Bug), and put yet another batshit crazy, waving the
dead chicken voodoo curse on Bill Gates, but I'm open to the
possibility that this is my fault (being utterly 'teh n00b'), or that
this is easily fixable with settings changes or patches. The point
is, I really need this to work.

Thank you, in advance, for your help

**Lyle Fairfield** · Nov 12 '05, 08:47 PM

Re: Query Causes Hard Crash

farkallyoupigfa rkers@hotmail.c om (Adam Louis) wrote in
news:26c90fcd.0 402181204.42e72 297@posting.goo gle.com:
[color=blue]
> I would like help resolving this problem. I'm a novice who's been
> hired to query a hospital database and extract useful information,
> available to me only in a dynamically generated, downloadable .mdb.
> The query below query runs correctly and without error, but any
> attempt to save it causes Access to crash without a message, leaving
> the .ldb file.[/color]

I've had queries that would not save from the query window. I have been able
to save them in the following way:

Dim sql As String

sql = "SELECT * FROM tblBlah WHERE fldTransactionI D = parBlah"

CurrentProject. Connection.Exec ute _
"CREATE PROCEDURE qryBlah2 (parBlah Integer) AS " & sql

If no Parameters then (parBlah Integer) is redundant.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

**Adam Louis** · Nov 12 '05, 08:49 PM

Re: Query Causes Hard Crash

I found another resolution for the problem, but thank you for the
help, anyways. In case you're interested, although this was not my
final solution, I found, through experimentation , that (after having
switched from IIfs to SWITCHes (again, 'teh noob')), Access crashes
after having 7 cases, plus another 1 for a "TRUE, elseCase)". Again,
thank you for taking the time to answer my question.

Lyle Fairfield <MissingAddress @Invalid.Com> wrote in message news:<Xns94965B 541FB0AFFDBA@13 0.133.1.4>...[color=blue]
> farkallyoupigfa rkers@hotmail.c om (Adam Louis) wrote in
> news:26c90fcd.0 402181204.42e72 297@posting.goo gle.com:
>[color=green]
> > I would like help resolving this problem. I'm a novice who's been
> > hired to query a hospital database and extract useful information,
> > available to me only in a dynamically generated, downloadable .mdb.
> > The query below query runs correctly and without error, but any
> > attempt to save it causes Access to crash without a message, leaving
> > the .ldb file.[/color]
>
> I've had queries that would not save from the query window. I have been able
> to save them in the following way:
>
> Dim sql As String
>
> sql = "SELECT * FROM tblBlah WHERE fldTransactionI D = parBlah"
>
> CurrentProject. Connection.Exec ute _
> "CREATE PROCEDURE qryBlah2 (parBlah Integer) AS " & sql
>
> If no Parameters then (parBlah Integer) is redundant.[/color]

**Adam Louis** · Nov 12 '05, 08:49 PM

Re: Query Causes Hard Crash

Thank you, I'll try that (plus I have a source through which to get
2002 at academic prices). In case you're interested, although this
was is not my final solution, I found, through experimentation , that
(after having switched from IIfs to SWITCHes (again, 'teh noob')),
Access crashes after having 7 cases, plus another 1 for a "TRUE,
elseCase)". Again, thank you for taking the time to answer my
question.

"Tom Wickerath" <AOS168RemoveTh isSpamBlock@com cast.net> wrote in message news:<Y7CdnbgZX 812yqrd4p2dnA@c omcast.com>...[color=blue]
> I just noticed your post on microsoft.publi c.access, dated 2/19 with subject = "Major, Crippling
> Bug", where you report the same crash when using the switch function. FYI -- I am able to save
> this query in Access 2000 without experiencing the same crash.
>
> _______________ _______________ _______________ ___
>
> "Tom Wickerath" <AOS168RemoveTh isSpamBlock@com cast.net> wrote in message
> news:ctadnSb_pb CK0qrdRVn-jw@comcast.com. ..
>
> Adam,
>
> This is one UGLY query! Using Access 2002 (SP2), with the Windows 2000 Server operating
> system (512 MB RAM with SP4), I was able to initially save all three of your queries
> without a problem. I then fired up my copy of Access 2000 (SP3) on the same PC. I was
> able to reproduce your hard crash when attempting to save the humongous query. Now for
> the really interesting part: After crashing in Access 2000, I started experiencing the
> same symptom in Access 2002. I had to reboot my machine before it would let me save this
> query again in 2002 without crashing!
>
> Another observation: After saving the file in Access 2002, I opened it in Access 2000. It
> opened ok, but as soon as I clicked on Save, it hard crashed. However, when I reopened
> the same file in Access 2002, the query was still intact and could be saved without a
> problem.
>
> So far, I haven't had any luck in finding a specification relating to how many nested IIF
> functions one can have. I suspect that this mess can be rewritten a WHOLE lot more efficiently
> in a VBA procedure, using an IF...THEN....EL SE IF construct or a SELECT CASE construct.
> Incidentally, in case anyone is interested, both Access 2000 and 2002 list the following
> specification:
>
> Number of characters in a cell in the query design grid 1,024
>
> I created a quickie procedure to count the number of characters and it came back with 955.
>
> Sub test()
> Dim strSQL As String
>
> strSQL = "Huge string from cell in query design pasted here"
> MsgBox Len(strSQL)
>
> End Sub
>
> In Access 2002, if I click into the cell in query design view, and bring up the zoom window using
> Shift F2, I get an error message informing me that "The expression you entered exceeds the
> 1,024-character limit for the query design grid." after dismissing the zoom dialog and attempting
> to click out of the cell. Very strange. This only happens if I bring up the zoom window.
>
> Also, shortening the table name "AnnualSurgeryA ndFollowupPSA" to less characters has not stopped
> the hard crash in Access 2000. If you can send me a private e-mail message (note: return address
> needs to be edited to remove 4 words from username) with the actual tables with data, I will look
> further into this for you. Of course, you can use fictitious patient names (Mickey Mouse, Donald
> Duck, etc.). It would be helpful to send an indication of what results you expect in the
> recordset for various starting and ending years.
>
> Tom
> _______________ _______________ ______
>
> "Adam Louis" <farkallyoupigf arkers@hotmail. com> wrote in message
> news:26c90fcd.0 402181204.42e72 297@posting.goo gle.com...
>
> I would like help resolving this problem. I'm a novice who's been
> hired to query a hospital database and extract useful information,
> available to me only in a dynamically generated, downloadable .mdb.
> The query below query runs correctly and without error, but any
> attempt to save it causes Access to crash without a message, leaving
> the .ldb file. Opening the DB reveals it saved a blank "query1".
> I've upgraded to Jet SP 8, and I'm running Office 2000 on WinXP.
> Access runs fine, other than this. Any insight?
>
> The query (counts the total number of PSA tests taken in 6-month
> intervals of time after RP surgeries):
>
> SELECT
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=0,'',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=6,'a
> 0-6',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=12,'b
> 6-12',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=18,'c
> 12-18',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=24,'d
> 18-24',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=30,'e
> 24-30',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=36,'f
> 30-36',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=42,'g
> 36-42',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=48,'h
> 42-48',
> 'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM AnnualSurgeryAn dFollowupPSA
> GROUP BY
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=0,'',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=6,'a
> 0-6',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=12,'b
> 6-12',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=18,'c
> 12-18',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=24,'d
> 18-24',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=30,'e
> 24-30',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=36,'f
> 30-36',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=42,'g
> 36-42',
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=48,'h
> 42-48',
> 'i 48+')))))))));
>
> Other queries it calls:
>
> AnnualSurgeryAn dFollowupPSA (lists each individual PSA test taken for
> each patient after their RP surgery, where their surgery occurred
> between given years):
>
> SELECT patient.lastNam e, patient.firstNa me, RP.date,
> IIf(PSA.totalPS A<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
> PSA.totalPSA
> FROM patient, RP, PSA
> WHERE patient.patient ID=RP.patient And RP.date Between
> DateValue('1/1/'+[StartingYear]) And DateValue('31/12/'+[EndingYear])
> And patient.patient ID=PSA.patient And PSA.date>RP.dat e
> ORDER BY patient.lastNam e, patient.firstNa me, RP.date, PSA.date;
>
> RP (unifies the unique patient ID and RP surgery date of each patient
> from 2 source tables):
>
> SELECT RPRecord.patien t AS [patient], RPRecord.startD ate AS [date]
> FROM RPRecord
> UNION SELECT RP_Pathology.pa tient, RP_Pathology.pr ocedureDate FROM
> RP_Pathology;
>
>
>
> I'm inclined to blame Microsoft (I wouldn't have touched Access if I
> didn't have to provide a simple interface for the Doctors) for YAFB
> (Yet Another Fatal Bug), and put yet another batshit crazy, waving the
> dead chicken voodoo curse on Bill Gates, but I'm open to the
> possibility that this is my fault (being utterly 'teh n00b'), or that
> this is easily fixable with settings changes or patches. The point
> is, I really need this to work.
>
> Thank you, in advance, for your help[/color]

**Tom Wickerath** · Nov 12 '05, 08:49 PM

Re: Query Causes Hard Crash

Hi Adam,

I think you'd still be better off to replace all the IIF (or Switch) calls with a custom function
that is called from your query. Are you comfortable writing VBA code?

Tom
_______________ _______________ ________

"Adam Louis" <farkallyoupigf arkers@hotmail. com> wrote in message
news:26c90fcd.0 402212303.649a0 7ff@posting.goo gle.com...
Thank you, I'll try that (plus I have a source through which to get
2002 at academic prices). In case you're interested, although this
was is not my final solution, I found, through experimentation , that
(after having switched from IIfs to SWITCHes (again, 'teh noob')),
Access crashes after having 7 cases, plus another 1 for a "TRUE,
elseCase)". Again, thank you for taking the time to answer my
question.
_______________ _______________ ________

"Tom Wickerath" <AOS168RemoveTh isSpamBlock@com cast.net> wrote in message
news:<Y7CdnbgZX 812yqrd4p2dnA@c omcast.com>...[color=blue]
> I just noticed your post on microsoft.publi c.access, dated 2/19 with subject = "Major,[/color]
Crippling[color=blue]
> Bug", where you report the same crash when using the switch function. FYI -- I am able to save
> this query in Access 2000 without experiencing the same crash.
>
> _______________ _______________ _______________ ___
>
> "Tom Wickerath" <AOS168RemoveTh isSpamBlock@com cast.net> wrote in message
> news:ctadnSb_pb CK0qrdRVn-jw@comcast.com. ..
>
> Adam,
>
> This is one UGLY query! Using Access 2002 (SP2), with the Windows 2000 Server operating
> system (512 MB RAM with SP4), I was able to initially save all three of your queries
> without a problem. I then fired up my copy of Access 2000 (SP3) on the same PC. I was
> able to reproduce your hard crash when attempting to save the humongous query. Now for
> the really interesting part: After crashing in Access 2000, I started experiencing the
> same symptom in Access 2002. I had to reboot my machine before it would let me save this
> query again in 2002 without crashing!
>
> Another observation: After saving the file in Access 2002, I opened it in Access 2000. It
> opened ok, but as soon as I clicked on Save, it hard crashed. However, when I reopened
> the same file in Access 2002, the query was still intact and could be saved without a
> problem.
>
> So far, I haven't had any luck in finding a specification relating to how many nested IIF
> functions one can have. I suspect that this mess can be rewritten a WHOLE lot more efficiently
> in a VBA procedure, using an IF...THEN....EL SE IF construct or a SELECT CASE construct.
> Incidentally, in case anyone is interested, both Access 2000 and 2002 list the following
> specification:
>
> Number of characters in a cell in the query design grid 1,024
>
> I created a quickie procedure to count the number of characters and it came back with 955.
>
> Sub test()
> Dim strSQL As String
>
> strSQL = "Huge string from cell in query design pasted here"
> MsgBox Len(strSQL)
>
> End Sub
>
> In Access 2002, if I click into the cell in query design view, and bring up the zoom window[/color]
using[color=blue]
> Shift F2, I get an error message informing me that "The expression you entered exceeds the
> 1,024-character limit for the query design grid." after dismissing the zoom dialog and[/color]
attempting[color=blue]
> to click out of the cell. Very strange. This only happens if I bring up the zoom window.
>
> Also, shortening the table name "AnnualSurgeryA ndFollowupPSA" to less characters has not[/color]
stopped[color=blue]
> the hard crash in Access 2000. If you can send me a private e-mail message (note: return[/color]
address[color=blue]
> needs to be edited to remove 4 words from username) with the actual tables with data, I will[/color]
look[color=blue]
> further into this for you. Of course, you can use fictitious patient names (Mickey Mouse,[/color]
Donald[color=blue]
> Duck, etc.). It would be helpful to send an indication of what results you expect in the
> recordset for various starting and ending years.
>
> Tom
> _______________ _______________ ______
>
> "Adam Louis" <farkallyoupigf arkers@hotmail. com> wrote in message
> news:26c90fcd.0 402181204.42e72 297@posting.goo gle.com...
>
> I would like help resolving this problem. I'm a novice who's been
> hired to query a hospital database and extract useful information,
> available to me only in a dynamically generated, downloadable .mdb.
> The query below query runs correctly and without error, but any
> attempt to save it causes Access to crash without a message, leaving
> the .ldb file. Opening the DB reveals it saved a blank "query1".
> I've upgraded to Jet SP 8, and I'm running Office 2000 on WinXP.
> Access runs fine, other than this. Any insight?
>
> The query (counts the total number of PSA tests taken in 6-month
> intervals of time after RP surgeries):
>
> SELECT
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=0,'',[color=blue]
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=6,'a[color=blue]
> 0-6',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=12,'b[color=blue]
> 6-12',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=18,'c[color=blue]
> 12-18',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=24,'d[color=blue]
> 18-24',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=30,'e[color=blue]
> 24-30',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=36,'f[color=blue]
> 30-36',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=42,'g[color=blue]
> 36-42',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=48,'h[color=blue]
> 42-48',
> 'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM AnnualSurgeryAn dFollowupPSA
> GROUP BY
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=0,'',[color=blue]
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=6,'a[color=blue]
> 0-6',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=12,'b[color=blue]
> 6-12',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=18,'c[color=blue]
> 12-18',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=24,'d[color=blue]
> 18-24',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=30,'e[color=blue]
> 24-30',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=36,'f[color=blue]
> 30-36',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=42,'g[color=blue]
> 36-42',
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow upPSA.PSA.date) <=48,'h[color=blue]
> 42-48',
> 'i 48+')))))))));
>
> Other queries it calls:
>
> AnnualSurgeryAn dFollowupPSA (lists each individual PSA test taken for
> each patient after their RP surgery, where their surgery occurred
> between given years):
>
> SELECT patient.lastNam e, patient.firstNa me, RP.date,
> IIf(PSA.totalPS A<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
> PSA.totalPSA
> FROM patient, RP, PSA
> WHERE patient.patient ID=RP.patient And RP.date Between
> DateValue('1/1/'+[StartingYear]) And DateValue('31/12/'+[EndingYear])
> And patient.patient ID=PSA.patient And PSA.date>RP.dat e
> ORDER BY patient.lastNam e, patient.firstNa me, RP.date, PSA.date;
>
> RP (unifies the unique patient ID and RP surgery date of each patient
> from 2 source tables):
>
> SELECT RPRecord.patien t AS [patient], RPRecord.startD ate AS [date]
> FROM RPRecord
> UNION SELECT RP_Pathology.pa tient, RP_Pathology.pr ocedureDate FROM
> RP_Pathology;
>
>
>
> I'm inclined to blame Microsoft (I wouldn't have touched Access if I
> didn't have to provide a simple interface for the Doctors) for YAFB
> (Yet Another Fatal Bug), and put yet another batshit crazy, waving the
> dead chicken voodoo curse on Bill Gates, but I'm open to the
> possibility that this is my fault (being utterly 'teh n00b'), or that
> this is easily fixable with settings changes or patches. The point
> is, I really need this to work.
>
> Thank you, in advance, for your help[/color]

**Joseph Li** · Nov 12 '05, 08:57 PM

Re: Query Causes Hard Crash

Your query is long which means it might be difficult to debug. I think when
it gets expanded ie. incorporating the other queries it calls, the query
might have become too long internally, ie. over the limit. This could cause
Access not able to save the query.
I have seen this happening with another RDBMS.
For me SQL queries should be short which means if I look at it again in
three months time I should be able to understand at no time. It might be
difficult to shorten your query because the .mdb was not designed by you.

regards
Joseph Li

"Adam Louis" <farkallyoupigf arkers@hotmail. com> wrote in message
news:26c90fcd.0 402181204.42e72 297@posting.goo gle.com...[color=blue]
> I would like help resolving this problem. I'm a novice who's been
> hired to query a hospital database and extract useful information,
> available to me only in a dynamically generated, downloadable .mdb.
> The query below query runs correctly and without error, but any
> attempt to save it causes Access to crash without a message, leaving
> the .ldb file. Opening the DB reveals it saved a blank "query1".
> I've upgraded to Jet SP 8, and I'm running Office 2000 on WinXP.
> Access runs fine, other than this. Any insight?
>
> The query (counts the total number of PSA tests taken in 6-month
> intervals of time after RP surgeries):
>
> SELECT[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =0,'',[color=blue]
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =6,'a[color=blue]
> 0-6',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =12,'b[color=blue]
> 6-12',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =18,'c[color=blue]
> 12-18',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =24,'d[color=blue]
> 18-24',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =30,'e[color=blue]
> 24-30',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =36,'f[color=blue]
> 30-36',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =42,'g[color=blue]
> 36-42',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =48,'h[color=blue]
> 42-48', 'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM
> AnnualSurgeryAn dFollowupPSA
> GROUP BY[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =0,'',[color=blue]
>[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =6,'a[color=blue]
> 0-6',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =12,'b[color=blue]
> 6-12',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =18,'c[color=blue]
> 12-18',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =24,'d[color=blue]
> 18-24',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =30,'e[color=blue]
> 24-30',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =36,'f[color=blue]
> 30-36',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =42,'g[color=blue]
> 36-42',[/color]
IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
pPSA.PSA.date)< =48,'h[color=blue]
> 42-48', 'i 48+')))))))));
>
> Other queries it calls:
>
> AnnualSurgeryAn dFollowupPSA (lists each individual PSA test taken for
> each patient after their RP surgery, where their surgery occurred
> between given years):
>
> SELECT patient.lastNam e, patient.firstNa me, RP.date,
> IIf(PSA.totalPS A<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
> PSA.totalPSA
> FROM patient, RP, PSA
> WHERE patient.patient ID=RP.patient And RP.date Between
> DateValue('1/1/'+[StartingYear]) And DateValue('31/12/'+[EndingYear])
> And patient.patient ID=PSA.patient And PSA.date>RP.dat e
> ORDER BY patient.lastNam e, patient.firstNa me, RP.date, PSA.date;
>
> RP (unifies the unique patient ID and RP surgery date of each patient
> from 2 source tables):
>
> SELECT RPRecord.patien t AS [patient], RPRecord.startD ate AS [date]
> FROM RPRecord
> UNION SELECT RP_Pathology.pa tient, RP_Pathology.pr ocedureDate FROM
> RP_Pathology;
>
>
>
> I'm inclined to blame Microsoft (I wouldn't have touched Access if I
> didn't have to provide a simple interface for the Doctors) for YAFB
> (Yet Another Fatal Bug), and put yet another batshit crazy, waving the
> dead chicken voodoo curse on Bill Gates, but I'm open to the
> possibility that this is my fault (being utterly 'teh n00b'), or that
> this is easily fixable with settings changes or patches. The point
> is, I really need this to work.
>
> Thank you, in advance, for your help[/color]

**Mike Storr** · Nov 12 '05, 08:59 PM

Re: Query Causes Hard Crash

If it is a length issue, then perhaps try aliasing (I think that's a
word) the table names after any FROMS, JOINS etc.

Example ASAFP.RP.Date is a field from below, when you use -
FROM AnnualSurgeryAn dFollowupPSA ASAFP

This allows you to shorten the name of a table into an alias. In your
case, it may cut out a few hundred characters. However, if you do this,
you'll need to do it for all fields, as you may encounter "Too Few
Parameter" errors.

Mike Storr

Veraccess Software

https://www.veraccess.com

Joseph Li wrote:[color=blue]
> Your query is long which means it might be difficult to debug. I think when
> it gets expanded ie. incorporating the other queries it calls, the query
> might have become too long internally, ie. over the limit. This could cause
> Access not able to save the query.
> I have seen this happening with another RDBMS.
> For me SQL queries should be short which means if I look at it again in
> three months time I should be able to understand at no time. It might be
> difficult to shorten your query because the .mdb was not designed by you.
>
> regards
> Joseph Li
>[/color]

[color=blue]
> IIf(DateDiff('m ',AnnualSurgery AndFollowupPSA. RP.date,AnnualS urgeryAndFollow u
> pPSA.PSA.date)< =48,'h
>[color=green]
>>42-48', 'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM
>>AnnualSurgery AndFollowupPSA
>>GROUP BY[/color]
>[/color]

Query Causes Hard Crash

Query Causes Hard Crash

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment