Frustrated with Access 2016 SQL Query not accepting a literal string in compound/nested iif

[bxdobs]

query entered

SELECT
  Schedule.*,
   [Work Orders].Jobdescription,
   [Work Orders].Schedulecomment,
   [Work Orders].Customeritems,
   [Work Orders].InvoiceDate,
   [Work Orders].Invoicenumber,
   IIf(Not IsNull([Work Orders].invoicenumber)=True,”Invoiced”,
      IIf([Work Orders].Completed=True,"Completed",Schedule.JobStatus)) AS Status
FROM Schedule LEFT JOIN [Work Orders]
     ON Schedule.workorder = [Work Orders].[WorkOrder#];

MS interprets and CHANGES this to:

SELECT
   Schedule.*,
   [Work Orders].Jobdescription,
   [Work Orders].Schedulecomment,
   [Work Orders].Customeritems,
   [Work Orders].InvoiceDate,
   [Work Orders].Invoicenumber,
   IIf(Not IsNull([Work Orders].invoicenumber)=True,[”Invoiced”],
      IIf([Work Orders].Completed=True,"Completed",Schedule.JobStatus)) AS Status
FROM Schedule LEFT JOIN [Work Orders]
    ON Schedule.workorder = [Work Orders].[WorkOrder#];

Which incorrectly wants an input param value instead of the literal string

Does this mean Access doesn't accept simple compound/nested iif statements? ... is there anther way to do this inline without resorting to a UDF?

 

[plog]

I don't know how you did it, but the double quote marks around Invoiced are different than the ones around Completed. Copy the ones from Completed and put them around Invoiced in your initial query. Or switch them all to single quotes

”Invoiced” --> ascii = 148
"Completed" --> ascii = 34

 

[sonic8]

Try using single quotes instead of double quotes.

 

[pbaldy]

I don't know how you did it, but the double quote marks around Invoiced are different than the ones around Completed

I've seen that happen when code was copied from Word to the VBE. I think Word (and probably other word processing software) has opening and closing quotes instead of/in addition to the regular double quotes we use.

 

[bxdobs]

ok thanks ... duly noted ... MS Word has fouled me up for other things in the past ... stuck doing an update on a W10 machine which doesn't have all my dos tools on it otherwise would have seen that ... managed to rewrite this statement again from scratch in the SQL Edit window and it finally was accepted as written

thanks again

 

[bxdobs]

footnote ... this all stems from MS ACCESS not accepting AS WRITTEN FORMATTING ... very hard to debug the blob that occurs once saved ... Perhaps the newer versions of MSA have options to correct this but being that I only touch code every other year I haven't gone looking for better options

 

[pbaldy]

AS WRITTEN FORMATTING

That's a new term for me. What does it mean in this context?

 

[bxdobs]

see my first post ... this is my formatting .... ms removes all the cr/lf and whitespace

 

[pbaldy]

Oh sorry, yeah, that's a long standing item on the Access developer's wish list. We'd like it to be like SQL Server, with formatting, comments, etc.

 

[Isaac]

footnote ... this all stems from MS ACCESS not accepting AS WRITTEN FORMATTING ... very hard to debug the blob that occurs once saved ... Perhaps the newer versions of MSA have options to correct this but being that I only touch code every other year I haven't gone looking for better options

you mean as in the fact that access considers whitespace significant and insist on everything being crammed together?

If that is what you are referring to, then absolutely that is hideous and what makes the access SQL view IDE a piece of cr@p as someone said earlier today. Note when I say that I am of course only referring to the SQL view

 

[Isaac]

oops after I posted this I realize there was another page to this thread and you had already answered that. Sorry for the redundant post

 

[pbaldy]

No worries (if you meant me).

 

[The_Doc_Man]

Word has a feature in the Options section where you can disable "SMART" quotes, in which case you get the "vanilla" single quote or double quote as you typed it. Therefore, if this was copied from Word with smart quotes enabled, those characters aren't quotes in USASCII. They are quotes in some extended portion of the character set. If they are not seen as quotes, that is why Access "diddles" with them.