CODEWITHSUNDEEP
sqlsql-serverms-access
Judith
Judith

Reputation: 85

Convert SQL Server query to be used in MS Access

I have a working query in T-SQL but need to convert it (and use it) in MS Access. I get an error when I try to run this:

SELECT 
    tblKPIData.id, tblKPIData.KPI_id,
    tblKPI.KPI_Name,
    tblKPIData.ImportTimestamp, tblKPIData.Quantity,
    tblKPIData.FinancialMonth, tblKPIData.FinancialYear,
    tblKPIData.Zone_id, tblZone.ZoneName,
    tblKPIData.DMA_id, tblDMA.DMA_Name,
    tblKPIData.TargetOrResult
FROM 
    ((((tblKPIData
INNER JOIN 
    tblKPI ON tblKPI.Id = tblKPIData.KPI_id)
LEFT JOIN 
    tblDMA ON tblDMA.Id = tblKPIData.DMA_id)
LEFT JOIN 
    tblZone ON tblZone.ID = tblKPIData.Zone_id)
INNER JOIN 
    tblDashboardKPI ON tblDashboardKPI.KPI_Id = tblKPIData.KPI_id)
INNER JOIN
    (SELECT 
         a.kpi_id, a.financialMonth, a.financialYear,
         ISNULL(a.zone_id, 0) AS zone_id,
         ISNULL(a.dma_id, 0) AS dma_id,
         a.targetorresult,
         MAX(a.importtimestamp) AS importtimestamp
     FROM
         tblKPIData a
     GROUP BY 
         kpi_id, financialMonth, financialYear,
         zone_id, dma_id, targetorresult) AS max_kpi ON (tblKPIData.KPI_id = max_kpi.KPI_id
                                                     AND tblKPIData.ImportTimestamp = max_kpi.importtimestamp
                                                     AND tblKPIData.FinancialMonth = max_kpi.FinancialMonth
                                                     AND tblKPIData.FinancialYear = max_kpi.FinancialYear
                                                     AND ISNULL(tblKPIData.Zone_id, 0) = ISNULL(max_kpi.zone_id, 0)
                                                     AND ISNULL(tblKPIData.DMA_id, 0) = ISNULL(max_kpi.dma_id, 0)
                                                     AND tblKPIData.TargetOrResult = max_kpi.TargetOrResult)
WHERE 
    tblKPIData.FinancialMonth = 'Oct'
    AND tblKPIData.FinancialYear = 2017
    AND tblKPIData.KPI_id IN (SELECT kpi_id FROM tblDashboardKPI WHERE tblDashboardKPI.KPI_Id = tblKPIData.KPI_id)
    AND (tblKPIData.Zone_id = 5 OR tblKPIData.DMA_id IN (SELECT id FROM tblDMA WHERE Zoneid = 5))
    AND ((tblDashboardKPI.Status) = True)
    AND ((tblDashboardKPI.Dashboard_Id) = 6)

I get this error:

Wrong number of arguments used with function in query expression 'tblKPIData.KPI_Id = max_kpi.KPI_id and tblKPIData.ImportTimestamp = max_kpi.importtimestamp and tblKPIData.FinancialMonth = max_kpi.FinancialMonth and ... etc

Can anyone tell me what is wrong? I really have no clue..

Upvotes: 0

Views: 57

Answers (1)

Erik A
Erik A

Reputation: 32682

ISNULL(Field) is valid Access SQL, but returns the same thing as Field Is Null (a boolean True if the field is Null, False if it isn't).

ISNULL(Field, 0) is invalid Access SQL, since ISNULL only takes one argument (hence the wrong number of arguments error).

Like Gordon Linoff said, you will want to use Nz if you want to replace Null with 0.

Just find and replace all the ISNULL with NZ

Upvotes: 3

Related Questions