DLookup Mismatch Error 13

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
strSQL = "UPDATE tblTaskInfo SET TaskStart = Forms!frmLogEntry!SignedDate WHERE [ClientName] = Forms!frmLogEntry!ClientName AND [TaskID] = 1"

Open in new window


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.
JPDeMossAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
Have you tried:
If DLookup("[ClientName]", "tblTaskInfo", "[ClientName] = '" & Forms!frmLogEntry!ClientName & "' AND TaskID = 1") Then
strSQL = "UPDATE tblTaskInfo SET TaskStart = Forms!frmLogEntry!SignedDate WHERE [ClientName] = '" & Forms!frmLogEntry!ClientName & "' AND [TaskID] = 1"

Open in new window

0
JPDeMossAuthor Commented:
Yes I've also tried that, but it seems the error is coming from Line 1 in the DLookup and not the SQL Update. I did copy your snipped into my code, and still return Error 13 Mismatch.
0
IrogSintaCommented:
Try using the Nz function:
DLookup("[ClientName]", "tblTaskInfo", "[ClientName] = '" & Nz(Forms!frmLogEntry!ClientName) & "' AND TaskID = 1")

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

JPDeMossAuthor Commented:
Putting the Nz function on the string still returned the Error 13 Mismatch, but like my original DLookup works fine in the Immediate window.
0
IrogSintaCommented:
Are you certain the data type of TaskID  in your tblTaskInfo table is a Number data type?
0
IrogSintaCommented:
Hang on a second.  i just realized that your DLookup doesn't make sense.  Why would you lookup of the ClientName where the ClientName is the Client name?
0
JPDeMossAuthor Commented:
Yes. I double checked the data type for TaskID is Number and the data type for ClientName is text. What's really getting me is that if I replace ClientName with ClientNo the function works like I would want it too, except that since on some records ClientNo may be null for a period of time I can't rely on it for the updating that I need. Here's an example of the working function.
If DLookup("[ClientNo]", "tblTaskInfo", "[ClientNo] = Forms!frmLogEntry!ClientNo and TaskID = 2") Then
strSQL = "UPDATE tblTaskInfo SET TaskStart = Forms!frmLogEntry!EmailDate WHERE [ClientNo] = Forms!frmLogEntry!ClientNo AND [TaskID] = 2"
StrSQL2 = "Update tblTaskInfo SET TaskEnd = Forms!frmLogEntry!EmailDate WHERE [ClientNo] = Forms!frmLogEntry!ClientNo AND [TaskID]=1"

Open in new window

0
JPDeMossAuthor Commented:
I am checking if a record exists in the tblTaskInfo and if it does I update it from frmLogEntry which is tied to another table called tblClientInfo. The Else statement here creates the record if it does not exist. Here's the full code for one of the fields After_Update

If DLookup("[ClientNo]", "tblTaskInfo", "[ClientNo] = Forms!frmLogEntry!ClientNo and TaskID = 2") Then
strSQL = "UPDATE tblTaskInfo SET TaskStart = Forms!frmLogEntry!EmailDate WHERE [ClientNo] = Forms!frmLogEntry!ClientNo AND [TaskID] = 2"
StrSQL2 = "Update tblTaskInfo SET TaskEnd = Forms!frmLogEntry!EmailDate WHERE [ClientNo] = Forms!frmLogEntry!ClientNo AND [TaskID]=1"
Else
strSQL = "INSERT INTO tblTaskInfo (ClientNo, ClientName, TaskStart, TaskID) VALUES (Forms!frmLogEntry!ClientNo, Forms!frmLogEntry!ClientName, Forms!frmLogEntry!EmailDate, 2)"
StrSQL2 = "Update tblTaskInfo SET TaskEnd = Forms!frmLogEntry!EmailDate WHERE [ClientNo] = Forms!frmLogEntry!ClientNo AND [TaskID] = 1"
End If

DoCmd.RunSQL strSQL
DoCmd.RunSQL StrSQL2

Open in new window

0
IrogSintaCommented:
I see.  The reason for the error is because you're missing a comparison that would return True or False (-1 or 0).  ClientNo returns a number which if not 0 would be interpreted as True.  DLookup of a ClientName returns a string which cannot be understood as True or False, hence your error.  The way to check if it exists with DLookup would be:

If DLookup("[ClientNo]", "tblTaskInfo", "[ClientNo] = Forms!frmLogEntry!ClientNo and TaskID = 2") <> "" Then
If DLookup("[ClientName]", "tblTaskInfo", "[ClientName] = Forms!frmLogEntry!ClientName AND TaskID = 1") <> "" Then

You can also use DCount:
If DCount("*", "tblTaskInfo", "[ClientName] = Forms!frmLogEntry!ClientName AND TaskID = 1") > 0 Then
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JPDeMossAuthor Commented:
Thank you so much. I am new to Access and VBA and I will defiantly be keeping this answer around for future cases where I have to compare against text and not a number. Also thank you for taking the time to walk through this problem with me. You've provided with some invaluable knowledge.
0
IrogSintaCommented:
You're welcome.

Ron
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.