Access 2007 DLOOKUP


J

JMD.Park

Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 
Ad

Advertisements

J

JMD.Park

This is the expression I am trying to use:=DLookUp("[Name]","[tbl
_DEALERS]","[ ID]=" & [Forms]![frmTRIP_TICKETS_ISSUED_TO]![ ID])
 
R

RonaldoOneNil

First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)
 
D

Daryl S

JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.
 
T

Tom van Stiphout

On Tue, 27 Apr 2010 06:13:01 -0700, JMD.Park

Why not use a dropdown with names? It would have 2 columns: a hidden
ID and a visible Name column.
"Name" is a reserved word. Rename the field to DealerName or some
such.

If you insist on DLookup: the ControlSource for the DealerName field
would be:
=DLookup("DealerName", "DEALER", "ID=" & Me.ID)

-Tom.
Microsoft Access MVP
 
G

gerjan

JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID
to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID
or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!

__________ Information from ESET Smart Security, version of virus
signature database 4436 (20090918) __________

The message was checked by ESET Smart Security.

http://www.eset.com


__________ Information from ESET Smart Security, version of virus signature database 4436 (20090918) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Ad

Advertisements

J

JMD.Park

When I use =DLookup("[BusinessName]","[ tbl_HISTORICAL_DEALERS]","[
SEAFOODID] = '" & Me. SEAFOODID & "'") I receive #Name? in the form field

When I use =DLookup("[BusinessName]","[ tbl_HISTORICAL_DEALERS]","[
SEAFOODID] = " & Me. SEAFOODID) I receive #Name? in the form field

What am I doing wrong?
Daryl S said:
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.

--
Daryl S


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 
J

JMD.Park

SEAFOODID is numeric

Daryl S said:
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.

--
Daryl S


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 
J

JMD.Park

When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?

RonaldoOneNil said:
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 
J

JMD.Park

When I use =DLookup("BusinessName ", " tbl_HISTORICAL_DEALERS ", " SEAFOODID
=" & Me. SEAFOODID) I receive #Name? in form field

I would use a drop down but there are hundreds of dealers.
Thanks for trying to help!
 
R

RonaldoOneNil

You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must contain the
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.

JMD.Park said:
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?

RonaldoOneNil said:
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 
Ad

Advertisements

J

JMD.Park

The names are as I have shown recently, sorry just trying to simplify. Guess
I just made it more difficult. The SEAFOODID is numeric.
Tried taking the spaces out
=DLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID = " &
SEAFOODID) still receiving #Name? in form field.

RonaldoOneNil said:
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must contain the
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.

JMD.Park said:
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?

RonaldoOneNil said:
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)


:

Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 
V

vvariety

The names are as I have shown recently, sorry just trying to simplify. Guess
I just made it more difficult. The SEAFOODID is numeric.
Tried taking the spaces out
=DLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID = " &
SEAFOODID) still receiving #Name? in form field.



RonaldoOneNil said:
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must containthe
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?
:
First of all, 'Name' is not a good word to use for a field as it isa
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)
:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!- Hide quoted text -

- Show quoted text -

based on the field names that you have provided you can try this.

=DLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =
me![SEAFOODID]")
 
J

John W. Vinson

When I use =DLookup("BusinessName ", " tbl_HISTORICAL_DEALERS ", " SEAFOODID
=" & Me. SEAFOODID) I receive #Name? in form field

I think you still have problems with blanks and brackets; and the Me. keyword
applies only in VBA code, not on forms. Try

=DLookup("BusinessName", "tbl_HISTORICAL_DEALERS",
"[SEAFOODID] = " & [SEAFOODID])
I would use a drop down but there are hundreds of dealers.
Thanks for trying to help!

So what? If it's just for display you'll be fine. A Combo can hold up to 65536
rows (not that I'd ever use one a tenth that size for user input). Depends on
how you're using it!
 
J

JMD.Park

Receive #Error in BusinessName field of form, SEAFOODID does not accept
characters
This =DLookUp("BusinessName","tbl_HISTORICAL_DEALERS","SEAFOODID =" &
[Me].[SEAFOODID]) accepts without errors but will not allow me to type in
SEAFOODID on the form and BusinessName gives #Name?
Any ideas?

vvariety said:
The names are as I have shown recently, sorry just trying to simplify. Guess
I just made it more difficult. The SEAFOODID is numeric.
Tried taking the spaces out
=DLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID = " &
SEAFOODID) still receiving #Name? in form field.



RonaldoOneNil said:
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must contain the
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.
"JMD.Park" wrote:
When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?
"RonaldoOneNil" wrote:
First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)
"JMD.Park" wrote:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!- Hide quoted text -

- Show quoted text -

based on the field names that you have provided you can try this.

=DLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =
me![SEAFOODID]")
.
 
J

JMD.Park

Receive #Error in BusinessName field of form, SEAFOODID does not accept
characters
NOT CHARACTERS, I MEANT NUMBERS

JMD.Park said:
Receive #Error in BusinessName field of form, SEAFOODID does not accept
characters
This =DLookUp("BusinessName","tbl_HISTORICAL_DEALERS","SEAFOODID =" &
[Me].[SEAFOODID]) accepts without errors but will not allow me to type in
SEAFOODID on the form and BusinessName gives #Name?
Any ideas?

vvariety said:
The names are as I have shown recently, sorry just trying to simplify. Guess
I just made it more difficult. The SEAFOODID is numeric.
Tried taking the spaces out
=DLookup("[BusinessName]","tbl_HISTORICAL_DEALERS ","SEAFOODID = " &
SEAFOODID) still receiving #Name? in form field.



:
You said the table was called Dealer. Is it Dealer or is it
tbl_Historical_dealers ?
You also said the ID was called ID. Is it ID or SEAFOODID ?
You also have spaces around tbl_Historical_Dealers - take these out.
Basically for it to work, the table tbl_Historical_Dealers must contain the
fields called BusinessName and SEAFOODID and SEAFOODID is a numeric data type.

:

When I use =DLookup("[BusinessName]"," tbl_HISTORICAL_DEALERS "," SEAFOODID =
" & SEAFOODID) I get #Name? in the form field
What am I doing wrong?

:

First of all, 'Name' is not a good word to use for a field as it is a
reserved word in Access. I suggest you change this field name.
You would put your DLookUp as the control source for the 'Name' field on
your form
=DLookup("[Name]","Dealer","ID = " & ID)

:

Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!- Hide quoted text -

- Show quoted text -

based on the field names that you have provided you can try this.

=DLookup("[BusinessName]"," [tbl HISTORICAL DEALERS] ","[SEAFOODID] =
me![SEAFOODID]")
.
 
Ad

Advertisements

J

JMD.Park

Which is text?

Daryl S said:
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.

--
Daryl S


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 
J

JMD.Park

Still will not allow me to type in SEAFOODID and BusinessName is #Name?


John W. Vinson said:
When I use =DLookup("BusinessName ", " tbl_HISTORICAL_DEALERS ", " SEAFOODID
=" & Me. SEAFOODID) I receive #Name? in form field

I think you still have problems with blanks and brackets; and the Me. keyword
applies only in VBA code, not on forms. Try

=DLookup("BusinessName", "tbl_HISTORICAL_DEALERS",
"[SEAFOODID] = " & [SEAFOODID])
I would use a drop down but there are hundreds of dealers.
Thanks for trying to help!

So what? If it's just for display you'll be fine. A Combo can hold up to 65536
rows (not that I'd ever use one a tenth that size for user input). Depends on
how you're using it!
 
J

John W. Vinson

Still will not allow me to type in SEAFOODID and BusinessName is #Name?

I don't understand.

This DLookUp is looking up a value from a table based on a (numeric) value in
a form. It has NOTHING TO DO with "typing in".

Back up a bit. What is the context? Is this an expression in the Control
Source of a Form textbox? What's the Recordsource for the form (post the SQL)?
Where are you typing? Why would you be typing in a numeric SEAFOODID anyway,
rather than concealing it and picking from a user-friendly combo box?
 
Ad

Advertisements

D

Daryl S

JMD -

If you are using this expression in a query (that is not within code behind
the form), then you need to provide the form name rather than use Me. I also
see a space between the Me. and SEAFOODID, so if this is in the form, you
might only have to remove the space.

If you are using this outside of the form (say in a query or report), then
you will use it like this (use your form name):

=DLookup("[BusinessName]","[ tbl_HISTORICAL_DEALERS]","[SEAFOODID] = " &
Forms![yourformname].[SEAFOODID])

--
Daryl S


JMD.Park said:
Which is text?

Daryl S said:
JMD.Park -

If you can, change the fieldname from Name to something else. Name is a
reserved word in Access, and this will cause you problems...

If you are looking up the Name, then you use the ID to look it up.
If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = " & Me.ID)

If the ID is numeric, then use this:
=DLookup("[Name]","[DEALER]","[ID] = '" & Me.ID & "'")

If you want the resulting name to be displayed in the Name field on the
form, then set the Control Source property of the name textbox to the
appropriate DLookup statement above.

--
Daryl S


JMD.Park said:
Can someone please help me with this expression? I am trying to use an ID to
bring up a Name in a form. The form is named ISSUE. The table that has the
ID's and Name is DEALER. The form and table fields are named exactly the
same. Is this where I am making my mistake? Do I put the Dlookup in the ID or
Name control source? I am new to this, so if someone could tell me what
fields and table names to put in the expression and where that would be
wonderful. Thank you in advance for your assistance in this matter!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookup and Nz 0
DLookUp 1
Dlookup error 4
DLOOKUP 2
Get a number from a table to a form. 0
DLookUp for an Alphanumeric Record 3
Dlookup 1
Problem with Dlookup function *12162008 2

Top