simple dlookup

[tina]

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

 

[Keith Wilby]

test = DLookup([both], "detail", [both] = Me.both)

Try

test = DLookup([both], "detail", "[both] = ' " & Me.both & " ' ")

Keith.

 

[Stefan Hoffmann]

hi Tina,

Dim test As String
test = DLookup([both], "detail", [both] = Me.both)

test = DLookup("[both]", _
"detail", _
"[both] = '" & Replace(Me![both], "'", "''") & "'")


mfG
--> stefan <--

 

[Douglas J. Steele]

<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>

 

[tina]

Thank you so much Douglas
I really need to spend some time learning syntex but needed this urgently vb
in excel is a lot easier

<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

(no e-mails, please!)

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

 

[Keith Wilby]

<picky>

Better to be picky than wrong

Keith.

 

[Mike Painter]

Thank you so much Douglas
I really need to spend some time learning syntex but needed this
urgently vb in excel is a lot easier

I don't thin vlookup is a vb function, it was in visicalc, the very first
spreadsheet.
One of my students wrote an entire payroll system in Visicalc in the early
80's on a TRS-80.
vlookup was used a lot for teh tax tables.
He kept asking for another problem.