Major, Crippling Bug

[Adam Louis]

I'm running Access 2000 on WindowsXP, with Jet 4.0 SP 8, and some of
my queries will run, and produce correct results, but Access hard
crashes to the desktop on attempting to save, leaving behind the .ldb
file and nothing else. I've tried recreating the queries, I've tried
recreating the database, and nothing seems to work. This is a major,
crippling bug that makes the software unusable.

A sample of an offending query:

SELECT [patient].[lastName], [patient].[firstName], [RP].[date],
IIf([PSA].[totalPSA]<=[PSACutoff],'Yes','No') AS ZeroPSA,
[PSA].[date], [PSA].[totalPSA],
SWITCH(DateDiff('m',RP.date,PSA.date)<0,'',
DateDiff('m',RP.date,PSA.date)<6,'a 0-6',
DateDiff('m',RP.date,PSA.date)<12,'b 6-12',
DateDiff('m',RP.date,PSA.date)<18,'c 12-18',
DateDiff('m',RP.date,PSA.date)<24,'d 18-24',
DateDiff('m',RP.date,PSA.date)<30,'e 24-30',
DateDiff('m',RP.date,PSA.date)<36,'f 30-36',
DateDiff('m',RP.date,PSA.date)<42,'g 36-42',
DateDiff('m',RP.date,PSA.date)<48,'h 42-48',TRUE, 'i 48+') AS
DateInterval
FROM patient, RP, PSA
WHERE [patient].[patientID]=[RP].[patient] And [RP].[date] Between
DateValue('1/1/'+[StartingYear]) And DateValue('31/12/'+[EndingYear])
And [patient].[patientID]=[PSA].[patient] And [PSA].[date]>[RP].[date]
ORDER BY [patient].[lastName], [patient].[firstName], [RP].[date],
[PSA].[date];

 

[Allen Browne]

Hi Adam.

See:
ACC2000: Access Quits Unexpectedly When You Run a Query
at:
http://support.microsoft.com/?id=322794
This is one of the many issues with Name AutoCorrect. More at:
http://allenbrowne.com/bug-03.html

The unusual aspect of the query example you posted is that there are no
joins between the 3 tables. Would it be possible to replace some of the
WHERE clause such as:
[patient].[patientID]=[RP].[patient]
and
[patient].[patientID]=[PSA].[patient]
with inner joins? While that's not an excuse for Access to crash, it would
greatly simply its task.

Less importantly, I would probably have used DateSerial([StartingYear],1,1)
and DateSerial([EndingYear],1,1).

 

[Adam Louis]

Thank you for the help, I'll check that out, immediately. 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.

Hi Adam.

See:
ACC2000: Access Quits Unexpectedly When You Run a Query
at:
http://support.microsoft.com/?id=322794
This is one of the many issues with Name AutoCorrect. More at:
http://allenbrowne.com/bug-03.html

The unusual aspect of the query example you posted is that there are no
joins between the 3 tables. Would it be possible to replace some of the
WHERE clause such as:
[patient].[patientID]=[RP].[patient]
and
[patient].[patientID]=[PSA].[patient]
with inner joins? While that's not an excuse for Access to crash, it would
greatly simply its task.

Less importantly, I would probably have used DateSerial([StartingYear],1,1)
and DateSerial([EndingYear],1,1).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm running Access 2000 on WindowsXP, with Jet 4.0 SP 8, and some of
my queries will run, and produce correct results, but Access hard
crashes to the desktop on attempting to save, leaving behind the .ldb
file and nothing else. I've tried recreating the queries, I've tried
recreating the database, and nothing seems to work. This is a major,
crippling bug that makes the software unusable.

A sample of an offending query:

SELECT [patient].[lastName], [patient].[firstName], [RP].[date],
IIf([PSA].[totalPSA]<=[PSACutoff],'Yes','No') AS ZeroPSA,
[PSA].[date], [PSA].[totalPSA],
SWITCH(DateDiff('m',RP.date,PSA.date)<0,'',
DateDiff('m',RP.date,PSA.date)<6,'a 0-6',
DateDiff('m',RP.date,PSA.date)<12,'b 6-12',
DateDiff('m',RP.date,PSA.date)<18,'c 12-18',
DateDiff('m',RP.date,PSA.date)<24,'d 18-24',
DateDiff('m',RP.date,PSA.date)<30,'e 24-30',
DateDiff('m',RP.date,PSA.date)<36,'f 30-36',
DateDiff('m',RP.date,PSA.date)<42,'g 36-42',
DateDiff('m',RP.date,PSA.date)<48,'h 42-48',TRUE, 'i 48+') AS
DateInterval
FROM patient, RP, PSA
WHERE [patient].[patientID]=[RP].[patient] And [RP].[date] Between
DateValue('1/1/'+[StartingYear]) And DateValue('31/12/'+[EndingYear])
And [patient].[patientID]=[PSA].[patient] And [PSA].[date]>[RP].[date]
ORDER BY [patient].[lastName], [patient].[firstName], [RP].[date],
[PSA].[date];