How to use IIF function in a Passthrough query in Ms Access (1 Viewer)

[nector]

I have constructed a passthrough query in Ms Access shown below and in its current form it is working as expected:

SELECT tblPOSStocksSold.ItemSoldID, tblPosLineDetails.ItemesID, tblProducts.ProductName, tblPosLineDetails.QtySold, tblPosLineDetails.SellingPrice, tblPosLineDetails.IsTaxInclusive, tblPosLineDetails.RRP, tblPosLineDetails.Tax, (([QtySold]*[SellingPrice])-(([QtySold]*0))) AS TotalAmount, tblPosLineDetails.TaxClassA, tblPosLineDetails.ProductID, tblPosLineDetails.TourismClass, tblProducts.BarCode, tblPosLineDetails.InsuranceClass, tblPosLineDetails.ExciseClass, tblPosLineDetails.TurnoverTax, tblPosLineDetails.Bettings, tblPosLineDetails.Duty
FROM tblPOSStocksSold INNER JOIN (tblProducts INNER JOIN tblPosLineDetails ON tblProducts.ProductID = tblPosLineDetails.ProductID) ON tblPOSStocksSold.ItemSoldID = tblPosLineDetails.ItemSoldID;

Now I want to include the final column as below, then it fails to work what should I do with this column:

IIf([IsTaxInclusive]<0,"True","False") AS CGControl

What is the correct syntax for the new column above?

 

[arnelgp]

have you tried Case

Case When [IsTaxInclusive] < 0 Then "True" Else "False" As CGControl

 

[nector]

But this is an access query not VBA , Am doing this in the query design which does not allow Else Or Then except commas

 

[arnelgp]

OR change the Double qoute to single Quote:

IIf([IsTaxInclusive]<0,'True','False') AS CGControl

 

[nector]

But this is an access query not VBA , Am doing this in the query design which does not allow Else Or Then except commas

 

[nector]

IIf([IsTaxInclusive]<0,'True','False') AS CGControl

Many thanks to you

arnelgp​

This has worked very well.

 

[Minty]

I'm confused.
A pass-through is generally used with SQL server or similar, I can't see any benefit to using it on an Access backend unless I'm missing something.

 

[Pat Hartman]

It is a querydef that is passed-through. It isn't acting on local tables. Pass through queries MUST use the syntax of the target database. They do NOT use the standard Access SQL syntax. When the BE is SQL Server, the syntax of the passthrough querydef would be T-SQL. If the BE were Oracle, it would be Oracle's SQL syntax. If the BE were DB2, it would be DB2's SQL syntax, etc. Basic SQL syntax is controlled by a committee so all RDBMS providers support certain base constructions of which Access SQL is one. But other providers add their own enhancements. Access SQL is still stuck in the 90's and until the abomination data types were added with A2007, hadn't been changed since the initial release of Access. But the abomination data types needed "enhancements" in order to function.

 

[Minty]

@Pat Hartman - Not sure who that was directed at?
If it was me I'm well aware of how a pass-through works, but the inference of the post if you read it is that the target for the pass-through is an Access database.

Which is why I was confused, why would you use a pass-through query on an Access database BE?

 

[Pat Hartman]

@Minty Yes you. Sorry. No, the pass though being created inside the Access database is fine. I do it all the time. You have to use SQL View though since QBE only supports standard Access SQL. With SQL View, you can write a letter to Santa as long as you tell Access the query is a pass through. If you create the query in SQL Server, it isn't a pass through. The nomenclature "pass through" means a query created in Access which is not executed by Access but "passed through" to the server directly.

 

[ebs17]

but "passed through" to the server directly

However, the SQL dialect of the SQL server must be used for this. However, IIF is part of Jet-SQL. Does the server know IIF?

 

[Pat Hartman]

However, the SQL dialect of the SQL server must be used for this.

I believe that is what I said.

T-SQL recognizes IIf()

 

[arnelgp]

MsSQL has Iif() function in T-SQL, so the "notion" of it being recognized in Access is not True.
IIF (Transact-SQL) - SQL Server | Microsoft Learn

 

[Minty]

I am obviously not making myself clear or someone isn't reading the words in the posts.
Forgive the bluntness but I use Azure SQL extensively and use Pass-throughs a lot. I have reusable generic pass-through routines set up in most of the databases I work with and I certainly do not need to have their inner workings explained to me.

My post was to highlight that the OP is inferring he is using the pass-through to another Access database (backend I assume), if he is, why would you do that? There is zero benefit.

 

[Pat Hartman]

is inferring he is using the pass-through to another Access database (backend I assume),

I don't see that and clearly the OP isn't going to tell us.