CODEWITHSUNDEEP
ms-access
Dude
Dude

Reputation: 11

How to fix a simple Dlookup - not working

I am trying to get username data from one table [tbl_ComputerNames] into a displayed form control: Me.UN

Either of these should work as far as I know...

 Me.UN = DLookup("[UN]", "tbl_ComputerNames", "[PCName] = '" & Forms![frm_CurrentConnections]!PCName & "'")

or

 Me.UN = DLookup("[UN]", "tbl_ComputerNames","[PCName] = '" & Me.PCName & "'")

I get the same error from both formats

Runtime 3075 - Syntax error in string in query expression '[PCNAME]='HD0HD8F2'

The PCName data is a string of text and numerals I am treating as text, I thought correctly.

I have tried replacing the criteria with known data and it works fine, which leads me to believe the error is in how I'm referencing the criteria...

I even had the PCName go to a msgbox instead of to the form criteria and it seemed to work fine, even displaying the correct PCName. But as soon as I put in the criteria again it goes back to the same issue.

I feel that I'm overlooking or forgetting some simple step or action. I have checked a bunch of pages about similar problems and I just can't seem to find anything obvious I'm doing wrong.

EDIT- exact error string below pasted from debug window as requested (including the full-stop at the end) Syntax error in string in query expression '[PCName] = 'H5CG7385KF7'.

EDIT: I used a function to remove any Nulls from the input string - as below - this did not appear to fix the problem and the error is still the same if I use the concatenated code. - at this point i will run with the unconcatenated code unless anyone can think of anything else to try?

Public Function StripNull(ByVal InString As String) As String

'Input: String containing null terminator (Chr(0))'Returns: all character before the null terminator

Dim iNull As Integer

If Len(InString) > 0 Then
iNull = InStr(InString, vbNullChar)

Select Case iNull
Case 0
    StripNull = InString

Case 1
    StripNull = ""

Case Else
   StripNull = Left$(InString, iNull - 1)

End Select

End If
End Function

Upvotes: 0

Views: 1297

Answers (1)

Erik A
Erik A

Reputation: 32682

If you're getting your parameter from a form value, just use a form-based parameter. That avoids these kind of string-concatenation based errors:

Me.UN = DLookup("[UN]", "tbl_ComputerNames", "[PCName] = Forms![frm_CurrentConnections]!PCName")

Upvotes: 1

Related Questions