> <picky>
> Both Keith and Stefan missed the fact that you've declared test as a String.
>
> The DLookup function will return Null if there is no match, and Null values
> cannot be assigned to String variables. (The only data type that can be
> assigned a Null value is the Variant).
>
> You either need to redefine test as Variant, or else wrap the Nz value
> around your DLookup call to set it to a string value if Null is returned.
>
> Incidentally, Keith's answer was "exagerated" so that you could see the
> difference between the single quotes and double quotes. In actual use, you
> would NOT have the spaces. In other words, instead of
>
> test = DLookup([both], "detail", "[both] = ' " & Me.both & " ' ")
>
> you'd actually use
>
> test = DLookup([both], "detail", "[both] = '" & Me.both & "'")
>
> Stefan's answer is safer: it will handle cases where there's an apostrophe
> in what's in Me!Both. Exagerated for clarity, he had
>
> test = DLookup("[both]", _
> "detail", _
> "[both] = ' " & Replace(Me![both], " ' ", " ' ' ") & " ' ")
>
> Incorporating my suggestion, you should use
>
> test = Nz(DLookup("[both]", _
> "detail", _
> "[both] = '" & Replace(Me![both], "'", "''") & "'"), "No
> Value")
>
> </picky>
>
>
>
> --
> Doug Steele, Microsoft Access MVP
>
http://I.Am/DougSteele> (no e-mails, please!)
>
>
> "tina" <tina[ at ]discussions.microsoft.com> wrote in message
> news:005C6262-8799-4FD8-B4F1-5D496A37943C[ at ]microsoft.com...
> > Hi
> > I am trying to understand dlookup I am used to vlookup in excel but can't
> > seem to get it in access
> > I would like to lookup field both in form data and see if it is in table
> > detail in field also called both
> > both are text fields
> > Dim test As String
> > test = DLookup([both], "detail", [both] = Me.both)
> > If IsNull(test) Then
> > MsgBox -"your lot number is wrong"
> > I did get some help when trying to lookup to fields but to try and make
> > things simplier i am now trying this .I have confused myself about Dlookup
> > Thanks
> > Tina
> > End If
>
>
>