访问VBA DLOOKUP

Question

Teller = Nz(DLookup("[Teller]", "[Lookuptable]", ("Artikel = '" & ValueArtikel & "' " And " Lookuptable= 'G'")), 0)
Noemer = Nz(DLookup("[Noemer]", "[lookuptable]", ("Artikel = ' " & ValueArtikel & " ' " And Lookuptable= " 'G' ")), 0)

我想在acces中执行一个DLOOKUP，但是我找不到正确的语句。我查看了许多站点，这是我认为是正确的两个查找，但都给出了错误类型不匹配。出纳员和noemer是整数，Artikel和artikelvalue和Lookuptable是字符串。对不起已经问过了但我找不到。我找到了很多关于它的帖子，但我无法修复它。尤其是对我糟糕的英语感到抱歉

Answer 1

第一个很接近。使用变量和Debug.Print帮助构建字符串。

Ctrl+g显示输出。

strCrit = "Artikel = '" & ValueArtikel & "' And Lookuptable= 'G'"
Debug.Print strCrit
Teller = Nz(DLookup("[Teller]", "[Lookuptable]", strCrit), 0)

Answer 2

我使用自己的函数进行查找，因为查找的性能非常差。

' Lookups Replacements
'---------------------

Function DLook(Expression As String, Domain As String, Optional Criteria) As Variant

  On Error GoTo Err_Handler

  Dim strSQL As String

  'DCount: strSQL = "SELECT COUNT(" & Expression & ") FROM " & Domain

  'Other replacements
  'DLookup: 
   strSQL = "SELECT " & Expression & " FROM " & Domain
  'DMax: strSQL = "SELECT MAX(" & Expression & ") FROM " & Domain
  'DMin: strSQL = "SELECT SUM(" & Expression & ") FROM " & Domain
  'DFirst: strSQL = "SELECT FIRST(" & Expression & ") FROM " & Domain
  'DLast: strSQL = "SELECT LAST(" & Expression & ") FROM " & Domain
  'DSum: strSQL = "SELECT SUM(" & Expression & ") FROM " & Domain
  'DAvg: strSQL = "SELECT AVG(" & Expression & ") FROM " & Domain

  If Not IsMissing(Criteria) Then strSQL = strSQL & " WHERE " & Criteria
  DLook = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenForwardOnly)(0)

Exit Function

Err_Handler:
  MsgBox "Error. Lookup couldnt be performed" & vbNewLine & Err.Description, vbCritical

End Function

呼叫：

If DLook("Column2", "Table1", "Column1 = " & ID) = 0 Then
  'Do stuff
End If

If DLook("Column2", "Table1") = 0 Then
  'Do other stuff
End If