Solved

Getting Datatype Mismatch in my DLookup

Posted on 2011-03-01
6
331 Views
Last Modified: 2012-05-11
I have the following DLookup and I keep a "Datatype Mismatch" error.

Me.Possible_Score = DLookup("[Possible Score]", "tblEnrollment_QualCriteriaReview", "[Subcode] = '" & Left([cboQualRevCriteria], 5) & "' And [Region] = '" & [Region] & "'")

What is wrong?

Thanks,

gdunn59
0
Comment
Question by:gdunn59
6 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
Try to change [Subcode] =
to
[Subcode] Like
0
 
LVL 7

Expert Comment

by:RemRemRem
Comment Utility
That error usually means you're getting a mismatch between, say, a numeric value and a text value. For example, is the [Subcode] field numeric and the first 5 characters are supposed to be, but perhaps include something that has an "o" instead of a zero in it?

-Rachel
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
Your

 Left([cboQualRevCriteria], 5

is that string data or number data?

If it is a number then use CInt( Left([cboQualRevCriteria], 5)
if its alphanumeric try Chr(34) &  Left([cboQualRevCriteria], 5) & chr(34)

chr(34) is quotation marks "

I like Chr(34) & somestringvariable & chr(34) better than trying to get double sets of quotes to work.
Less frustration and easier to troubleshoot
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Accepted Solution

by:
gdunn59 earned 0 total points
Comment Utility
I couldn't get it to work with any of the suggestions, so I just tried a different route.

Thanks everyone.

gdunn59
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@gdunn59

It's ok if you went a different route, but post up what you did do.
That way everyone who may happen to google across your question finds an answer.

Nick67
0
 

Author Closing Comment

by:gdunn59
Comment Utility
None of the other solutions worked, so I tried something else.  Thanks everyone for your assistance though!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now