Results 1 to 5 of 5
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Invalid Syntax

    I have the following Text Box where its Control source with code "=IIf([T020]<>0,Sum([PrevBalance147]),0)"


    Where [T020] Text Box with "=Count(*)" in the footer and PrevBalance147 is an integer From the datasource of the report.

    When this Control source is created on Computer A and Opened it works fine on both Computers when opening the report.
    But Computer B Can't Create the same Control source or edit the existing Control source without receiving the error:
    "The expression you have entered contains invalid syntax."
    "You may have entered an operand without an operator."

    I have tried multiple ways of Criteria for this Control source but receive errors on Computer B That Computer A Doesn't Receive.
    Don't know if this has something to do with computer settings that have been changed.
    Both Computers have been freshly reinstalled with Windows 10 and Microsoft Access 2016 32bit

    Ruben

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make an invisible text box that does the sum, then put IT in the IIF,
    =IIf(txtT020<>0,txtSum,0)

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try replacing T020 in the IIf statement with what it is counting Count("*", "TableName")
    Is your PrevBalance147 a control name or a field name? If a control name, use the field instead

    And just out of interest, how do you keep track of what all your many fields and controls with similar names refer to?
    For example, can I assume you may well have PrevBalance1 through to 146 as well?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    As I said previously i have tried multiple ways of criteria and all of them work 100% on Computer A, even the ones mentioned on this forum. They all work on Computer A.
    Computer B will throw an error when editing the text field or trying to create the expression in a text field even when copying and pasting the exact code to a text field.
    Computer B will only allow(output is correct) these expressions if the report is copied from another device or access database. I might not have narrowed down to a question in the previous post,
    I am wondering if there are any settings that cause Computer B to throw errors for code that would be 100% correct in normal databases and are proven to work in other access databases?

    ridders52 : "And just out of interest, how do you keep track of what all your many fields and controls with similar names refer to?
    For example, can I assume you may well have PrevBalance1 through to 146 as well?"

    We use these numbers behind the fields to narrow down what table the field comes from, where we have a number allocated to a table and then all the fields within the table uses that number as a suffix, If multiple Fields with the same name do come forward and we have these suffixes already we tend to use alphabetical numbers and description inside the table for what reasons we use them multiple times(Only happens on rare occasions).

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Have you tried replacing the .accdb on computer B with the .accdb on computer A?

    Afterthought:
    Or moving the accdb from computer B to computer A to see if problem follows?
    Last edited by davegri; 11-28-2018 at 10:00 AM. Reason: afterthought

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

Similar Threads

  1. Replies: 1
    Last Post: 06-22-2018, 05:53 PM
  2. Using IIF in an append query, invalid syntax
    By securitywyrm in forum Queries
    Replies: 5
    Last Post: 03-27-2018, 04:17 PM
  3. Replies: 18
    Last Post: 02-26-2018, 03:42 PM
  4. Replies: 1
    Last Post: 05-29-2013, 04:01 PM
  5. Replies: 13
    Last Post: 12-05-2011, 05:10 AM

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