[SOLVED] Using Varible in a Dlookup to rifle through a For Each Loop

Using Varible in a Dlookup to rifle through a For Each Loop

Im calculating probabilities with numbers from diffrent tables. Every customer has a letter dedicated to a document number. These letters have a probability (0/ 0.3 or 0.7).Im new in Vba and dont really know why Im getting this error, or how i could fix it.

Dim Sum As Double
Dim CB As Double
Dim Csize As Double
Dim USp As Double
Dim DocCat As String

Sum = 0

'CB Loop - Go through all customers in CArray and get the document letter corresponding to the document number in the textbox named SBBox

Dim CArray(1 To 5) As String
CArray(1) = "FF"
CArray(2) = "GG"
CArray(3) = "HH"
CArray(4) = "JJ"
CArray(5) = "DD"

Dim customer As Variant
For Each customer In CArray

'Get portion from customer table by corresponding the customer name with name in array. Here is my problem at the moment
Csize = DLookup("[portion]", "tblCustomer", "[sName]" = customer)

'get document letter from table customer depending on name from array. The table contaning the DocLetters is named the same as in the array (e.g. FF GG HH and so on)
    DocCat = DLookup("[DocLetter]", customer, "[Doc Number]= SBBox.Value")

   
'Use the DocCat to categoriez "bad", "good", "great" Letter
    If StrComp(DocCat, "A") Or StrComp(DocCat, "B") Or StrComp(DocCat, "C") Then

    CB = 0

    ElseIf DocCat = StrComp(DocCat, "E") Or StrComp(DocCat, "F") or Null Then

    CB = 0.3

    Else: CB = 0.7

    End If

'get the usage amount from the tblUsage depending from the PartNumber in PNBox
 USp = DLookup("[Rel]", "tblUsage", "[Material]=PNBox.Value")

'sum up all poducts from each loop

      Sum = Sum + CB * Csize * USp


Next customer


'print out inTextbox
    Sum = TextBox1.Text

End Sub

Im getting an error "invaled use of Null" on Csize = DLookup.... I guess its because of the criteria

I cant chance any table names and the code will be extended in the futre.

Any help would be appreciated.

Solution

You must concatenate the variables:

' Text
Csize = Nz(DLookup("[portion]", "tblCustomer", "[sName]" = '" & customer & "'"))

' Number
DocCat = DLookup("[DocLetter]", customer, "[Doc Number] = " & SBBox.Value & "")

' Text
USp = DLookup("[Rel]", "tblUsage", "[Material] = '" & PNBox.Value & "'")