Circular Reference Caused by Query

[jband]

I created a query that pulls information from 2 other queries and everything was going fine until I saved the query. I now get a circular reference error

Here is the query. Any help would be appreciated

SELECT [CashValue Link Query1].Facility,
[CashValue Link Query1].[Financial Class],
[CashValue Link Query1].Date,
[CashValue Link Query1].Date,
[CashValue Link Query1].[Total A/R],
[CashValue Link Query1].Current,
[CashValue Link Query1].[Over 30 Days],
[CashValue Link Query1].[Over 60 Days],
[CashValue Link Query1].[Over 90 Days],
[CashValue Link Query1].[Over 120 Days],
[CashValue Link Query1].[Actual Reimbursement %]
,iif([CashValue Link Query1].[Financial Class] = 'VETERANS ADMIN',([CashValue Link Query1].[Total A/R] * [CashValue Link Query1].[Actual Reimbursement %]), [CashValue Link Query1].[A/R Cash Value]) AS [A/R Value],
iif([CashValue Link Query1].[Financial Class] = 'VETERANS ADMIN',([CashValue Link Query1].[Current] * [CashValue Link Query1].[Actual Reimbursement %]),[CashValue Link Query1].[CUR Cash Value]) AS [CUR Value],
iif([CashValue Link Query1].[Financial Class] = 'VETERANS ADMIN',([CashValue Link Query1].[Over 30 Days] * [CashValue Link Query1].[Actual Reimbursement %]), [CashValue Link Query1].[T30 Cash Value]) AS [T30 Value],
IIf([CashValue Link Query1].[Financial Class]='VETERANS ADMIN',([CashValue Link Query1].[Over 60 Days]*[CashValue Link Query1].[Actual Reimbursement %]),[CashValue].[T60 Cash Value]) AS [T60 Value],
IIf([CashValue Link Query1].[Financial Class]='VETERANS ADMIN',([CashValue Link Query1].[Over 90 Days]*[CashValue Link Query1].[Actual Reimbursement %]),[CashValue].[T90 Cash Value]) AS [T90 Value],
iif([CashValue Link Query1].[Financial Class] = 'VETERANS ADMIN',([CashValue Link Query1].[Over 120 Days] * [CashValue Link Query1].[Actual Reimbursement %]) ,[CashValue Link Query1].[T120 Cash Value]) AS [T120 Value],
IIf([CashValue Link Query1].[Financial Class] In ('VETERANS ADMIN'),([CashValue Link Query1].[Total A/R]-[TCASH Query11].BAL),IIf([CashValue Link Query1].[Financial Class] In ('PRIVATE PAY'),[TA/R],[CashValue Link Query1].PAR)) AS P1AR,
IIf([CashValue Link Query1].[Financial Class] In ('VETERANS ADMIN'),([CashValue Link Query1].[Current]-[TCASH Query11].CUR),IIf([CashValue Link Query1].[Financial Class] In ('PRIVATE PAY'),[Current],[CashValue Link Query1].PCUR)) AS P1CUR,
IIf([CashValue Link Query1].[Financial Class] In ('VETERANS ADMIN'),([CashValue Link Query1].[Over 30 Days]-[TCASH Query11].O30),IIf([CashValue Link Query1].[Financial Class] In ('PRIVATE PAY'),[Total 30],[CashValue Link Query1].P30)) AS PA30,
IIf([CashValue Link Query1].[Financial Class] In ('VETERANS ADMIN'),([CashValue Link Query1].[Over 60 Days]-[TCASH Query11].O60),IIf([CashValue Link Query1].[Financial Class] In ('PRIVATE PAY'),[Total 60],[CashValue Link Query1].P60)) AS PA60,
IIf([CashValue Link Query1].[Financial Class] In ('VETERANS ADMIN'),([CashValue Link Query1].[Over 90 Days]-[TCASH Query11].O90),IIf([CashValue Link Query1].[Financial Class] In ('PRIVATE PAY'),[Total 90],[CashValue Link Query1].P90)) AS PA90,
IIf([CashValue Link Query1].[Financial Class] In ('VETERANS ADMIN'),([CashValue Link Query1].[Over 120 Days]-[TCASH Query11].O120),IIf([CashValue Link Query1].[Financial Class] In ('PRIVATE PAY'),[Total 120],[CashValue Link Query1].P120)) AS PA120,
[CashValue Link Query1].[TA/R],
[CashValue Link Query1].[Total CUR],
[CashValue Link Query1].[Total 30],
[CashValue Link Query1].[Total 60],
[CashValue Link Query1].[Total 90],
[CashValue Link Query1].[Total 120]
FROM [CashValue Link Query1] RIGHT JOIN [TCASH Query11] ON ([CashValue Link Query1].Date=[TCASH Query11].ODATE) AND ([CashValue Link Query1].[Financial Class]=[TCASH Query11].CODE) AND ([CashValue Link Query1].Facility=[TCASH Query11].FCDESC);

 

[Pat Hartman]

Thank you for attempting to make the query more readable. Most people don't bother. It didn't help me though. Your names include so many spaces and special characters which forces encapsulation of the offending name by square brackets that they are hard to "see". I did notice that you selected .Date twice although that won't give you a circular reference error. Usually you get that error if you assign a name to a calculated field that duplicates an existing field. Since we can't see the actual SQL for the two source queries, you'll need to check them but that's what to look for.

You also switched part way through the query from using = to using In(). Since In() is SQL syntax, I'm not sure that it actually works inside an IIf().

PS - Date is a reserved word and if you are not careful, you could end up with Access or SQL assuming you mean Date() rather than your own column. Current is also a reserved word but not a function so is less likely to cause problems except in VBA.

Better practice is to use the letters a-z, A-Z, numbers 0-9, and the underscore only when forming column and object names. To avoid using reserved words, make compound words such as SaleDate or CurrentStatus. It's easier than memorizing a list containing hundreds of SQL, VBA, and Access reserved words.