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.