Chart Error: Expression is typed incorrectly, or is too complex to be evaluated (1 Viewer)

[naa123]

Hi, I created a chart in Access based on a query. The query is as follows:

SELECT [RCADS-C].[Client ID], [RCADS-C].[Appointment date], [RCADS-C].[RCADS Separation Anxiety], [RCADS-C].[RCADS Generalised Anxiety], [RCADS-C].[RCADS Panic Disorder], [RCADS-C].[RCADS Social Phobia], [RCADS-C].[RCADS OCD], [RCADS-C].[RCADS Depression],
DLookUp("[Age]","Demographic Information","[Client ID] = '" & [RCADS-C].[Client ID] & "' AND [UserID] = " & [RCADS-C].[UserID]) AS Age,
DLookUp("[Gender]","Demographic Information","[Client ID] = '" & [RCADS-C].[Client ID] & "' AND [UserID] = " & [RCADS-C].[UserID]) AS Gender,
Switch([Gender]="Female (including trans women)",2,[Gender]="Male (including trans man)",1,[Gender]="Non-binary" Or [Gender]="Other (not listed)",3,[Gender]="Not known",4) AS [Gender code],
Age-6 AS Grade,
IIf(Not IsNull([Grade]) And Not IsNull([Gender code]) And Not IsNull([RCADS Separation Anxiety]),CalculateSADT([Grade],[Gender code],[RCADS Separation Anxiety]),"") AS [SAD T],
IIf(Not IsNull([Grade]) And Not IsNull([Gender code]) And Not IsNull([RCADS Generalised Anxiety]),CalculateGADT([Grade],[Gender code],[RCADS Generalised Anxiety]),"") AS [GAD T],
IIf(Not IsNull([Grade]) And Not IsNull([Gender code]) And Not IsNull([RCADS Panic Disorder]),CalculatePDT([Grade],[Gender code],[RCADS Panic Disorder]),"") AS [PD T],
IIf(Not IsNull([Grade]) And Not IsNull([Gender code]) And Not IsNull([RCADS Social Phobia]),CalculateSOCT([Grade],[Gender code],[RCADS Social Phobia]),"") AS [SOC T],
IIf(Not IsNull([Grade]) And Not IsNull([Gender code]) And Not IsNull([RCADS OCD]),CalculateOCDT([Grade],[Gender code],[RCADS OCD]),"") AS [OCD T],
IIf(Not IsNull([Grade]) And Not IsNull([Gender code]) And Not IsNull([RCADS Depression]),CalculateMDDT([Grade],[Gender code],[RCADS Depression]),"") AS [MDD T],
[RCADS-C].UserID
FROM [RCADS-C]
WHERE ((([RCADS-C].[Client ID]) Like "*" & Forms!ORS!cbo_Source5 & "*") And (([RCADS-C].UserID)=TempVars!TempUserID));

This query is taking data from the RCADS-C table associated with the current user (through the User ID), and calculating the standardized T score based on the raw RCADS score, gender and grade.

I have then created a chart based on the T score fields in this query with the row source:

SELECT RCADS.[Appointment date], Avg(RCADS.[SAD T]) AS [AvgOfSAD T], Avg(RCADS.[GAD T]) AS [AvgOfGAD T], Avg(RCADS.[PD T]) AS [AvgOfPD T], Avg(RCADS.[SOC T]) AS [AvgOfSOC T], Avg(RCADS.[OCD T]) AS [AvgOfOCD T], Avg(RCADS.[MDD T]) AS [AvgOfMDD T]
FROM RCADS
GROUP BY RCADS.[Appointment date]
ORDER BY RCADS.[Appointment date];

However from this, I am receiving an error if any of the T score fields (SAD T, GAD T, PD T, SOC T, OCD T or MDD T) are blank. It is very likely that some of these fields will be blank as the user will not always collect data for every RCADS subscale, they might just take one or two, e.g. RCADS Generalised Anxiety and RCADS Depression.

Does anyone know what is triggering the error: "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expressions by assigning parts of the expression to variables."? And what I can do to solve this when there are blank fields?

Many thanks

 

[arnelgp]

you can simplify your query string, by
1. removing the DLookup() function and instead use Join.
2. remove the IIF() function and instead just pass the parameters whether null or not and Let the
function use Ifs for those null values.

 

[CJ_London]

Does anyone know what is triggering the error: "This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expressions by assigning parts of the expression to variables."? And what I can do to solve this when there are blank fields?

how about
Try simplifying the expressions by assigning parts of the expression to variables

 

[naa123]

you can simplify your query string, by
1. removing the DLookup() function and instead use Join.
2. remove the IIF() function and instead just pass the parameters whether null or not and Let the
function use Ifs for those null values.

Thank you very much for your help - I have done this and it has solved the issue.

 

[naa123]

how about
Try simplifying the expressions by assigning parts of the expression to variables

This is a very unhelpful reply. If I knew how to simplify the expressions by assigning parts of the expression to variables, I would have tried that. I am a beginner with Access, hence why I am here asking for help.

 

[The_Doc_Man]

If you are a beginner, is a JOIN something that you aren't sure about? If so, here is a hint. Your DLookup references another table that shares some field called [Client ID] with your RCADS table. Therefore, one option you could use is

SELECT ..... , DI.Age, DI.Gender, .... 
FROM [RCADS-C] INNER JOIN [Demographic Information] AS DI ON [RCADS-C].[Client ID] = [Demographic Information].[Client ID] ...

This does a virtual JOIN between the two tables (only for the duration of the query) so that you can directly reference the field rather than having to look it up. It is a lot faster and more efficient. You already qualified your SELECT fields with [RCADS-C] so you can use the other table, give it an ALIAS name with the AS clause, and then directly reference the demographic information by qualifying the field names to show where they originate.

 

[Gasman]

This is a very unhelpful reply. If I knew how to simplify the expressions by assigning parts of the expression to variables, I would have tried that. I am a beginner with Access, hence why I am here asking for help.

You would be surprised what new users to Access have not tried.
Half the time we get told 'I get an error' and they cannot even think to say what the error is?
So suggesting breaking it down bit by bit, is a viable method.
Anything with ID in it to me is numeric and would not use or need single quotes?
Putting criteria into variables is a standard debugging technique. You can then Debug.Print the variable to see if is correct

I often use
StrSelect
StrWhere
StrGroupBy etc

 

[The_Doc_Man]

This is a very unhelpful reply. If I knew how to simplify the expressions by assigning parts of the expression to variables, I would have tried that. I am a beginner with Access, hence why I am here asking for help.

And you WERE given help but you didn't realize it. CJ is one of our best members and I guarantee he is not intrinsically a rude person. But sometimes our answers are so abrupt that they might seem a bit cold. Don't forget that we are all volunteers here. You pay nothing for our help, unlike some organizations that charge you by the minute. A relatively short answer is better for us because we can service more questions, so we often give an answer that sometimes doesn't include a lot of details. If we give short answers and you needed a long answer, there is no need to go into personalities. Just say, "I didn't understand that answer. What did you mean by it?"

CJ's answer, by the way, is one of the tried-and-true methods called "divide and conquer." If you break apart a troublesome query into its component parts, you often can find the problem by inspection. It is a variation of that old phrase "can't see the forest for the trees."

 

[Pat Hartman]

The problem is that your IIf() statements are using "" as the false value rather than null. That means that when you are trying to do math later on, you are working with a string and you can't do math on strings.

Avg(RCADS.[SAD T]) AS [AvgOfSAD T]

will ignore null values for [SAD T] but not "" which is a string.

start by changing the false path in the If()s from "" to Null.

If that doesn't work, you need to fix the Avg()'s to convert "" to Null.

However, since I don't have any idea what any of this is about, you need to understand that you might want 0 to be returned in the false path rather than null depending on what you are actually calculating.

Consider this:

Avg(3, 0, 3) = 2
BUT
Avg (3, null, 3) = 3

Null is ignored by the Avg() but 0 counts.