Replace a DLOOKUP expression with a Variable

[ted.martin]

I would like to replace the expression in a DLOOKUP with a text based variable.

For example:

REPLACE
If IsNull(DLookup("", "Contacts", "ContactID = 3")) = True Then .....

With
strExp = "DLookup('[eMail]', 'Contacts', 'ContactID = 3')"
If IsNull(strExp) = True Then .....

Of course this doesn't work as the variable strExp is not null and so does not process what is within the value.

Any thoughts would be appreciated.

Thanks

 

[vbaInet]

If strExp is a variant then you can check for Null. Do this instead:

If Len$(strExp & "") = 0 then

 

[ted.martin]

Thanks but I have not explained myself properly.

I want the Isnull(expression) to be processed

If IsNull(expression) = True then ....
where expression is a variable text that has the usual DLOOKUP format e.g.
DLookup("'", "Contacts", "ContactID = 3")


My reason is that I am using this within a Function and I want to pass the DLOOKUP expression through the Function arguments.

Thanks

 

[vbaInet]

Like I mentioned, if you want to test for Null, then your variable must be declared as a VARIANT, not a string, not an integer. Test it:

Dim myVar as Variant
myVar = DLookup(...)
Msgbox IsNull(myVar)

If no records are found or the field found is Null, then the DLOOKUP will return a Null. The only type of variable that can be assigned a Null value is a VARIANT.

 

[vbaInet]

Hmmm... maybe you're talking about being able to evaluate a DLOOKUP() function that is saved in a string variable? You can use the Eval() function but it will fail if the DLookup() returns a Null so that won't work for you.

Maybe you can explain why you're using a DLookup and we could advise of a different approach?

 

[ted.martin]

Since Posting, I have managed a workaround - thanks though. Interesting thought with the Eval function; may try and use that.

On my form is a Button1 picking up a textbox ; Button2 requires a DLOOKUP.

Private Sub cmdeMail1_Click()

If GoeMail("Field", eMail.Value) = True Then
MsgBox "OK"
Else
MsgBox "Nope"
End If

End Sub

Private Sub cmdeMail2_Click()

Dim varExp As Variant
varExp = DLookup("[eMail]", "Contacts", "ContactID = 3") ' for testing this is Null and ContactID 3 does not exist

If GoeMail("DLOOKUP", varExp) = True Then
MsgBox "OK"
Else
MsgBox "Nope"
End If

End Sub
_____________________________________________________________
Function GoeMail(strSource As String, myeMail As Variant) As Boolean

On Error GoTo Err_GoeMail

Select Case strSource

Case Is = "Field"

If Len(myeMail & "") > 0 And InStr(2, myeMail, "@") > 1 And myeMail Like "*[!0-9a-Z@._-]*" = False Then
GoeMail = True
Else
MsgBox "Client does not have a valid eMail address" & vbCrLf & vbCrLf & myeMail, vbExclamation, "Cancelled ..."
GoeMail = False
End If


Case Is = "DLOOKUP"

If IsNull(myeMail) = True Then
MsgBox "Client does not have an eMail address" & vbCrLf & vbCrLf & myeMail, vbExclamation, "No eMail address"
GoeMail = False
Else
If Len(myeMail & "") > 0 And InStr(2, myeMail, "@") > 1 And myeMail Like "*[!0-9a-Z@._-]*" = False Then
GoeMail = True
Else
MsgBox "Client does not have a valid eMail address" & vbCrLf & vbCrLf & myeMail, vbExclamation, "Cancelled ..."
GoeMail = False
End If
End If

Case Else
MsgBox "Programme Error", vbCritical, "Contact Administrator"
GoeMail = False

End Select


Exit_GoeMail:
Exit Function

 

[vbaInet]

Glad you got that working!