How do I correctly use a DLookup statement in a DAO.Recordset?
SRW1 = DLookup("Unit Name", "NBOI", "rU![BN (Owning)] = 'INF BN #' & rU![BN (Owning)= 'A?'")
Could I use Unit in this instance as Unit=[Unit Name].NBOI
I'm not sure I am using the wildcards correct either.
In the field [BN (Owning)].NBOIP I have the following values:
INF BN 1
INF BN 2
INF BN 3
In the field [CO (Owning)].NBOIP I have the following values:
A
B
C
D
E
F
G
H
J
I want to to look up the [Unit Name].NBOI
Code:
Dim rU As DAO.Recordset, X As Integer, Z As Integer, Units As String, A As String, B As String, C As String, SRW1 As String
Set rU = CurrentDb.OpenRecordset("NBOI")
If rU.BOF And rU.EOF Then
rU.Close
Else
rU.MoveLast
rU.MoveFirst
Do While Not rU.EOF
If Not rU![Co (Owning)] Like "HH*" Then
B = rU![Co (Owning)] & " "
ElseIf rU![Co (Owning)] Like "HH*" And rU![PLT] = "1" Or rU![PLT] = "2" Or rU![PLT] = "3" Or rU![PLT] = "4" Or rU![PLT] = "5" Then
B = rU![Co (Owning)] & " "
ElseIf rU![Co (Owning)] Like "HH*" Then
B = ""
End If
If rU![PLT] = "1" Or rU![PLT] = "2" Or rU![PLT] = "3" Or rU![PLT] = "4" Or rU![PLT] = "5" Or rU![PLT] = "SCT" Then
A = rU![PLT] & " "
ElseIf rU![PLT] = "MRT" Then
A = "MORT "
Else
A = ""
End If
If IsNull(rU![BN (Owning)]) Then
Units = ""
Z = 0
ElseIf rU![BN (Owning)] = "BDE" Then
Units = A & B & "2BDE101AB"
Z = 1 + X
ElseIf rU![BN (Owning)] = "INF BN 1" Then
Units = A & B & "1BN502IN"
Z = 0
ElseIf rU![BN (Owning)] = "INF BN 2" Then
Units = A & B & "2BN502IN"
Z = 0
ElseIf rU![BN (Owning)] = "INF BN 3" Then
Units = A & B & "1BN26IN"
Z = 2 + X
ElseIf rU![BN (Owning)] = "Fires" Then
Units = A & B & "1BN320FA"
Z = 8
ElseIf rU![BN (Owning)] = "CAV" Then
Units = A & B & "1BN75CAV"
Z = 0
ElseIf rU![BN (Owning)] = "BEB" Then
Units = A & B & "39BEB"
Z = 13
ElseIf rU![BN (Owning)] = "BSB" Then
Units = A & B & "526BSB"
Z = 19
End If
If rU![SRW 1] = "BDE HHC" Then
SRW1 = "2BDE101AB"
ElseIf rU![SRW 1] Like "INF BN # A?" Then
SRW1 = DLookup("Units", "NetBOI", "rU![BN (Owning)] = 'INF BN #' & B= A?'")
ElseIf Not IsNull(rU![SRW 1]) Then
SRW1 = Dlookup(rU![]
ElseIf IsNull(rU![SRW 1]) Then
SRW1 = ""
End If
rU.Edit
rU!UnitIDs = Z
rU![Unit Name] = Units
rU![SRW1] = SRW1
rU.Update
rU.MoveNext
Loop
End If
rU.Close
Set rU = Nothing
End Sub