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

Help with Dlookup and Mid

P: n/a
I have a field with a serial number in it. I want to use Mid to
extract the 4th and 5th characters, use them to lookup a value on a
small lookup table, and use the info from that table to fill in the
value for my lens field. I tried doing this:

=DLookUp([LensLookup]![LensName],[LensLookup],Mid([Camera Data]![Serial
Number],4,2)=[LensLookup]![LensID])

Where LensLookup is my lookup table, LensName is the value I want to
have appear

I will get the 4th and 5th characters from Serial Number and use them
to find the corrsponding numbers in the LensID on the LensLookup table.
I know that the two characters will be there. But I keep getting an
#Name? error. Is it a syntax problem or do I have my tables messed up?

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
No, it means your control has the same name as the field it tries to
calculate. Change the name.

ba*****@gmail.com wrote:
I will get the 4th and 5th characters from Serial Number and use them
to find the corrsponding numbers in the LensID on the LensLookup table.
I know that the two characters will be there. But I keep getting an
#Name? error. Is it a syntax problem or do I have my tables messed up?


--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #2

P: n/a

<ba*****@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
I have a field with a serial number in it. I want to use Mid to
extract the 4th and 5th characters, use them to lookup a value on a
small lookup table, and use the info from that table to fill in the
value for my lens field. I tried doing this:

=DLookUp([LensLookup]![LensName],[LensLookup],Mid([Camera Data]![Serial
Number],4,2)=[LensLookup]![LensID])

Where LensLookup is my lookup table, LensName is the value I want to
have appear

I will get the 4th and 5th characters from Serial Number and use them
to find the corrsponding numbers in the LensID on the LensLookup table.
I know that the two characters will be there. But I keep getting an
#Name? error. Is it a syntax problem or do I have my tables messed up?


Is "SerialNumber" a text or numeric field?

Nov 13 '05 #3

P: n/a
On 27 Jun 2005 12:32:29 -0700, ba*****@gmail.com wrote:
I have a field with a serial number in it. I want to use Mid to
extract the 4th and 5th characters, use them to lookup a value on a
small lookup table, and use the info from that table to fill in the
value for my lens field. I tried doing this:

=DLookUp([LensLookup]![LensName],[LensLookup],Mid([Camera Data]![Serial
Number],4,2)=[LensLookup]![LensID])

Where LensLookup is my lookup table, LensName is the value I want to
have appear

I will get the 4th and 5th characters from Serial Number and use them
to find the corrsponding numbers in the LensID on the LensLookup table.
I know that the two characters will be there. But I keep getting an
#Name? error. Is it a syntax problem or do I have my tables messed up?


1) Your syntax, for one thing, is incorrect.
Each argument in the DLookUp must be a string.

It is unclear to me if "Camera Data" is the name of the form this code
is on, or a table name. It should be the name of the form, and
[Serial] is a field on this form.
Try:

=DLookUp(["[LensName]","[LensLookup]","[LensID] = " & Mid([Serial
Number],4,2)

The above assumes the [LensID] field is a Number datatype.

Look up DLookUp in VBA Help.
Also look up
Where clause + Restrict data to a subset of records.

2) Also make sure that the name of this control is not the same as the
name of any field used in it's control source expression.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #4

P: n/a
I got my problem fixed. Here is what I have now:

=DLookUp("[LensLookup]![LensName]","[LensLookup]","[LensLookup]![LensID]=
Mid ([Serial Number] , 4, 2) ")

It works great!

Now...new problem...new post

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.