By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,819 Members | 1,573 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,819 IT Pros & Developers. It's quick & easy.

DLookup expression as control source results in error

P: 20
I have a text box control on a form. I would like this text box to display a value from a table that is not the form's record source. To do this, I am attempting a DLookUp expression. The table that contains this target is called DateCodes. The field that I would like returned is called OutputDateCode. I would like the OutputDateCode that matches the CartonDateFormat for each record to be returned in the text box.For example, if CartonDateCode= 13, I would like the text box to return the OutputDateCode from record 13 in the DateCodes table. I cannot figure out what is wrong with the expression that I have written and was hoping someone might be able to help me pinpoint the problem. Here is what I have written:
Expand|Select|Wrap|Line Numbers
  1. =DLookUp(" [OutputDateCode]","[DateCodes]","[CatonDateFormat] =" & [Forms]![ProductionReportLandscape]![CartonDateFormat])
I am working in Microsoft Access 2010.
3 Weeks Ago #1

✓ answered by Seth Schrock

I do see a space between the double quote and the left square bracket for the field name (" [OUtputDateCode]"). That would cause a problem, but it could have just been a typo when entered in the thread.

You also have CatonDateFormat in the DLookup() function, but CartonDateCode (spelling difference) in the body of your post.

Also want to verify (not just assume) that the field CartonDateCode is a number field and not a text field.

Just some quick observations.

You may have done this, but please try to always copy and paste your code directly from the database and not try to retype it in the thread. Often errors are caused by typos and they are impossible to find if you are typing it correctly in the thread, but have it wrong in the database. Also, please use Code tags when posting code (the [CODE/] button above the text editor).

Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
I do see a space between the double quote and the left square bracket for the field name (" [OUtputDateCode]"). That would cause a problem, but it could have just been a typo when entered in the thread.

You also have CatonDateFormat in the DLookup() function, but CartonDateCode (spelling difference) in the body of your post.

Also want to verify (not just assume) that the field CartonDateCode is a number field and not a text field.

Just some quick observations.

You may have done this, but please try to always copy and paste your code directly from the database and not try to retype it in the thread. Often errors are caused by typos and they are impossible to find if you are typing it correctly in the thread, but have it wrong in the database. Also, please use Code tags when posting code (the [CODE/] button above the text editor).
3 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,470
Hi Kledki.

This may just sound like I'm being hyper-critical, but it's far more fundamental than that. Your attention to detail leaves a great deal to be desired. Three or four mistakes in just what you've posted as a question.

This is a problem when asking questions as it sends people off on wild goose chases. What's more important though, is that trying to code and design computer work without proper attention to detail will get you forever mixed up in unnecessary problems. Computer work requires attention to detail. It really isn't worth getting involved at all unless you can give that as a bare minimum.

For this you just need to review what you've done and makes sure you use the right names in the right places and I'm sure all will be fine. Seth's already pointed out a few places where you've gone astray. Quotes (') and Double-Quotes (") - Where and When to use them may help as we don't really know what type of data you're working with so you may need to change that but not sure.

The main point to take away from this though is about the approach to such work.

Best of luck.
3 Weeks Ago #3

P: 20
You're right, I misspoke when I said CartonDateCode, it should have been CartonDateFormat.

I deleted the space, but I am still getting an error in the text box when I switch over to form view.

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[OutputDateCode]","DateCodes","[CatonDateFormat] =" & [Forms]![ProductionReportLandscape]![CartonDateFormat])
3 Weeks Ago #4

P: 20
Thank you both for your help, I solved my own problem. I checked the function 100 times, but there was one typo I just wasn't seeing! I feel silly for stressing out about it now
3 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,470
Kledki:
I feel silly for stressing out about it now
If you're lucky you'll manage to overcome that approach just as you get too old to benefit from it :-D

We all do it. Even those of us old enough to know a lot better. It helps to learn the lesson that such problems are very often the results of a small mistake here or there though. When we start to panic we get a chance to stop and remind ourselves to check it over carefully before getting into too much of a panic. Where possible letting go of the stress first. Normally that's enough.

I'm guessing the problem was one that Seth highlighted in his post as your post #4 still includes :
Expand|Select|Wrap|Line Numbers
  1. "[CatonDateFormat] =" & 
All good that you have it resolved anyway.
3 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.