SQL debugging as mismatch but works in a query

[LB79]

Hello,

I'm trying to create a QDF, but when the code is running it debugs with RT error 13 mismatch.
I've put the SQL in a query and it works. I've checked that the table connections are all the same format. I cant see what’s going wrong. Does anyone know the reason for this? (in debug the yellow line highlights:

SAISQL = "SELECT tbl_REF_RMOPIC.[UN Code], tbl_SAI_Transition.[Reporting Office], tbl_REF_RMOPIC.[Agency Name] AS Agency, [FY] & " - " & [FY_QTR (QQ)] AS Period, tbl_SAI_Transition.[Cost Month] AS CCYYMM, tbl_REF_Month2.MonthMMCCYY AS MMCCYY, tbl_SAI_Transition.Line, tbl_SAI_Transition.Vsl, tbl_SAI_Transition.Voy, tbl_SAI_Transition.Port, tbl_SAI_Transition.[Activity Date], tbl_SAI_Transition.[Charge Item], tbl_SAI_Transition.[PO Cur] AS [Currency], tbl_SAI_Transition.[PO Amount] AS [Current Estimate], IIf([PO Cur]='USD',[PO Amount],[Conversion to USD]*[PO Amount]) AS EstUSD, tbl_SAI_Transition.[GL Amount] AS Actual, IIf([PO Cur]='USD',[GL Amount],[Conversion to USD]*[GL Amount]) AS ActUSD, [ActUSD]-[EstUSD] AS Balance"

This is the full code:

Dim DB As DAO.Database
Dim QDF As DAO.QueryDef
Set DB = CurrentDb
Set QDF = DB.QueryDefs("qry_REF_GenericExport_QDF")
Dim SAISQL As String
SAISQL = ""
SAISQL = "SELECT tbl_REF_RMOPIC.[UN Code], tbl_SAI_Transition.[Reporting Office], tbl_REF_RMOPIC.[Agency Name] AS Agency, [FY] & " - " & [FY_QTR (QQ)] AS Period, tbl_SAI_Transition.[Cost Month] AS CCYYMM, tbl_REF_Month2.MonthMMCCYY AS MMCCYY, tbl_SAI_Transition.Line, tbl_SAI_Transition.Vsl, tbl_SAI_Transition.Voy, tbl_SAI_Transition.Port, tbl_SAI_Transition.[Activity Date], tbl_SAI_Transition.[Charge Item], tbl_SAI_Transition.[PO Cur] AS [Currency], tbl_SAI_Transition.[PO Amount] AS [Current Estimate], IIf([PO Cur]='USD',[PO Amount],[Conversion to USD]*[PO Amount]) AS EstUSD, tbl_SAI_Transition.[GL Amount] AS Actual, IIf([PO Cur]='USD',[GL Amount],[Conversion to USD]*[GL Amount]) AS ActUSD, [ActUSD]-[EstUSD] AS Balance"
SAISQL = SAISQL & " FROM ((tbl_SAI_Transition INNER JOIN tbl_REF_RMOPIC ON tbl_SAI_Transition.[Reporting Office] = tbl_REF_RMOPIC.[AG Code]) INNER JOIN tbl_REF_Month2 ON tbl_SAI_Transition.[Cost Month] = tbl_REF_Month2.MonthCCYYMM) INNER JOIN tbl_ExR_ExRate ON tbl_SAI_Transition.[Cost Month] = tbl_ExR_ExRate.MONTH_CCYYMM"
SAISQL = SAISQL & " WHERE (((tbl_SAI_Transition.[Charge Item]) Like '6910*' Or (tbl_SAI_Transition.[Charge Item]) Like '6920*'));"
QDF.sql = SAISQL
Set QDF = Nothing

Thanks

 

[ajetrumpet]

please format your SQL so there are no horizontal scrolls in it.

 

[Kafrin]

You've got issues with your quote marks in the sql, try this:

SAISQL = "SELECT tbl_REF_RMOPIC.[UN Code], tbl_SAI_Transition.[Reporting Office], tbl_REF_RMOPIC.[Agency Name] AS Agency, [FY] & ' - ' & [FY_QTR (QQ)] AS Period, tbl_SAI_Transition.[Cost Month] AS CCYYMM, tbl_REF_Month2.MonthMMCCYY AS MMCCYY, tbl_SAI_Transition.Line, tbl_SAI_Transition.Vsl, tbl_SAI_Transition.Voy, tbl_SAI_Transition.Port, tbl_SAI_Transition.[Activity Date], tbl_SAI_Transition.[Charge Item], tbl_SAI_Transition.[PO Cur] AS [Currency], tbl_SAI_Transition.[PO Amount] AS [Current Estimate], IIf([PO Cur]='USD',[PO Amount],[Conversion to USD]*[PO Amount]) AS EstUSD, tbl_SAI_Transition.[GL Amount] AS Actual, IIf([PO Cur]='USD',[GL Amount],[Conversion to USD]*[GL Amount]) AS ActUSD, [ActUSD]-[EstUSD] AS Balance"

If that doesn't work, try this instead:

SAISQL = "SELECT tbl_REF_RMOPIC.[UN Code], tbl_SAI_Transition.[Reporting Office], tbl_REF_RMOPIC.[Agency Name] AS Agency, [FY] & "" - "" & [FY_QTR (QQ)] AS Period, tbl_SAI_Transition.[Cost Month] AS CCYYMM, tbl_REF_Month2.MonthMMCCYY AS MMCCYY, tbl_SAI_Transition.Line, tbl_SAI_Transition.Vsl, tbl_SAI_Transition.Voy, tbl_SAI_Transition.Port, tbl_SAI_Transition.[Activity Date], tbl_SAI_Transition.[Charge Item], tbl_SAI_Transition.[PO Cur] AS [Currency], tbl_SAI_Transition.[PO Amount] AS [Current Estimate], IIf([PO Cur]=""USD"",[PO Amount],[Conversion to USD]*[PO Amount]) AS EstUSD, tbl_SAI_Transition.[GL Amount] AS Actual, IIf([PO Cur]=""USD"",[GL Amount],[Conversion to USD]*[GL Amount]) AS ActUSD, [ActUSD]-[EstUSD] AS Balance"