Forum Discussion

HWK's avatar
HWK
Copper Contributor
May 05, 2025

Access keeps giving syntax error

Hello,

I wanted to classify test results in different groups and used this formula for a calculated field in a table: IIf([Testscore1c1]>28,'Plus',IIf([Testscore1c1]>15,'I',IIf([Testscore1c1]>6,'II',IIf([Testscore1c1]>2,5,'III','IV'))))

This worked (although I started with " " and had to change them to ' ' for my Dutch Access365 to accept it. 

 

The problem is that I copied this formula and wanted to use it in a different field (only changed [Testscore1c1] to [Testscore1cR] which is in the table as well. But the same formula is now giving me endless syntax errors without telling me what is wrong. I've tried everything I could think of and have spent hours searching for answers online already. Could you help me?

access
office 365

7 Replies

Sort By
  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    May 07, 2025

    you can also use Query and create the Calculated column there:

    SELECT 
    *, 
    Switch([Testscore1c1]>28,'Plus', [Testscore1c1]>15,'I',[Testscore1c1]>6,'II', [Testscore1c1]>2,5,'III',True,'IV') As Score1, 
    Switch([Testscore1cR]>28,'Plus', [Testscore1cR]>15,'I',[Testscore1cR]>6,'II', [Testscore1cR]>2,5,'III',True,'IV') As Score2  
FROM yourTableName;

     

    • HWK's avatar
      HWK
      Copper Contributor
      May 07, 2025

      Will that show the results in the table or in a separate query?

  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    May 06, 2025

    what is your Locale list separator character? isn't it comma (;)? then use it instead of comma (,) in your Calculated field Expression.

  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    MVP
    May 06, 2025

    Hi,

    It is strange that you would have to use ' instead of ". That shouldn't matter. The comma is even more strange. AFAIK the default for Dutch is semicolon as list separator. If you haven't changed that in the Windows regional settings, then you will have to use the semicolon as separator in every expression in Access including calculated fields: 

    IIf([Testscore1c1]>28;'Plus'; etc.

    Servus
    Karl
    ****************
    Access Forever News DevCon
    Access-Entwickler-Konferenz AEK

     

    • HWK's avatar
      HWK
      Copper Contributor
      May 06, 2025

      It says commas as list separators. I started with " " for true and untrue, but it didn't work. Then I changed the " to ' and it worked, for some reason. But when I did exactly the same but for Testscore1cR it doesn't work.

  • Tom_van_Stiphout's avatar
    Tom_van_Stiphout
    Steel Contributor
    May 05, 2025

    Can we step back and first discuss your table design? I have a suspicion this can be done in a better way. Show us a screenshot of your table in design view.

    • HWK's avatar
      HWK
      Copper Contributor
      May 06, 2025

      I'm quite new to databases and Access. I did read something online about having a separate table with minimum and maximum values. But I couldn't follow and thought this would be an easier way to do things for now. This is my table in design view:

      So, what I want to do with the database is the following: I have students from three villages who take different tests. It is a bit like the DrieMinutenToets and CITO tests. So I would like to classify their scores into quartile groups and outliers as well. I am expecting to get more data and therefore have to change the quartile minimums and maximums as well.

Resources