Results 1 to 4 of 4
  1. #1
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    The specified field '[Period to Post]' could refer to more than one table...

    Hello,



    I could use some help with a statement that is giving me an error because it exists in both tables that I'm using. The field is "Period to Post" and the statement that I'm trying to use is:


    IIf(Right([Period to Post],2)="01",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Jan


    The table names are "PSJ" and "GL Quick Query". I've tried using "[GL Quick Query].[Period to Post]" but that doesn't seem to work. I've also tried renaming "Period to Post" to just "Period" with "SELECT DISTINCT [GL Quick Query].[Period to Post] AS Period" at the start of my statement and then changing "Period to Post" to just "Period" in the above statement but that doesn't work either. Any suggestions would be greatly appreciated. Thanks.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    "[GL Quick Query].[Period to Post]" is the correct syntax. There must be another place in the query that it is being referenced?

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Not sure but is it possible that the - [credit amount] may be producing issues.

    How about trying (-1 X [credit amount])

  4. #4
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Thank you both. I went back and reviewed all of the SQL statement and did find another instance where Period to Post was used. Once I corrected that, it looks like my syntax is working. Thanks for your help!

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

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2013, 12:36 PM
  2. Replies: 4
    Last Post: 02-16-2012, 05:23 PM
  3. Replies: 10
    Last Post: 08-08-2011, 01:55 PM
  4. Allow a period in a table field name
    By escapades_access in forum Database Design
    Replies: 9
    Last Post: 07-08-2011, 09:21 AM
  5. Replies: 4
    Last Post: 03-12-2010, 05:42 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