Data Mismatch in DLookUp Stmt - Microsoft Access Forum


	Back to Access Forum Comments List

Data Mismatch in DLookUp Stmt

Kent Jamison

4 years ago

I'm trying to create a report (InboundPersonnelR), which has a field I'm trying to pull from my Inbound Personnel Form (InboundPersonnelF), where I have a ComboBox (SponsorComboBox) for picking a Sponsor's last name. The value of that ComboBox (a number, type long-integer) is stored InboundPersonnelT, in the field Sponsor. However, in my report, I don't want just a number to show up. I want the actual last name of the sponsor to show up just like it does in my SponsorComboBox in InboundPersonnelF,
When I write the DLookUp statement:
Sponsor: DLookUp("LastName","CurrentOI-GIPersonnelT","CurrentPersonnelID="" & Sponsor"""), I get a data mismatch error. However, both CurrentPersonelID and Sponsor are AutoNumber and Number fields, respectively. Both are number type long-integer.
What am I doing wrong?

	Adam Schwanz @Reply 4 years ago
Your quotations. Concatenation

	Adam Schwanz @Reply 4 years ago
Double Double QuotesDLookup You're also missing the other & symbol

	Adam Schwanz @Reply 4 years ago
If both are numbers you don't need any of that. Just `"CurrentPersonnelID=" & Sponsor)`

Scott Axton @Reply

4 years ago

As the guys said, the problem is in the quotes. You are converting the Sponsor to text the way you have it.
Just because you have a dash in your field name - CurrentOI-GIPersonnelT - I would place that in brackets to prevent issues with Access thinking you are trying to subtract two parts of the field.

So [CurrentOI-GIPersonnelT].

Depending how far along in your project you are you might want to reconsider your field naming to follow Richards recommendations. Letters and numbers only - no special characters.

Kent Jamison OP @Reply

4 years ago

Adam, Scott,
I wasn't tracking no special characters in Rost's field naming conventions, just no spaces, no numbers and use CamelCase.  I haven't had any issues to date. But no issues with converting over.  Only a few table names to change.
I've rewrote the DLookUp to Sponsor: DLookUp("LastName","[CurrentOI-GIPersonnelT]","CurrentPersonnelID=" & [Sponsor]), per your suggestions above.  Then I get a circular logic error from the Alias, which I changed to SponsorLastName.  And Voila!  It works!  Awesome, guys!  Thanks for the assist!
FYSA: I've watched that Double Double Quote video several times after I took lessons Expert 10 & 11.  The concepts still makes my head spin.  I'll rewatch them now I have some more experience under my belt.  Maybe it will click this time.  I do understand simple concatenation, which is not much different than what is commonly used in Excel and I use frequently. It's the swapping-code-in-and-out-of-the-expression part that confuses me.
Thanks again.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen