1

I'm using Access ADP as a front end to SQL. I have two tables:

  • One is Price_2018
  • One is Price_2020

I want to choose Price from the Price_2018 or the Price_2020 table, depending upon the Purchase_Date. I tried first putting IiF statement in the Query Designer:

Iif ([Purchase_Date] < Convert(DateTime, '2020-01-01 00:00:00'),[Price_2018],[Price_2020])

Access didn't allow that and ended up putting the whole thing in quotes in the SQL pane, so all I got was text output.

Someone suggested putting in the Select section:

CASE ([Purchase_Date] < CONVERT(DATETIME, '2020-01-01 00:00:00')   WHEN 1 THEN [Price_2018] ELSE [Price_2020]

That didn't work either and gave me this error: 

Error in list of function arguments: '<' not recognized.Error in list of function arguments: ',' not recognized.Error in list of function arguments: 'FROM' not recognized.
Unable to parse query text.

How to resolve this error. I'm not familiar with using a CASE statement in Access or SQL.

I combined 2 Price tables (combined table called VWC_2018_2020) as suggested. Full query:

SELECT      TOP 100 PERCENT dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_ID,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Date_From, dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Update_Status,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Account_Number, dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Units,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Modifiers, dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Procedure_Code,                         NormalUnionCPASumAdjustments.SumAmount AS Adjustment, NormalUnionCPASumPayments.SumAmount AS Payment,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Fee AS Charge,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Fee - ISNULL(NormalUnionCPASumPayments.SumAmount, 0) AS Unpaid,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Primary_Diagnosis_Code, LastInsurancePmt.last_insurance_pmt,                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Patient_Number, dbo.VWCFees_2018_2020.VWC_2020, dbo.VWCFees_2018_2020.VWC_2018,                        IIF (dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Date_From < CDate('2020-01-01 00:00:00'),dbo.VWCFees_2018_2020.VWC_2018,dbo.VWCFees_2018_2020.VWC_2020) AS VWCFEE
FROM          dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo LEFT OUTER JOIN                        dbo.VWCFees_2018_2020 ON dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Procedure_Code = dbo.VWCFees_2018_2020.CPT LEFT OUTER JOIN                        dbo.NormalUnionCPASumAdjustments() NormalUnionCPASumAdjustments ON                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_ID = NormalUnionCPASumAdjustments.Service_ID LEFT OUTER JOIN                        dbo.NormalUnionCPASumPayments() NormalUnionCPASumPayments ON                         dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_ID = NormalUnionCPASumPayments.Service_ID LEFT OUTER JOIN                        dbo.LastInsurancePmt() LastInsurancePmt ON dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_ID = LastInsurancePmt.Service_ID
WHERE      (dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Update_Status <= 1) AND                         (dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Service_Date_From BETWEEN @StartDate AND @EndDate) AND                         (dbo.VHCSERVER_Ntier_VHC_dbo_vwGenSvcInfo.Patient_Number = @VHC_NumberChild)

Running in Access, gives error message: '<' not recognized. Missing FROM clause. Unable to parse query text.

What now? Thanks

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
sleibo
  • 11
  • 3
  • ADP's are an excellent RAD (rapid application development) tool and it's a shame they have been deprecated. Are you able to create a view in your SQL database that does what you want. Then you can reference that in your ADP. It does sound like you have some data modelling issues but that's a bigger fish to fry – Nick.Mc May 20 '20 at 02:41
  • You should really have one Price table with year column for 2018 and 2020 values. And there is no `CASE` or `CONVERT` methods in Access SQL. Also, like any SQL expression, all tables need to be in query to use its columns. Please show full query with `FROM` and `JOIN` clauses (not line snippets). – Parfait May 21 '20 at 00:14

1 Answers1

0

Since ADP files use an SQL Server backend (no longer supported as of MS Access 2013), you must use its TSQL dialect which does support IIF (or CASE) and can compare dates with string representations without conversion.

Below I extend to fuller query assuming various columns. Adjust as needed:

SELECT
  ...
  IIF(t.[Purchase_Date] < '2020-01-01', p18.price, p20.price)
  ...
FROM [Transactions] t
INNER JOIN [Price_2018] p18
  ON t.price_id = p18.price_id
INNER JOIN [Price_2020] p20
  ON t.price_id = p20.price_id
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • I don't think that's correct for an ADP - Access Data Project. No data is actually stored using the JET engine. There are no linked tables as such - it's completely referencing a SQL Server database – Nick.Mc May 23 '20 at 01:07
  • Ah yes, I mistakenly conflated `accde` files, compiled executable versions of `accdb`. OP will then need to use TSQL dialect which as of SQL Server 2012 does support [`IIF`](https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-ver15). Edited accordingly. – Parfait May 23 '20 at 15:41