Help with Dlookup and Mid

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • basstwo@gmail.com
    #1

    Help with Dlookup and Mid

    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?

    Tags: None
    • Bas Cost Budde
      #2
      Re: Help with Dlookup and Mid

      No, it means your control has the same name as the field it tries to
      calculate. Change the name.

      basstwo@gmail.c om wrote:
      [color=blue]
      > 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?
      >[/color]

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


        Comment

        • Randy Harris
          #3
          Re: Help with Dlookup and Mid


          <basstwo@gmail. com> wrote in message
          news:1119900749 .371170.38030@g 44g2000cwa.goog legroups.com...[color=blue]
          > 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?[/color]

          Is "SerialNumb er" a text or numeric field?

            Comment

            • fredg
              #4
              Re: Help with Dlookup and Mid

              On 27 Jun 2005 12:32:29 -0700, basstwo@gmail.c om wrote:
              [color=blue]
              > 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?[/color]

              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.

                Comment

                • basstwo@gmail.com
                  #5
                  Re: Help with Dlookup and Mid

                  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

                    Comment

                    Previous template Next
                    Working...