Results 1 to 4 of 4
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    avoid circular reference when formatting field in query

    I am trying to format a field to force it to have leading zero's.



    RecordCount: Format([RecordCount]), "0000000")

    However, this gives me "Circular reference caused by alias 'RecordCoun' in query definitions select list.


    Is there a way to avoid using a different name. I can make it work by using

    RecordCount: Format([RecordCountF]), "0000000")

    or

    RecordCountF: Format([RecordCount]), "0000000")

    But then I have to go in and change a bunch of other queries and modules that are using this query.

    Is there a way to force it to use 7 digits without changing the field name?

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    For what purpose do you uneed to format it that way?
    If it is just for looks, you can use the Format property on the field itself (in your Query, Report, Form), instead of the Format function.
    Or maybe you can apply this Format function at the very end, after it has been through all your queries and modules.

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I am using it to output to a flat file. The file specifications state that it has to be right aligned and have 0 if there is no data there.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The only way I know of is to have a second query to put that name back!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-20-2015, 10:41 AM
  2. Circular reference in calculated query
    By aquaticjewel in forum Queries
    Replies: 4
    Last Post: 03-11-2015, 08:33 AM
  3. circular reference cause by query
    By mike02 in forum Queries
    Replies: 4
    Last Post: 08-21-2013, 02:01 PM
  4. Circular reference caused by
    By rncarterjm in forum Queries
    Replies: 1
    Last Post: 04-02-2013, 08:59 PM
  5. Expression builder error, circular reference
    By cowboy in forum Programming
    Replies: 3
    Last Post: 07-15-2010, 12:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums