Ms Access Circular Reference Error
was working in Access the other day, when I built a query with the expression you see here in this screenshot. . I'm passing the [Profile Number] field through the RIGHT function to pad it with 10 zeros. When I try to run the query, I get this error telling me that a Circular Reference is caused by the alias I'm using. Hmm….I didn't even know you could get a Circular Reference in Access. . Apparently, Access doesn't like the fact that I'm referencing the [Profile Number] field then using the same field name as the alias. . Microsoft Support tells me to: "Change the name of the alias or label used by the calculated expression or let Microsoft Access automatically assign an alias or label." . But I like my alias. I want my alias. I shouldn't have to change my alias for anyone! No Microsoft - I'll fix this problem my way.    . Instead of changing my alias, I simply added a reference to the table name in my expression. . This sufficiently distinguishes the field name from the alias. No more Circular Reference error! Access, Tips and Tricks ← Irregular Color Banding on Double Click Traveling Tip for the Memory Challenged → 7 thoughts on “Circular Reference in an Access Query” Venus February 24, 2012 at 7:26 pm Holy alias Batman! I've came across this from time to time. A week or two ago I couldn't remember how I worked it out the last time so I decided to put the project aside for a while. Thanks for the answer; you are the pigglety-wigglety bomb. Daniel Dewey March 13, 2012 at 9:46 pm Yup, those aliases do make it easy/hard sometimes. This may be more a question for an entire blog post, but when to use [table].[field], and when to use [table]![field]. I've used both in my queries, but never know what's more appropriate. Ed November 5, 2012 at 9:26 pm Thanks, this was an annoyance for me and your solution fixed it! tran.mish@gmail.com April 10, 2013 at 5:20 pm Wow, I love you datapig! So simple but I couldn't thought of it Shahab December 15, 2015 at 10:55 am Another quick solution would be to qualify the field name with the table name, in the query. Like [Table Name].[Field Name] AS [Field Name] someotherdatapig January 11, 2016 at 5:15 am Gee thanks mister! I'm sure that was hours you've sav
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Access sql query Circular Reference error up vote 1 down vote favorite http://datapigtechnologies.com/blog/index.php/circular-reference-in-an-access-query/ I'm creating a sql select query for an access database, and receiving a circular reference error, because my alias name is the same as a column name in my expression. Here is the fragment of my query: switch([CULET]='N','NONE', [CULET]='S', 'SMALL',[CULET]='VS','VERY SMALL', [CULET]='SL',' ',[CULET]='MD',' ') AS [Culet] This specific alias name is part of the requirements for the output. Is there any way to get around this without changing the http://stackoverflow.com/questions/2647251/access-sql-query-circular-reference-error alias name? TIA sql ms-access alias circular-reference share|improve this question asked Apr 15 '10 at 16:43 user228058 225416 add a comment| 2 Answers 2 active oldest votes up vote 5 down vote accepted Add the table name/alias to the field. Something like Switch([Table1.CULET]='N','NONE',[Table1.CULET]='S','SMALL',[Table1.CULET]='VS','VERY SMALL',[Table1.CULET]='SL',' ',[Table1.CULET]='MD',' ') AS Culet share|improve this answer answered Apr 15 '10 at 16:47 Adriaan Stander 107k11180220 1 thanks, that worked! –user228058 Apr 15 '10 at 16:49 add a comment| up vote 0 down vote Sometimes, this error can occur when the circular reference is actually within one of the queries used as the basis of the query you are working on (even when the basis query actually doesn't raise any errors, itself!) In this case, you should follow the recommendations in Microsoft's KB (knowledge-base) article 97526, for the basis query. share|improve this answer answered Jun 16 '15 at 15:58 Matthew Slyman 1007 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a guest Name Email discard By posting your answer, you agree to the privacy policy and terms of service. Not th
Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rate Thread Display Modes http://www.access-programmers.co.uk/forums/showthread.php?t=223117 03-15-2012, 04:44 AM #1 chrisleng Newly Registered User Join Date: Feb 2010 Posts: 114 Thanks: 12 Thanked 1 Time in 1 Post Updated: Allowing a Circular reference UPDATE: I have made this https://www.experts-exchange.com/questions/24628898/Microsoft-Access-VB-Circular-Reference-Error.html work by making a second query and linking it to the first, it ran once and as far as I could tell gave the correct results down levels!! However once I saved it and tried ms access to run it again it gave a circular reference error, why did it do this when it has already worked once?! ---------------------------------- Hi, I need to use a circular reference in a query, I can do it currently with an update query [below] where I reference the field being updated in the update statement but I dont want to have to make a temp table to do it (at ms access circular some point a single instance of the temp table will exceed access's 2gb limit!) Code: UPDATE sndbasis LEFT JOIN sndbasis AS sndbasis_1 ON (sndbasis.cldate = sndbasis_1.cldate) AND (sndbasis.ijn = sndbasis_1.ijn) AND (sndbasis.pa_irn = sndbasis_1.irn) SET sndbasis.requirement = IIf([sndbasis].[pa_irn]<>"",[sndbasis].[multiple]*[sndbasis_1].[requirement],[sndbasis].[requirement]); Above is the update query, how can I do this with a select query? Last edited by chrisleng; 03-15-2012 at 08:46 AM. chrisleng View Public Profile Find More Posts by chrisleng 03-15-2012, 04:51 AM #2 chrisleng Newly Registered User Join Date: Feb 2010 Posts: 114 Thanks: 12 Thanked 1 Time in 1 Post Re: Allowing a Circular reference I think this is what I need (it describes my problem): http://msdn.microsoft.com/en-us/library/ms186243.aspx but I'm afraid I dont really understand it, I'm also wondering if I can somehow trick the query into running on itself or something, any ideas? chrisleng View Public Profile Find More Posts by chrisleng « Previous Thread | Next Thread » Thread Tools Show Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Rate This Thread Excellent Good Average Bad Terrible Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Acces
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Microsoft Access VB - Circular Reference Error Want to Advertise Here? Solved Microsoft Access VB - Circular Reference Error Posted on 2009-08-05 Visual Basic Classic MS Access 2 Verified Solutions 5 Comments 564 Views Last Modified: 2013-12-26 HI - Hope someone can help. I am trying to have a query filtered (by Business Unit) then exported. When I run this i get the following error: Run-Time error '3102' Circular Reference caused by 'EmployeeDetail_List' thanks in advance for any help you can provide. Private Sub exportrosters_Click() On Error GoTo Err_exportrosters_Click Dim rst As DAO.Recordset Dim Path As String Dim StrBU As String Dim intDcode As String Dim strQry As String Dim StrExt As String Dim strFile As String Dim qDef As DAO.QueryDef Err.Clear On Error Resume Next Set qDef = CurrentDb.QueryDefs("EmployeeDetail_List") If Err.Number <> 0 Then Set qDef = CurrentDb.CreateQueryDef("EmployeeDetail_List") On Error GoTo 0 strQry = "SELECT Executive.[BU]FROM Executive ORDER BY Executive.[BU];" Set rst = CurrentDb().OpenRecordset(strQry, dbOpenDynaset) rst.MoveLast rst.MoveFirst Do While Not rst.EOF Path = "C:\" StrBU = rst("BU") StrExt = "_Jan09.xls" strFile = Path & StrBU & StrExt intDcode = rst("BU") qDef.SQL = "SELECT * FROM EmployeeDetail_List WHERE BusinessUnit = '" & intDcode & "'" DoCmd.OutputTo acOutputQuery, "EmployeeDetail_List", ac