1

I’m using MS Query in Excel to execute the following query:

select 
iif(egachid <>'GCAJA0', True, False)
from fgledg
where egcono='1'and egdivi='D30'and egvono=51166554

I have a tabel, fgledg, that contains a column, egchid. For a specific voucher (egvono = 51166554) I get the following rows:

EGCONO    EGDIVI    EGYEA4  EGVONO      EGCHID
1         D30       2015    51166554    GCAJA0
1         D30       2015    51166554    GCAJA0
1         D30       2015    51166554    GCAJA0
1         D30       2015    51166554    GCAJA0
1         D30       2015    51166554    SEBSHHASP
1         D30       2015    51166554    SEBSHHASP
1         D30       2015    51166554    SEBSHHASP

I would like my query to return True if the column egchid contains other values than GCAJA0 and false if the column only contains the value GCAJA0, but I keep getting error messages no matter how i write the syntax. It seems to accept the iif function at least, but the syntax is incorrect?

I have tried diffrent variations of writing the syntax, but get the following error messages:

IIf(egachid <>'GCAJA0', True, False)        Token <> was not valid
IIf(egachid = 'GCAJA0', False, True)        Token = was not valid
IIf([egachid] <>'GCAJA0', True, False)      Token [ was not valid
IIf([egachid] <>'GCAJA0', 'True', 'False')  Token [ was not valid

What am I doing wrong?

Lily
  • 9
  • 6

1 Answers1

0

Check your spelling of egachid. The column name is EGCHID -> no "A" Else you can try:

SELECT CASE WHEN egachid CONTAINS 'GCAJA0' THEN False ELSE True END AS name
Daniel Lee
  • 7,189
  • 2
  • 26
  • 44
  • Hi Daniel, You're compleately right about the spelling, however, I get the same error message. The CASE WHEN function is not supported by MS Query Excel. That's why I can't use it. – Lily Aug 15 '16 at 05:26
  • Try `SELECT IFF (EGCHID CONTAINS 'GCAJA0`, False, True)` You generally don't want to use <> for strings. Otherwise use `EGCHID LIKE 'GCAJA0'` if you're sure of the string being exactly that. – Daniel Lee Aug 15 '16 at 07:00
  • Didn't work. I tried both (CONTAINS and LIKE). It seems like it doesn't accept any conditions... :( I think I'm just going to download WinSQL and do the CASE WHEN option. Thanks anyway! :) – Lily Aug 15 '16 at 08:52