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',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a
0-6', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b
6-12', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c
12-18', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d
18-24', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e
24-30', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f
30-36', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g
36-42', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h
42-48', 'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM
AnnualSurgeryAndFollowupPSA
GROUP BY IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a
0-6', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b
6-12', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c
12-18', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d
18-24', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e
24-30', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f
30-36', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g
36-42', IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h
42-48', 'i 48+')))))))));
Other queries it calls:
AnnualSurgeryAndFollowupPSA (lists each individual PSA test taken for
each patient after their RP surgery, where their surgery occurred
between given years):
SELECT patient.lastName, patient.firstName, RP.date,
IIf(PSA.totalPSA<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
PSA.totalPSA
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;
RP (unifies the unique patient ID and RP surgery date of each patient
from 2 source tables):
SELECT RPRecord.patient AS [patient], RPRecord.startDate AS [date]
FROM RPRecord
UNION SELECT RP_Pathology.patient, RP_Pathology.procedureDate 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
8  6281 
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....ELSE 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 "AnnualSurgeryAndFollowupPSA" 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" <fa******************@hotmail.com> wrote in message
news:26**************************@posting.google.c om...
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',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a
0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b
6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c
12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d
18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e
24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f
30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g
36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h
42-48',
'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM AnnualSurgeryAndFollowupPSA
GROUP BY
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a
0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b
6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c
12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d
18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e
24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f
30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g
36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h
42-48',
'i 48+')))))))));
Other queries it calls:
AnnualSurgeryAndFollowupPSA (lists each individual PSA test taken for
each patient after their RP surgery, where their surgery occurred
between given years):
SELECT patient.lastName, patient.firstName, RP.date,
IIf(PSA.totalPSA<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
PSA.totalPSA
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;
RP (unifies the unique patient ID and RP surgery date of each patient
from 2 source tables):
SELECT RPRecord.patient AS [patient], RPRecord.startDate AS [date]
FROM RPRecord
UNION SELECT RP_Pathology.patient, RP_Pathology.procedureDate 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
I just noticed your post on microsoft.public.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" <AO***********************@comcast.net> wrote in message
news:ct********************@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....ELSE 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 "AnnualSurgeryAndFollowupPSA" 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" <fa******************@hotmail.com> wrote in message
news:26**************************@posting.google.c om...
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',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a
0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b
6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c
12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d
18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e
24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f
30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g
36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h
42-48',
'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM AnnualSurgeryAndFollowupPSA
GROUP BY
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a
0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b
6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c
12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d
18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e
24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f
30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g
36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h
42-48',
'i 48+')))))))));
Other queries it calls:
AnnualSurgeryAndFollowupPSA (lists each individual PSA test taken for
each patient after their RP surgery, where their surgery occurred
between given years):
SELECT patient.lastName, patient.firstName, RP.date,
IIf(PSA.totalPSA<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
PSA.totalPSA
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;
RP (unifies the unique patient ID and RP surgery date of each patient
from 2 source tables):
SELECT RPRecord.patient AS [patient], RPRecord.startDate AS [date]
FROM RPRecord
UNION SELECT RP_Pathology.patient, RP_Pathology.procedureDate 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 fa******************@hotmail.com (Adam Louis) wrote in
news:26**************************@posting.google.c om: 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.
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 fldTransactionID = parBlah"
CurrentProject.Connection.Execute _
"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)
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 <Mi************@Invalid.Com> wrote in message news:<Xn*******************@130.133.1.4>... fa******************@hotmail.com (Adam Louis) wrote in
news:26**************************@posting.google.c om:
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.
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 fldTransactionID = parBlah"
CurrentProject.Connection.Execute _
"CREATE PROCEDURE qryBlah2 (parBlah Integer) AS " & sql
If no Parameters then (parBlah Integer) is redundant.
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" <AO***********************@comcast.net> wrote in message news:<Y7********************@comcast.com>... I just noticed your post on microsoft.public.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" <AO***********************@comcast.net> wrote in message
news:ct********************@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....ELSE 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 "AnnualSurgeryAndFollowupPSA" 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" <fa******************@hotmail.com> wrote in message
news:26**************************@posting.google.c om...
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',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a
0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b
6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c
12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d
18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e
24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f
30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g
36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h
42-48',
'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM AnnualSurgeryAndFollowupPSA
GROUP BY
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a
0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b
6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c
12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d
18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e
24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f
30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g
36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h
42-48',
'i 48+')))))))));
Other queries it calls:
AnnualSurgeryAndFollowupPSA (lists each individual PSA test taken for
each patient after their RP surgery, where their surgery occurred
between given years):
SELECT patient.lastName, patient.firstName, RP.date,
IIf(PSA.totalPSA<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
PSA.totalPSA
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;
RP (unifies the unique patient ID and RP surgery date of each patient
from 2 source tables):
SELECT RPRecord.patient AS [patient], RPRecord.startDate AS [date]
FROM RPRecord
UNION SELECT RP_Pathology.patient, RP_Pathology.procedureDate 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
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" <fa******************@hotmail.com> wrote in message
news:26**************************@posting.google.c om...
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" <AO***********************@comcast.net> wrote in message
news:<Y7********************@comcast.com>... I just noticed your post on microsoft.public.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" <AO***********************@comcast.net> wrote in message
news:ct********************@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....ELSE 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 "AnnualSurgeryAndFollowupPSA" 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" <fa******************@hotmail.com> wrote in message
news:26**************************@posting.google.c om...
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',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a 0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b 6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c 12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d 18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e 24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f 30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g 36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h 42-48',
'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM AnnualSurgeryAndFollowupPSA
GROUP BY
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=6,'a 0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=12,'b 6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=18,'c 12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=24,'d 18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=30,'e 24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=36,'f 30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=42,'g 36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowupPSA.PSA.date)<=48,'h 42-48',
'i 48+')))))))));
Other queries it calls:
AnnualSurgeryAndFollowupPSA (lists each individual PSA test taken for
each patient after their RP surgery, where their surgery occurred
between given years):
SELECT patient.lastName, patient.firstName, RP.date,
IIf(PSA.totalPSA<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
PSA.totalPSA
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;
RP (unifies the unique patient ID and RP surgery date of each patient
from 2 source tables):
SELECT RPRecord.patient AS [patient], RPRecord.startDate AS [date]
FROM RPRecord
UNION SELECT RP_Pathology.patient, RP_Pathology.procedureDate 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
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" <fa******************@hotmail.com> wrote in message
news:26**************************@posting.google.c om... 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',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=6,'a 0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=12,'b 6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=18,'c 12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=24,'d 18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=30,'e 24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=36,'f 30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=42,'g 36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=48,'h 42-48', 'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM
AnnualSurgeryAndFollowupPSA
GROUP BY
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=0,'',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=6,'a 0-6',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=12,'b 6-12',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=18,'c 12-18',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=24,'d 18-24',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=30,'e 24-30',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=36,'f 30-36',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=42,'g 36-42',
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=48,'h 42-48', 'i 48+')))))))));
Other queries it calls:
AnnualSurgeryAndFollowupPSA (lists each individual PSA test taken for
each patient after their RP surgery, where their surgery occurred
between given years):
SELECT patient.lastName, patient.firstName, RP.date,
IIf(PSA.totalPSA<=[PSACutoff],'Yes','No') AS ZeroPSA, PSA.date,
PSA.totalPSA
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;
RP (unifies the unique patient ID and RP surgery date of each patient
from 2 source tables):
SELECT RPRecord.patient AS [patient], RPRecord.startDate AS [date]
FROM RPRecord
UNION SELECT RP_Pathology.patient, RP_Pathology.procedureDate 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
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 AnnualSurgeryAndFollowupPSA 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 www.veraccess.com
Joseph Li wrote: 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
IIf(DateDiff('m',AnnualSurgeryAndFollowupPSA.RP.da te,AnnualSurgeryAndFollowu
pPSA.PSA.date)<=48,'h
42-48', 'i 48+'))))))))) AS [DateInterval], count(*) AS [Count] FROM
AnnualSurgeryAndFollowupPSA
GROUP BY This discussion thread is closed Replies have been disabled for this discussion. Similar topics
10 posts
views
Thread by Alex Vinokur |
last post: by
|
1 post
views
Thread by John C |
last post: by
|
18 posts
views
Thread by Simula |
last post: by
|
3 posts
views
Thread by Ramalingam Chandran |
last post: by
|
reply
views
Thread by Andrew W |
last post: by
|
9 posts
views
Thread by badboybrown |
last post: by
|
5 posts
views
Thread by elitecodex |
last post: by
|
3 posts
views
Thread by Typehigh |
last post: by
|
12 posts
views
Thread by si_owen |
last post: by
| | | | | | | | | | | | | | | | | | | |
