Dlookup Help Please

[Dic_nutana]

Hi hope you can help I am having problems with dlookup.

I’m using access 2000 and I’m trying to retrieve the ‘email’ address
contained in a table called ‘contacts’ by using two fields i.e. ‘last name’
and ‘first name’ as the criteria, which the end user enters onto a form

I tried referencing just one field but as you know this only returns to
first occurrence of the ‘last name’ found,
I used this code :-
Private Sub eryc_rep_last_AfterUpdate()
[email eryc] = DLookup("", "Contacts", "[surname] = [ERYC Rep Last] ")
End Sub

which worked as far as it goes.

I then tried this :-
Private Sub eryc_rep_last_AfterUpdate()
[email eryc] = DLookup("[email]", "Contacts", "[surname] = ' " & [eryc rep
last] & " ' And [FirstName]= ' " & [ERYC Rep first])
End Sub

but it fails and I not sure where I have gone wrong and where to go from here.

Thanks in advance

 

[Keith Wilby]

I then tried this :-
Private Sub eryc_rep_last_AfterUpdate()
[email eryc] = DLookup("", "Contacts", "[surname] = ' " & [eryc rep
last] & " ' And [FirstName]= ' " & [ERYC Rep first])
End Sub

but it fails and I not sure where I have gone wrong and where to go from
here.
[/QUOTE]

You need a closing quotation mark at the end:

" ' And [FirstName] = ' " & [ERYC Rep first] & " ' "

Keith.
www.keithwilby.co.uk

 

[fredg]

Hi hope you can help I am having problems with dlookup.

I¢m using access 2000 and I¢m trying to retrieve the ¡email¢ address
contained in a table called ¡contacts¢ by using two fields i.e. ¡last name¢
and ¡first name¢ as the criteria, which the end user enters onto a form

I tried referencing just one field but as you know this only returns to
first occurrence of the ¡last name¢ found,
I used this code :-
Private Sub eryc_rep_last_AfterUpdate()
[email eryc] = DLookup("", "Contacts", "[surname] = [ERYC Rep Last] ")
End Sub

which worked as far as it goes.

I then tried this :-
Private Sub eryc_rep_last_AfterUpdate()
[email eryc] = DLookup("[email]", "Contacts", "[surname] = ' " & [eryc rep
last] & " ' And [FirstName]= ' " & [ERYC Rep first])
End Sub

but it fails and I not sure where I have gone wrong and where to go from here.

Thanks in advance[/QUOTE]

[email eryc] = DLookup("[email]", "Contacts", "[surname] = ' " & [eryc
rep last] & " ' And [FirstName]= ' " & [ERYC Rep first & " ' ")

Which should work, unless one of the names includes an apostrophe,
i.e. O'Connor.

Better would be:
[email eryc] = DLookup("[email]", "Contacts", "[surname] = " " " &
[eryc rep last] & " " " And [FirstName]= " " " & [ERYC Rep first & "
" " ")
Which should work as long as neither name includes a double quote.
Of course, remove the spaces between the quotes.

Now what will you do if you have more than one person in the list with
the same first and last name?

You should be using the record's unique prime key [ID] field to get
the correct person's name, not the last and first names.

Assuming the ID field is a Number datatype:

[email eryc] = DLookup("[email]", "Contacts", "[ID] = " & [ID])

 

[Dic_nutana]

Many thanks Keith thats got it fixed now

I then tried this :-
Private Sub eryc_rep_last_AfterUpdate()
[email eryc] = DLookup("", "Contacts", "[surname] = ' " & [eryc rep
last] & " ' And [FirstName]= ' " & [ERYC Rep first])
End Sub

but it fails and I not sure where I have gone wrong and where to go from
here.
[/QUOTE]

You need a closing quotation mark at the end:

" ' And [FirstName] = ' " & [ERYC Rep first] & " ' "

Keith.
www.keithwilby.co.uk
[/QUOTE]

 

[Dic_nutana]

Thanks for the info fredg but the enduser does not have that information they
only input the name.

Hi hope you can help I am having problems with dlookup.

Iʼm using access 2000 and Iʼm trying to retrieve the ʽemailʼ address
contained in a table called ʽcontactsʼ by using two fields i.e. ʽlast nameʼ
and ʽfirst nameʼ as the criteria, which the end user enters onto a form

I tried referencing just one field but as you know this only returns to
first occurrence of the ʽlast nameʼ found,
I used this code :-
Private Sub eryc_rep_last_AfterUpdate()
[email eryc] = DLookup("", "Contacts", "[surname] = [ERYC Rep Last] ")
End Sub

which worked as far as it goes.

I then tried this :-
Private Sub eryc_rep_last_AfterUpdate()
[email eryc] = DLookup("[email]", "Contacts", "[surname] = ' " & [eryc rep
last] & " ' And [FirstName]= ' " & [ERYC Rep first])
End Sub

but it fails and I not sure where I have gone wrong and where to go from here.

Thanks in advance[/QUOTE]

[email eryc] = DLookup("[email]", "Contacts", "[surname] = ' " & [eryc
rep last] & " ' And [FirstName]= ' " & [ERYC Rep first & " ' ")

Which should work, unless one of the names includes an apostrophe,
i.e. O'Connor.

Better would be:
[email eryc] = DLookup("[email]", "Contacts", "[surname] = " " " &
[eryc rep last] & " " " And [FirstName]= " " " & [ERYC Rep first & "
" " ")
Which should work as long as neither name includes a double quote.
Of course, remove the spaces between the quotes.

Now what will you do if you have more than one person in the list with
the same first and last name?

You should be using the record's unique prime key [ID] field to get
the correct person's name, not the last and first names.

Assuming the ID field is a Number datatype:

[email eryc] = DLookup("[email]", "Contacts", "[ID] = " & [ID])
[/QUOTE]

 

[John W. Vinson]

Thanks for the info fredg but the enduser does not have that information they
only input the name.

I once worked in the same building as Dr. Lawrence David Wise, Ph.D. and Dr.
Lawrence David Wise, Ph.D. Larry was tall, blond and affable; L. David was
stocky, dark and taciturn. How will your end user handle such a situation?

Names ARE NOT UNIQUE and no database should be based on the assumption that
they are!

 

[Dic_nutana]

Thanks for that John, my skills at this game are still in their infancy, but
I take your point and agree, but I must admit, at first I thought that you
where… well … enough said!

I have thought harder about a solution and have identified a way I can
circum navigate the problem of the end-user not knowing the unique name ID
(the name is all that is available to them) and I have identified one
solution that they could use, although not as convenient as just entering the
name, the desired outcome can be achieved.

So thanks for making me think more about the solution and increasing my
knowledge, but rather than lecture newbes, it would be better to give some
constructive direction/suggestions

 

[John W. Vinson]

I have thought harder about a solution and have identified a way I can
circum navigate the problem of the end-user not knowing the unique name ID
(the name is all that is available to them) and I have identified one
solution that they could use, although not as convenient as just entering the
name, the desired outcome can be achieved.

The user should not in fact need to ever know or see the unique ID. Typically
the user will use a Combo Box, which *stores* the unique ID, while it
*displays* the name - along with whatever other information is available to
disambiguate the name, such as the employee's department, or phone number, or
birthdate, or whatever.

So thanks for making me think more about the solution and increasing my
knowledge, but rather than lecture newbes, it would be better to give some
constructive direction/suggestions

My apologies, that did come across a lot snarkier than I intended.

 

[Dic_nutana]

Thats OK no problem, have a great day

Regards

Dic