How to use IIF function in a Passthrough query in Ms Access

[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.

 

[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?

 

[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?

 

[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.