DLookup doesn't pick up string if there is a space in it (1 Viewer)

[mike2000]

Somebody kindly helped me with a DLookup Function, it works but sometimes a code (string) will get entered and a space will appear in it, which stops the DLookup from working.
eg SL6K6 may get entered as SL 6K6. The code is scanned in so i can't uses a filter.
Is there any way around this?

Private Sub TxtBoxQty_BeforeUpdate(Cancel As Integer)

If DCount("[Spec]", "Stolen", "[BoxNo] = '" & Me.BoxNo & "' And [SpecCode] = '" & Me.SpecCode & "'") = 1 Then
MsgBox "This box is registered stolen", "vbcritical", "STOLEN BOX"
Cancel = True
Me.Undo
Exit Sub
Else
'Do nothing
End If

End Sub

Thanks.

 

[jca]

You could pass the code through a function which remove the blank first.

Public Function RemoveBlank(ByVal strCode As String) As String
Dim x As Integer
Dim strResult As String

For x = 1 To Len(strCode)
    If Mid(strCode, x, 1) <> " " Then
        strResult = strResult + Mid(strCode, x, 1)
    End If
Next x

RemoveBlank = strResult
End Function

Hope this help

 

[mike2000]

JCA, thanks very much. That's solved my problem.

 

[jca]

No problem, my pleasure