Hi, I created a chart in Access based on a query. The query is as follows:
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:
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
Code:
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:
Code:
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