Results 1 to 7 of 7
  1. #1
    rsnoco is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    4

    Field reference

    I have inherited a query that has been running for years, and now is not.


    SELECT IIf([4]>0,"551","552") AS Mvt, Abs([4]) AS QtyInUOm, SAPtoOURINV_InvComparison_4day_Q02.SAP_BUm, Null AS Expr2, SAPtoOURINV_InvComparison_4day_Q02.MATERIAL, Null AS Expr3, Null AS Expr4, "1045" AS Plant, Null AS Expr5, "A043" AS StLoc, "10937" AS CostCntr, Null AS Expr6, Null AS Expr7, Null AS Expr8, "0200" AS Rsn, Null AS Expr9, "Decimal Dust" AS [Text]
    FROM SAPtoOURINV_InvComparison_4day_Q02
    WHERE (((Abs([4]))<1) AND ((SAPtoOURINV_InvComparison_4day_Q02.SAP_BUm)<>"G" ) AND ((IIf([1]=[2] And [2]=[3] And [3]=[4],"Adj"))="Adj"));




    The error is:
    The Microsoft Access database engine does not recognize '[1]' as a valid field name or expression.


    There are no fields called 1, 2, 3 or 4 in the query SAPtoOURINV_InvComparison_4day_Q02. The concept of column 1=column 2=column 3=column 4 makes no sense for the columns in this query. The form launching this query does not have any fields called 1, 2, 3 or 4.


    My question is "What is the [1] referring to?"

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    For convenience I've formatted the sql to make this easier to read.

    Code:
    SELECT IIf([4] > 0, "551", "552") AS Mvt, 
           Abs([4])                   AS QtyInUOm, 
           SAPtoOURINV_InvComparison_4day_Q02.SAP_BUm, 
           Null                       AS Expr2, 
           SAPtoOURINV_InvComparison_4day_Q02.MATERIAL, 
           Null                       AS Expr3, 
           Null                       AS Expr4, 
           "1045"                     AS Plant, 
           Null                       AS Expr5, 
           "A043"                     AS StLoc, 
           "10937"                    AS CostCntr, 
           Null                       AS Expr6, 
           Null                       AS Expr7, 
           Null                       AS Expr8, 
           "0200"                     AS Rsn, 
           Null                       AS Expr9, 
           "Decimal Dust"             AS [Text] 
    FROM   SAPtoOURINV_InvComparison_4day_Q02 
    WHERE  ( ( ( Abs([4]) ) < 1 ) 
             AND ( ( SAPtoOURINV_InvComparison_4day_Q02.SAP_BUm ) <> "G" ) 
             AND ( ( IIf([1] = [2] 
                         And [2] = [3] 
                         And [3] = [4], "Adj") ) = "Adj" ) );  
    https://www.dpriver.com/pp/sqlformat.htm

  3. #3
    rsnoco is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    4
    Thank you for formatting that query! I will do that in the future.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by rsnoco View Post
    There are no fields called 1, 2, 3 or 4 in the query SAPtoOURINV_InvComparison_4day_Q02. The concept of column 1=column 2=column 3=column 4 makes no sense for the columns in this query. The form launching this query does not have any fields called 1, 2, 3 or 4.

    My question is "What is the [1] referring to?"
    I don't know. I was hoping someone else would have chimed in for you by now. Do you have a backup copy of your db where this query still works? If so can you post it's sql?
    Can you post the sql for SAPtoOURINV_InvComparison_4day_Q02?
    Better yet can you post a zipped copy of your db (with confidential data removed and replaced with test data)?

  5. #5
    rsnoco is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    4
    Quote Originally Posted by kd2017 View Post
    I don't know. I was hoping someone else would have chimed in for you by now. Do you have a backup copy of your db where this query still works? If so can you post it's sql?
    Can you post the sql for SAPtoOURINV_InvComparison_4day_Q02?
    Better yet can you post a zipped copy of your db (with confidential data removed and replaced with test data)?
    A backup of the DB is acting identically.


    The SAPtoOURINV_InvComparison_4day_Q02 query:
    TRANSFORMSUM(Formatnumber([diff],3))ASdiffer
    SELECTdbo_saptoourinv_invcomparison_t.material,
    dbo_saptoourinv_invcomparison_t.desc,
    dbo_sap_material_master.mrp_controllerASMRP,
    dbo_sap_material_master.profit_centerASPC,
    dbo_saptoourinv_invcomparison_t.sap_bum
    FROMsaptoourinv_invcomparison_4day_q01
    RIGHTJOIN(saptoourinv_invcomparison_q_invdates
    INNERJOIN(dbo_saptoourinv_invcomparison_t
    INNERJOINdbo_sap_material_master
    ON
    dbo_saptoourinv_invcomparison_t.material=
    dbo_sap_material_master.material_nbr)
    ONsaptoourinv_invcomparison_q_invdates.inv_date=
    dbo_saptoourinv_invcomparison_t.inv_date)
    ONsaptoourinv_invcomparison_4day_q01.material=
    dbo_saptoourinv_invcomparison_t.material
    WHERE(((dbo_saptoourinv_invcomparison_t.material)<>"2000621")
    AND((dbo_saptoourinv_invcomparison_t.desc)NOTLIKE(
    "*filter*"))
    AND((dbo_sap_material_master.profit_center)NOTIN
    ("ieb","ipm8","spm6","p0tb"))
    AND((dbo_sap_material_master.mrp_controller)NOTIN(
    "2cc","2kc","072","1cp",
    "1kp","1xy","ipj"))
    AND((RIGHT([mrp_controller],1))NOTIN("l","z"))
    AND((dbo_saptoourinv_invcomparison_t.sap_inv)<1))
    GROUPBYdbo_saptoourinv_invcomparison_t.material,
    dbo_saptoourinv_invcomparison_t.desc,
    dbo_sap_material_master.mrp_controller,
    dbo_sap_material_master.profit_center,
    dbo_saptoourinv_invcomparison_t.sap_bum
    PIVOTsaptoourinv_invcomparison_q_invdates.invday;

    And the data it returns:


    I will work on scrubbing a sharable version of the DB. In the mean time, I suspect you already answered my question. My SQL formatter was dropping the TRANSFORM/PIVOT statements from the sub-query. I am reasonably certain my answer will be found right there.

    I will keep everyone posted.

    Thank you!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 5 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    rsnoco is offline Novice
    Windows 10 Office 365
    Join Date
    Aug 2022
    Posts
    4

    Resolved

    Just closing this out... from my last post, it appeared there should be five columns:
    SELECT dbo_saptoourinv_invcomparison_t.material,
    dbo_saptoourinv_invcomparison_t.desc,
    dbo_sap_material_master.mrp_controller AS MRP,
    dbo_sap_material_master.profit_center AS PC,
    dbo_saptoourinv_invcomparison_t.sap_bum
    FROM<snip>

    It turns out, there were four more columns added by the pivot, named "1", "2", "3" and "4". One of the subqueries was failing to match on 1 and 2, so only 3 and 4 made it back to the pivot, and as a result, only columns "3" and "4" were added. Once the subquery was corrected, the "1 = 2 = 3 = 4" logic works, as odd as it looks.

    Thanks to everyone who helped out with this issue.

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

Similar Threads

  1. Using field reference prevents using criteria
    By fhickler in forum Access
    Replies: 19
    Last Post: 02-27-2019, 12:25 PM
  2. VBA How To Reference Active/Selected Field?
    By Enrightt in forum Forms
    Replies: 2
    Last Post: 08-26-2017, 03:59 PM
  3. Replies: 1
    Last Post: 03-17-2017, 08:08 PM
  4. Replies: 2
    Last Post: 07-28-2013, 04:52 PM
  5. How do I reference a Calculated Field?
    By Zerdan in forum Forms
    Replies: 7
    Last Post: 05-31-2011, 01:38 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