0

I'm having trouble getting the IIf() function to work in the Expression Builder in Microsoft Access 2010 64-bit, Windows 7 Professional. I have knocked the problem down to its most basic form, and it really looks to me like the IIf function, within the Expression Builder, is not supported. I am attempting to follow the instructions given at http://msdn.microsoft.com/en-us/library/gg241307%28v=office.14%29.aspx.

The particulars: I have a simple database, and I'm pulling into a query just one field from one table: Table: schedule_data, Field: purpose. On the first empty column to the right, I am attempting:

MyExp: IIf( IsNull([purpose]) , "n/a" , [purpose] )

I have tried many variations of the IIf() function, to include boolean values such as:

MyExp: IIf(1=2,"Wow!","Bah!")

And every time the error returned is: "The expression you entered contains invalid syntax." + "You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks." The options given are "OK" and "Help," and clicking on the "Help" button takes me directly to the front page of the help system (i.e. doesn't direct me to anything specific to my issue.)

My question is, could this be something related to my 64-bit installation? I am aware that some functionality in 64-bit applications is not widely supported yet. I have used the equivalent function in other languages and applications, such as WordPerfect (Perfect Script), to great effect. Please help; this is driving me crazy.

As requested: Here is a paste of my SQL when switching to SQL View. It wouldn't let me switch views until I corrected the error, so I simply had to remove that column:

SELECT schedule_data.sdate, schedule_data.purpose FROM schedule_data;
Tony Rowlett
  • 3
  • 1
  • 8
  • Please switch your query from Design to SQL View, copy the statement text, and paste it into your question. – HansUp Mar 11 '14 at 17:24
  • As for boolean values, I mean to say "True","False" in place of "Wow!","Bah!" but I don't think it makes a difference in my simple examples. – Tony Rowlett Mar 11 '14 at 17:25
  • @HansUp, OK, done, but it wouldn't let me switch to SQL View with the problematic statement. – Tony Rowlett Mar 11 '14 at 17:30
  • Does the following work in Expression Builder: `MyExp: iif([purpose] is null, "n/a", [purpose])`? – Yawar Mar 11 '14 at 23:10
  • As a general tip, you don't have to get stuck in a single query designer/editor if Access won't let you switch views because of an error. You can just open a new SQL query designer window, switch to SQL view, and type in some test SQL to see what will work. I do this all the time. – Yawar Mar 11 '14 at 23:12
  • Ah, thanks for that, I'll remember it. Also, no, when I try your verson with "[purpose] is null" I get the same error. – Tony Rowlett Mar 11 '14 at 23:34

3 Answers3

1

I had exactly the same issue with access 2013. By looking around getting crazy i tried to change my regional settings from Switzerland french to English US and bingo it worked. That's bug in Access for sure. I suggest when you write your iif expression to have your regional to US and then switch back to your original region if needed.

kamy
  • 41
  • 4
0

The issue is local to either the installation of Microsoft Office 2010 (Access), or to the workstation's hardware or operating system details. This is confirmed by checking another computer in the same organization with the exact same computer, OS, and application software. The minor differences are: the problematic computer has 16.0 GB or installed memory while the computer without the issue has 8.0 GB. The only other difference is that the last three digits of the "Product ID" is (not Version) on the problematic computer are 220 and the last three on the working computer are 741.

Tony Rowlett
  • 3
  • 1
  • 8
0

No need to change regional settings, just use semicolon instead of comma:

IIF([MyField]="Special Value"; 0; 1)
Limonka
  • 656
  • 3
  • 18