If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dlookup Help Please
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("[email]", "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 |
#2
|
|||
|
|||
Dlookup Help Please
"Dic_nutana" wrote in message
... 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. You need a closing quotation mark at the end: " ' And [FirstName] = ' " & [ERYC Rep first] & " ' " Keith. www.keithwilby.co.uk |
#3
|
|||
|
|||
Dlookup Help Please
On Wed, 12 Nov 2008 04:47:02 -0800, Dic_nutana wrote:
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("[email]", "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 [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]) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#4
|
|||
|
|||
Dlookup Help Please
Many thanks Keith thats got it fixed now
"Keith Wilby" wrote: "Dic_nutana" wrote in message ... 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. You need a closing quotation mark at the end: " ' And [FirstName] = ' " & [ERYC Rep first] & " ' " Keith. www.keithwilby.co.uk |
#5
|
|||
|
|||
Dlookup Help Please
Thanks for the info fredg but the enduser does not have that information they
only input the name. "fredg" wrote: On Wed, 12 Nov 2008 04:47:02 -0800, Dic_nutana wrote: 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("[email]", "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 [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]) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#6
|
|||
|
|||
Dlookup Help Please
On Thu, 13 Nov 2008 02:54:01 -0800, Dic_nutana
wrote: 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! -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Dlookup Help Please
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" wrote: On Thu, 13 Nov 2008 02:54:01 -0800, Dic_nutana wrote: 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! -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Dlookup Help Please
On Fri, 14 Nov 2008 07:35:01 -0800, Dic_nutana
wrote: 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. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Dlookup Help Please
Thats OK no problem, have a great day
Regards Dic "John W. Vinson" wrote: On Fri, 14 Nov 2008 07:35:01 -0800, Dic_nutana wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|