Query for <> "'#Error"

[Der_Winzerkoenig]

Hello,

I have written a query in Access which may at some points return #Error. This is fine as long as one out of three fields potentially returning #Error return a numeric value.

I was wondering if I could simply exclude these entries with a simple line in the "Criteria:" row in the query design view.





I have so far tried using

IsNumeric([qryKCCQ23]![KCCQ12_OS]) and <> IsError([qryKCCQ23]![KCCQ12_OS])

on the columns, this however resulted in a Data type mismatch warning.

Do you have any ideas how to get to the expected results?

Thanks in advance!

P.s. I am aware that there are most likely more subtle ways to work around the problem. This is not about performce, I just want to spare myself the effort of going through the results manually

[CJ_London]

You can’t in a query but you might be able to in a vba function

Much better to understand why you get the error and work around it. E.g. if the error is the result of a null value modify your sql to avoid it using the nz function or excluding those records with the criteria

[June7]

What calculation is producing the #Error? Are these fields returning value from VBA custom function?

[Der_Winzerkoenig]

Ok let's do it the proper way then

So there is a primary predefined query, patient encounters. Patients may give their feedback at each encounter by using three different questionnaires (KCCQ23, KCCQ12, EQ-5D) which are matched to the corresponding encounter by PatientId(= AmyId) and Date.
There are three queries which do some calculations (i.e. performance scores) on each one. These are the three queries you see in my previous post for each of the different questionnaires.

The query i now need to run is basically all patient encounters where one or multiple questionnaires have been recorded with the corresponding calculations.

This is the SQL code generated by Access (for a simplified version of the query with only two questionnaires):

SELECT qryStatusPerPatientAll.Date, [qryEQ-5D].[Overall Score], qryKCCQ12.KCCQ12_OS
FROM (qryStatusPerPatientAll LEFT JOIN [qryEQ-5D] ON (qryStatusPerPatientAll.Date = [qryEQ-5D].Date) AND (qryStatusPerPatientAll.AmyID = [qryEQ-5D].PatientId)) LEFT JOIN qryKCCQ12 ON (qryStatusPerPatientAll.AmyID = qryKCCQ12.PatientId) AND (qryStatusPerPatientAll.Date = qryKCCQ12.Date)
ORDER BY qryStatusPerPatientAll.Date;

What would be better solution for this task? Thanks in advance!

[CJ_London]

There are three queries which do some calculations (i.e. performance scores) on each one. These are the three queries you see in my previous post for each of the different questionnaires.

since it it these queries generating the #error, these are the ones we need to see the sql.

Also need to know whether your tables are local tables or linked - and if linked then to what - another access db, a text or excel file, sql server or something else?

Also provide some example data to illustrate where you are getting the error

[Der_Winzerkoenig]

Hey,

so this is the SQL-code of the three queries in question

SELECT [tblEQ-5D].*, EQ5D([Mobility],[Autonomy],[Activity],[Pain],[Anxiety]) AS [Overall Score]FROM [tblEQ-5D];

All three of them do basically the same, just on data formatted differently. EQ-5D() is a function locally defined via a VBA Module, and works fine whenever there is data. I have also implented internal checking of the parameters provided for = 0 and IsNull(parameter):

Public Function EQ5D( _
    Mobility As Integer, _
    SelfCare As Integer, _
    UsualActivities As Integer, _
    PainDiscomfort As Integer, _
    AnxietyDepression As Integer _
    ) As Variant
        
       Dim Score As Single
       
       Score = 1
       
       If Mobility = 0 Or SelfCare = 0 Or UsualActivities = 0 Or PainDiscomfort = 0 Or AnxietyDepression = 0 Then Exit Function
       If IsNull(Mobility) Or IsNull(SelfCare) Or IsNull(UsualActivities) Or IsNull(PainDiscomfort) Or IsNull(AnxietyDepression) Then Exit Function
       'do stuff
       EQ5D = Math.Round(Score, 3)
              
    End Function

All tables, queries etc are executed locally within the Access file.

An #Error is thrown wherever there is a patient encounter (the primary table) with no matching entry for the questionnaires. However, with the SQL code posted previously, as every single encounter is listed, the questionnaire query will get called even if there is no questionnaire.

If it was only for one distinct type of questionnaire, I could simply use INNER JOIN to display only matching entries. However, I need to display every encounter with at least one type of questionnaire leading to the situation where I needed to use several LEFT JOIN clauses. This however resulted in #Errors where no matching questionnaire is found.

I hope this explains the issue better, I am glad to clarify where needed

[CJ_London]

so basically you are saying the EQ5D function produces the error - which I'm guessing is due to the function exiting without an assigned value

perhaps put

EQ5D = 1

after

Score = 1

you can also simplify

If Mobility = 0 Or SelfCare = 0 Or UsualActivities = 0 Or PainDiscomfort = 0 Or AnxietyDepression = 0 Then Exit Function
If IsNull(Mobility) Or IsNull(SelfCare) Or IsNull(UsualActivities) Or IsNull(PainDiscomfort) Or IsNull(AnxietyDepression) Then Exit Function

to

If (nz(Mobility,0)+nz(SelfCare,0)+nz(UsualActivities, 0)+nz(PainDiscomfortm0)+nz(AnxietyDepression,0))=0 Then Exit Function

[Der_Winzerkoenig]

I don't think its the function which produces the error. I have tried to adapt the function to simply return 1 and the #Error keeps reappearing. It seems as if the function is not even called, as a inserting a break point does not halt the query until some questionnaires are encountered.

My guess would be that this is about the way in which the queries gets called with LEFT JOIN, which will result in some patient encounters with no matching questionnaire to run the function on. It is exactly those encounters which I need to exclude from the results.

Thank you for your effort in helping me out!

[CJ_London]

show me where your function returns 1 when this line executes

If Mobility = 0 Or SelfCare = 0 Or UsualActivities = 0 Or PainDiscomfort = 0 Or AnxietyDepression = 0 Then Exit Function

and all values are 0

As far as I can see it would return null since you have specified it as a variant

[June7]

Maybe time to provide your db for analysis. Follow instructions at bottom of my post.

[Der_Winzerkoenig]

Hi,

thank you for your replies! Sorry for the delayed response, I have been away for 3 days. I will provide a sample file later today

[Der_Winzerkoenig]

Hi,

sorry for the delay! After talking to a colleague of mine, we could finally find a solution to run the query smoothly.

We used a WHERE EXISTS-clause to select and run the function only on entries where we knew there would be data, and therefor avoided the #Error result.

Thank you for your input and replies