Hallo zusammen,
ich bin jetzt bald am verzweifeln.
Ich möchte aus einem Excel Makro auf Daten aus einer Access DB zugreifen
und habe mit der SQL Formulierung ein Problem.
Folgender Code funktioniert:
Code:
strSQL = "SELECT ITEMS.ITM_ID, " _
& "ITEMS.ITM_NAME, " _
& UnitSelection(i - 1) & ", " _
& "Sum(([ITV_VALUE]/[CRM_RATE])) AS Expr1, " _
& "CURRENCY_MATRIX.CRM_TYPE " _
& "FROM ([PARAMETERS] INNER JOIN " _
& "ITEMS ON PARAMETERS.PAR_ID = ITEMS.ITM_PAR_ID) INNER JOIN " _
& "((UNITS INNER JOIN (PACKAGES INNER JOIN " _
& "(ITEM_VALUES INNER JOIN " _
& "CURRENCY_MATRIX ON ITEM_VALUES.ITV_CUR_ID = CURRENCY_MATRIX.CRM_CUR_ID) " _
& "ON (PACKAGES.PCK_ID = ITEM_VALUES.ITV_PCK_ID) " _
& "AND (PACKAGES.PCK_PER_ID = CURRENCY_MATRIX.CRM_PER_ID)) ON " _
& "UNITS.UNT_ID = PACKAGES.PCK_UNT_ID) INNER JOIN " _
& "UNIT_HIERARCHY ON UNITS.UNT_ID = UNIT_HIERARCHY.UNH_UNT_ID) ON " _
& "ITEMS.ITM_ID = ITEM_VALUES.ITV_ITM_ID " _
& "WHERE (((ITEMS.ITM_PAR_ID)= " & Param_Pos & ") " _
& "AND ((PACKAGES.PCK_PER_ID)= " & Period & ") " _
& "AND ((PACKAGES.PCK_CAT_ID)= " & DataCat & ") " _
& "AND ((CURRENCY_MATRIX.CRM_CUR_ID_BASE)=" & KonsCur & ") " _
& "AND ((UNIT_HIERARCHY.UNH_PAR_ID)=" & Param_Unit & ") " _
& "AND ((UNIT_HIERARCHY.UNH_PARENT_NODE) " & ParentNode & ")) " _
& "GROUP BY ITEMS.ITM_ID, ITEMS.ITM_NAME, " & Having(i - 1) & "CURRENCY_MATRIX.CRM_TYPE " _
& "HAVING (((ITEMS.ITM_NAME) " & Criteria(i - 1) & "" _
& ") AND ((Sum(([ITV_VALUE]/[CRM_RATE])))0))"
Set rec = dbCurrent.OpenRecordset(strSQL, dbOpenForwardOnly)
Das funzt wie gesagt astrein.
Nun möchte ich ein weiteres Ausschlusskriterium einbauen:
((UNITS.UNT_CODE) NOT Like " & SelCriteria(i - 1)
Wenn ich ihn wie folgt einbaue bekomme ich einen Fehler:
Laufzeitfehler 3075
Sytaxfehler (fehlender Operator)
Code:
For i = 1 To 2
strSQL = "SELECT ITEMS.ITM_ID, " _
& "ITEMS.ITM_NAME, " _
& UnitSelection(i - 1) & ", " _
& "Sum(([ITV_VALUE]/[CRM_RATE])) AS Expr1, " _
& "CURRENCY_MATRIX.CRM_TYPE " _
& "FROM ([PARAMETERS] INNER JOIN " _
& "ITEMS ON PARAMETERS.PAR_ID = ITEMS.ITM_PAR_ID) INNER JOIN " _
& "((UNITS INNER JOIN (PACKAGES INNER JOIN " _
& "(ITEM_VALUES INNER JOIN " _
& "CURRENCY_MATRIX ON ITEM_VALUES.ITV_CUR_ID = CURRENCY_MATRIX.CRM_CUR_ID) " _
& "ON (PACKAGES.PCK_ID = ITEM_VALUES.ITV_PCK_ID) " _
& "AND (PACKAGES.PCK_PER_ID = CURRENCY_MATRIX.CRM_PER_ID)) ON " _
& "UNITS.UNT_ID = PACKAGES.PCK_UNT_ID) INNER JOIN " _
& "UNIT_HIERARCHY ON UNITS.UNT_ID = UNIT_HIERARCHY.UNH_UNT_ID) ON " _
& "ITEMS.ITM_ID = ITEM_VALUES.ITV_ITM_ID " _
& "WHERE [b](((UNITS.UNT_CODE) NOT Like " & SelCriteria(i - 1) & ") " _
& "AND((ITEMS.ITM_PAR_ID)= " & Param_Pos & ") " _
& "AND ((PACKAGES.PCK_PER_ID)= " & Period & ") " _
& "AND ((PACKAGES.PCK_CAT_ID)= " & DataCat & ") " _
& "AND ((CURRENCY_MATRIX.CRM_CUR_ID_BASE)=" & KonsCur & ") " _
& "AND ((UNIT_HIERARCHY.UNH_PAR_ID)=" & Param_Unit & ") " _
& "AND ((UNIT_HIERARCHY.UNH_PARENT_NODE) " & ParentNode & ") " _
& "GROUP BY ITEMS.ITM_ID, ITEMS.ITM_NAME, " & Having(i - 1) & "CURRENCY_MATRIX.CRM_TYPE " _
& "HAVING (((ITEMS.ITM_NAME) " & Criteria(i - 1) & "" _
& ") AND ((Sum(([ITV_VALUE]/[CRM_RATE])))0))"
Set rec = dbCurrent.OpenRecordset(strSQL, dbOpenForwardOnly)
Im Array SelCriteria steht z.B. 3214 HGSE (als String)
So langsam verzweifle ich.
Wäre super wenn mir hier jemand helfen könnte.....
Viele Grüße,
Dirk