Solved DLookup with criteria based on a text field on a form

[chuckcoleman]

Hi,
I've been going nuts trying to get this to work.
In my form, in the After Update for a control, [LotNbrX], I want the value in that control to be used to establish the value of another control on the form called [EMailX]. Note: [LotNbrX] is a text field.

If I use a MsgBox to test this, MsgBox ("The value of LotNbr is: " & Me.LotNbrX), before the code below runs, and I enter 19 in the [LotNbrX] control, the MsgBox returns "The value of LotNbr is: 19", which is correct. The next step in the code is to set the value of EMailLU as shown below. It returns, "An Invalid Use of Null" when it runs.

Dim EmailLU as String
EMailLU = DLookup("[Email]", "[Email for Receipt]", "[LotNbr] =  ' " & [Forms]![Revenue Input Form].[LotNbrX] & " ' ")
Me.EmailX = EmailLU

What am I missing/doing wrong?

Thank you for your help - Chuck

 

[plog]

Divide, isolate and conquer.

1. Remove the criteria from your Dlookup entirely. If it returns a value, you know the criteria is the issue. If not, then the problem is either the field or the table name.

2. Make your criteria a string and msgbox that out. Then you can see exactly what the criteria is. And to be fair, the thing you messaged box out isn't exactly the same as what is in your Dlookup criteria (Me.LotNbrX & [Forms]![Revenue Input Form].[LotNbrX])

3. Make sure that field and value of the criteria is actually in [Email for Receipt].

 

[theDBguy]

DLookup() returns a Null if it doesn't find a match. Try wrapping it inside an Nz() function.

 

[chuckcoleman]

Thanks guys. I just have to believe the problem is that the criteria and the field in the query are text. It's hard to tell in a MsgBox when you see the value if it's right-aligned, (number), or left-aligned, (text). The data is in the query so there absolutely should be a match. Here are a couple of things I've tried.

   Dim EmailLU As String
   Dim EmailLU2 As String
  
   MsgBox ("The value of LotNbr is: " & Me.LotNbrX)
   MsgBox ("The value of the criteria is: " & [Forms]![Revenue Input Form].[LotNbrX])
   EmailLU = Nz([Forms]![Revenue Input Form].[LotNbrX])
   MsgBox ("The value of EmailLU is: " & EmailLU)
   EmailLU2 = DLookup("[Email]", "[Email for Receipt]", "[LotNbr] = ' " & Nz(EmailLU) & " ' ")
   MsgBox ("22-The value of EmailLU2 is: " & EmailLU2)
   If IsNull(Me.LotNbrX) Then

Both with and without Nx. I still get the Invalid Use of Null immediately before the MsgBox that starts with "22-....."

 

[Josef P.]

EmailLU2 = DLookup("[Email]", "[Email for Receipt]", "[LotNbr] = ' " & Nz(EmailLU) & " ' ")

Test:

dim CriteriaString as String
CriteriaString = "[LotNbr] = ' " & Nz(EmailLU) & " ' "
msgbox "CriteriaString: " & CriteriaString
EmailLU2 = DLookup("[Email]", "[Email for Receipt]", CriteriaString) '<-- will return Null, if no record found

Is the critera correct?

 

[Gasman]

Why the spaces in the criteria?

 

[chuckcoleman]

Hi Josef P. It returns, [LotNbr] ' '. Null. What I don't understand is why all of the "test" msgbox's ahead of this result return 19 which is the number I've entered into [LotNbr].

 

[chuckcoleman]

Why the spaces in the criteria?

Do you mean around the single and double quotes?

 

[chuckcoleman]

Josef P. I had the CriteriaString before I established EmailLU. After moving it I do get: [LotNbr] = 19. After that msgbox I still get Invalid Use of Null immediately before the msgbox that starts "22-....".

Dim EmailLU As String
   Dim EmailLU2 As String
   Dim CriteriaString As String
  
 
   MsgBox ("The value of LotNbr is: " & Me.LotNbrX)
   MsgBox ("The value of the criteria is: " & [Forms]![Revenue Input Form].[LotNbrX])
   EmailLU = Nz([Forms]![Revenue Input Form].[LotNbrX])
   CriteriaString = "[LotNbr] = ' " & Nz(EmailLU) & " ' "
   MsgBox ("The value of EmailLU is: " & EmailLU)
   MsgBox ("11-CriteriaString is: " & CriteriaString)
   EmailLU2 = DLookup("[Email]", "[Email for Receipt]", CriteriaString) '<-- will return Null, if no record found
   MsgBox ("22-The value of EmailLU2 is: " & EmailLU2)

 

[Josef P.]

CriteriaString = "[LotNbr] = ' " & Nz(EmailLU) & " ' "

This never results in [LotNbr] = 19 but [LotNbr] = ' 19 ' (if EmailLU = 19)

 

[chuckcoleman]

This never results in [LotNbr] = 19 but [LotNbr] = ' 19 ' (if EmailLU = 19)

[SOLVED] Sometimes you can learn something every day. For readability I had in my DLookUp criteria, " space ' space ". When I removed the spaces, the criteria worked. Gasman pointed this out early on and I just didn't pay enough attention to it because I didn't think it mattered. It does.

Thank you all for helping! You all make a difference.