Data Type Mismatch

[LadyDi]

I am using a DLookup to capture an e-mail address as a variable in my code. Then I use the e-mail address when I generate an e-mail with an attached report. My problem occurs when the e-mail address I am looking for does not yet exist. I tried using IsError with my Dlookup in an If statement, but I keep getting a "Data Type Mismatch" error, instead of the code going to my condition. I also tried IsMissing with the same results. I also tried to add a Try-Catch block, but was unable to use that logic in Access. How can I get this to work?

Here is the code I have now (EmailAddr is a string variable declared at the beginning of the sub. BinName is also a string variable declared earlier.):

If IsError(DLookup("EMAIL", "EMAIL_TBL", "CStr(BIN) =  " & BinName)) = True Then
   EmailAddr = InputBox("Tech e-mail not on file. " & Chr(13) & "Please enter the Tech's e-mail address for this bin.", "E-mail Not On File")
   Else
   EmailAddr = DLookup("EMAIL", "EMAIL_TBL", "CStr(BIN) =  " & BinName)
   End If

Any assistance you can provide would be greatly appreciated.

 

[pbaldy]

If the data type of BIN is text, you need delimiters:

http://www.mvps.org/access/general/gen0018.htm

Typically you'd test for the DLookup returning Null with IsNull().

 

[LadyDi]

This actually works, as long as the Bin is found in the e-mail table. Since the BinName is stored as a string variable, it errors out if I add the delimiters. I already tried it this way -- DLookup("EMAIL", "EMAIL_TBL", "BIN = ' " & BinName &"'". However, when I do it that way, it never finds the e-mail address.

I need to get it so that when the Bin is not found in the e-mail table, it asks the user for the e-mail address instead of erroring out.

 

[pbaldy]

Like I said, the test for when the DLookup doesn't find a match is IsNull().

 

[LadyDi]

I changed the IsError to IsNull, and tried again. Unfortunately, I am still getting the "Data Type Mismatch in Criteria Expression" error. How do you get the IsNull to work?

 

[pbaldy]

What is the data type of Bin? Are Bin and BinName the same, or are you comparing apples and oranges?

 

[LadyDi]

In my table, BIN is formatted as Text with a limit of 50 characters. I declared my variable BinName as String. I thought that was the same. Is it?

 

[pbaldy]

If the DATA TYPE of the field is text, it should require the delimiters, and you'd get the data type mismatch error without. You appear to say you don't get an error with them, but never find a match either. That would appear to be the problem we need to solve.

 

[LadyDi]

How would you solve that?

 

[pbaldy]

I'd set a breakpoint and check the value in the variable. Can you post the db here?