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

bxdobs

New member
Local time
Today, 02:54
Joined
Apr 5, 2018
Messages
12
query entered

Code:
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:

Code:
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

Banishment Pending
Local time
Today, 04:54
Joined
May 11, 2011
Messages
11,613
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

AWF VIP
Local time
Today, 10:54
Joined
Oct 27, 2015
Messages
998
Try using single quotes instead of double quotes.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:54
Joined
Aug 30, 2003
Messages
36,118
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

New member
Local time
Today, 02:54
Joined
Apr 5, 2018
Messages
12
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

New member
Local time
Today, 02:54
Joined
Apr 5, 2018
Messages
12
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
 

bxdobs

New member
Local time
Today, 02:54
Joined
Apr 5, 2018
Messages
12
see my first post ... this is my formatting .... ms removes all the cr/lf and whitespace
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:54
Joined
Aug 30, 2003
Messages
36,118
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

Lifelong Learner
Local time
Today, 02:54
Joined
Mar 14, 2017
Messages
8,738
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

Lifelong Learner
Local time
Today, 02:54
Joined
Mar 14, 2017
Messages
8,738
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

Wino Moderator
Staff member
Local time
Today, 02:54
Joined
Aug 30, 2003
Messages
36,118
No worries (if you meant me).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:54
Joined
Feb 28, 2001
Messages
27,001
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.
 

Users who are viewing this thread

Top Bottom