Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    MeeToo is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    3

    IIF always causes syntax error when syntax is correct

    My version of Access has a problem with understanding IIF in a query. Even something as simple as Test:IIF(1+1=2,"yes","no") triggers a syntax error:

    There got to be an ActiveX or some other setting that is wrong
    using Windows10 and Office 365 enterprise
    Regional settings are correct


    All suggestions are appreciated

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's an odd one. I'd check the version and build number and see if Google tells me anything related. Updates have a way of introducing problems so I turned off updates long ago. Was it OK for a while and now it's not, or is this a brand new install? If this is recent and you had an update, maybe try rolling it back. I doubt it has anything to do with ActiveX; rather I suspect it's part of one of the main libraries like VBA or Access 16.0 database object.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Using the Query Builder (QBE) window the comma "," must be replaced by the semicolon ";" then the expression becomes: Test: IIF (1 + 1 = 2; "yes"; "no")

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by CarlettoFed View Post
    Using the Query Builder (QBE) window the comma "," must be replaced by the semicolon ";" then the expression becomes: Test: IIF (1 + 1 = 2; "yes"; "no")
    I've never had to do that?
    Code:
    SELECT Transactions.ID, IIf([ID]>100,"Yes","No") AS Test
    FROM Transactions;
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I also had no problem with the expression with a comma. In fact, I can't recall any built in function where the arguments are separated by semicolons. Are there any?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think Carletto's regional settings are set to Italian which uses semicolon as a list separator. I suspect the OPs settings are also using something other then comma.
    Here is a nice read:
    https://www.devhut.net/2017/09/01/sp...n-application/
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Previously posted here https://www.access-programmers.co.uk...ike-iif.316350
    With a raft of suggestions.

    @MeeToo - It might make more sense to continue the issue on the other thread rather than getting the same advice all over again?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That sucks. Posted here 11 days after excellent suggestions elsewhere and no courtesy link to the other forum.

  9. #9
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Micron View Post
    In fact, I can't recall any built in function where the arguments are separated by semicolons. Are there any?
    Depending on the OS regional settings. In my case (Greek), the separator of the arguments in expressions for form’s controls and for query design view, is the semicolon, but, not for the SQL and VBA code(!!!).

    For example, this is an expression of a calculated field of a query in Expression builder:
    Code:
    Logo: IIf(IsNull([makeLogo]);Replace([makeName];" ";"_");[makeLogo])
    and this is the same expression in SQL view:
    Code:
    IIf(IsNull([makeLogo]),Replace([makeName]," ","_"),[makeLogo]) AS Logo
    As you see, is a pain in the ass, and, in the beginning, it is able to make you crazy.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I guess not realizing that is the result of designing in a bubble of sorts. So what about a value list (control rowsource property)? Commas, not semicolons?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Micron View Post
    So what about a value list (control rowsource property)? Commas, not semicolons?
    With my regional settings, semicolons.
    Code:
    "1";"Value 1";"2";"Value 2";"3";"Value 3"

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Interesting, thanks.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    MeeToo is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    3
    Interesting discussion everyone. TO answer issues raised:
    1. Regional settings are correct for US and the DB is for US use only. The commas are not (a much as I can tell) getting switched to anything else.
    2. Micron, you suggested the problem may be in the VBA or Object library. Could you elaborate? Please use simple language; this is not my day job.
    3. I posted here after getting nothing from the other site; I'm looking for a smarter group of techies.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I posted here after getting nothing from the other site; I'm looking for a smarter group of techies.
    That's amazing. I saw several sincere and valuable responses and I know the caliber of many of those people, having been a member there for some time. If you think those responses and efforts were "nothing" then I certainly have nothing else to offer. Regardless, IMO you're not helping your case with that defense. I won't tell anyone at AWF that you think we're 'smarter' over here, but since many responders are members at several forums, AWF members will probably get the opportunity to weigh your opinion of them.

    Maybe you should take a look at this for some perspective and for what to do when you think you have received less than helpful responses. It might clue you in on what your next move ought to be.

    https://www.excelguru.ca/content.php?184
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    MeeToo is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    3

    Cool Solved It!

    The problem was in the Windows setting - specifically in the list delimiter settings. Several months ago for entirely strange reasons, I had to change the .csv delimiter from comma to pipe; I hadn't thought about it since. That carried over into SQL which now wanted IIF(1+1=2|"yes"|"no"). When I change it back to comma everything works and I can add the last 2 columns to my query report.
    Thanks for the help.
    MeeToo.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Correct Syntax
    By bidbud68 in forum Programming
    Replies: 6
    Last Post: 10-19-2012, 01:09 AM
  2. DCount Syntax Correct?
    By Huddle in forum Access
    Replies: 2
    Last Post: 06-21-2012, 03:06 PM
  3. SQL Correct Syntax
    By tbassngal in forum Queries
    Replies: 11
    Last Post: 09-01-2011, 01:55 PM
  4. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 10:47 AM
  5. What is the correct syntax for
    By giladweil in forum Access
    Replies: 1
    Last Post: 07-29-2010, 04:56 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