Mismatch error 13 using DLookup

[JPDeMoss]

First post here. I am trying to resolve an issue in MS Access using VBA where I am getting an error 13 type mismatch when using the DLookup function.

I am looking for two values one string type and one integer type. The string is using a form control value and the integer is just the specific value not contained in any form control. My code looks like this.

If DLookup("[ClientName]", _
   "tblTaskInfo", _
   "[ClientName] = '" _
      & Forms!frmLogEntry!ClientName _
      & "' AND TaskID = 1") 
   Then
 
'[{Moderator's note}:Z{I've left the following unstepped
'... to correctly step this would be to fix the code
'... so this is done in the follow-up post}]
      strSQL = "UPDATE tblTaskInfo SET TaskStart = Forms!frmLogEntry!SignedDate WHERE [ClientName] = Forms!frmLogEntry!ClientName AND [TaskID] = 1"
 

If I open the Immediate window and run the function there the ClientName value is returned to me not a null, but when updating the record in the sheet it displays the error and prompts to open the debugger. I have a version of this working fine using ClientNo in place of all ClientName fields, but the problem is in my data the ClientNo may not always be immediately filled out which then causes some of my later Dlookup functions to update and insert records to fail.

Any help would be appreciated.

[zmbd]

I started to step your code so that it would be easier to read without haveing to scroll thru things. When I started to this on your string in the if-then clause, it appears at first glance to be... well we'll take a look at that in a minute.

First let's take a look at the Dlookup() - there's potentially more than one thing wrong here:

What are you trying to actually return? Right now you appear to be returning a string value (the ClientName) where a boolean expression is required. The conditional must evaluate to a true or false, or be somthing that explicitly converts to a true/false boolean. A numeric is one thing as almost anything other than 0 will evaluate to "true", a string quite another - strings and booleans don't match for type and the compiler will complain about this, and if you get a null, then all bets are off as these are undefined - you could get an error, or you could get the "else" or false branch.

Pet-peeve-#2 on my list, using the Dlookup() directly in a conditional as you can not easily trouble shoot the returned value. This is closely related to:

My Pet Peeve #1 - It Lives, please don't feed it after midnight nor expose it to water at anytime:

DLookup("[ClientName]", _
   "tblTaskInfo", _
   "[ClientName] = '" _
      & Forms!frmLogEntry!ClientName _
      & "' AND TaskID = 1")

Now it isn't your fault, just about every example shows to build the criteria within the function; however, how are you going to check that the criteria acutally is resolving to the correct value... you can't! The fact that this is shown/taught this way just frustrates me to no end as all to often the error lies within a malformed string resolution!

The solution is simple, build the criteria string first and then use that in the function:

(...lots of code... - note we have air code here)
Dim z_str_sql As String
Dim z_var_return as Varient
(...lots of code...)
z_str_sql = "[ClientName] = '" _
      & Forms!frmLogEntry!ClientName _
      & "' AND TaskID = 1")
'Now if needed, you can place:
'Debug.Print "Sql= " & z_str_sql
'run the code, press <ctrl><g> and see what the 
'string resolves to
z_var_return = DLookup("[ClientName]", "tblTaskInfo", z_str_sql)

Checking how this string value resolves would be my first step.

Second, let's take a look at the update string line 11:
strSQL = "UPDATE tblTaskInfo SET TaskStart = Forms!frmLogEntry!SignedDate WHERE [ClientName] = Forms!frmLogEntry!ClientName AND [TaskID] = 1"
I just want to make sure that your form is open when this executes as this string is going to return the form's control names and not the values. Therefor, if the form is closed you should receive a few prompts asking for various information.
Once again, I would make sure that this string is resolving correctly by placing a debug print on the line following it.