DLookup Function

[Duane]

I am trying to lookup the value of a field in another table. The table is a
linked table in a database which I didn't design, so I am working with what
I have.

Reference Table:
Table: Prisoners
Target Field: Position - Text Field
Key Field for Reference: Number - Text Field. I know, it say "Number", but
it is a text field.

Calling Form
frmDataEntry
Text box - Assignment
Text box - Number Same value as the Key Field in table Prisoners.

In the assingment text box I have the following as its control source
=DLookup("Position", "Prisoners", "Number =" & '" &
Forms!frmDataEntry!Number & "'")

I have changed things around a bit, but everything I have tried returns and
#Error

Any help woud be appreciated.

Thanks in advance...

 

[Al Campagna]

Duane,
First, don't use "Number" as the name of a field. It's a reserved word in Access, and
will cause no end of problems.
Try something like PrisonerNo, or PNo, or etc...

=DLookup("[Position]", "Prisoners", "PrisonerNo = '" & PrisonerNo & "'")

(expanded for clarity to show quotes... don't use)
=DLookup("[Position]", "Prisoners", "PrisonerNo = ' " & PrisonerNo & " ' ")
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Duane]

Thank you for your response.

I might not be understanding you correctly and/or the DLookup function. I
can change the field in my local table from Number to PrisonerNo and I can
change the Name of the Text Box on the form to PrisonerNo, but I don't have
any control over the field name in the linked table ([Prisoners].[Number]).

Please correct me if I am wrong, the first argument "Position" is the field
I am looking for. "Prisoners" is the linked table. The first PrisonerNo is
the Keyed Field in the linked table, which I cannot change. The second
"PrisonerNo" is the field in my local table OR the name of the control on
the form???

Once again, Thanks in advance.

Duane,
First, don't use "Number" as the name of a field. It's a reserved word
in Access, and will cause no end of problems.
Try something like PrisonerNo, or PNo, or etc...

=DLookup("[Position]", "Prisoners", "PrisonerNo = '" & PrisonerNo & "'")

(expanded for clarity to show quotes... don't use)
=DLookup("[Position]", "Prisoners", "PrisonerNo = ' " & PrisonerNo & " '
")
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I am trying to lookup the value of a field in another table. The table is
a linked table in a database which I didn't design, so I am working with
what I have.

Reference Table:
Table: Prisoners
Target Field: Position - Text Field
Key Field for Reference: Number - Text Field. I know, it say "Number",
but it is a text field.

Calling Form
frmDataEntry
Text box - Assignment
Text box - Number Same value as the Key Field in table Prisoners.

In the assingment text box I have the following as its control source
=DLookup("Position", "Prisoners", "Number =" & '" &
Forms!frmDataEntry!Number & "'")

I have changed things around a bit, but everything I have tried returns
and #Error

Any help woud be appreciated.

Thanks in advance...

 

[Al Campagna]

Duane,
Check out the DMax function in Help. It explains all the arguments in the DMax
syntax.

DMax(SomeValue, FromSomeTable, WhereSomeExpression)
The Where in out DMax...
"PrisonerNo = '" & PrisonerNo & "'")
says in words...
"Where the field name value (in my table) is equal to the field name value (on my
open form)"

However, I can not guarantee correct functioning if you do not change the table field
name [Number] to something else. It may work... it may not.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thank you for your response.

I might not be understanding you correctly and/or the DLookup function. I can change
the field in my local table from Number to PrisonerNo and I can change the Name of the
Text Box on the form to PrisonerNo, but I don't have any control over the field name in
the linked table ([Prisoners].[Number]).

Please correct me if I am wrong, the first argument "Position" is the field I am looking
for. "Prisoners" is the linked table. The first PrisonerNo is the Keyed Field in the
linked table, which I cannot change. The second "PrisonerNo" is the field in my local
table OR the name of the control on the form???

Once again, Thanks in advance.

Duane,
First, don't use "Number" as the name of a field. It's a reserved word in Access,
and will cause no end of problems.
Try something like PrisonerNo, or PNo, or etc...

=DLookup("[Position]", "Prisoners", "PrisonerNo = '" & PrisonerNo & "'")

(expanded for clarity to show quotes... don't use)
=DLookup("[Position]", "Prisoners", "PrisonerNo = ' " & PrisonerNo & " ' ")
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I am trying to lookup the value of a field in another table. The table is a linked
table in a database which I didn't design, so I am working with what I have.

Reference Table:
Table: Prisoners
Target Field: Position - Text Field
Key Field for Reference: Number - Text Field. I know, it say "Number", but it is a
text field.

Calling Form
frmDataEntry
Text box - Assignment
Text box - Number Same value as the Key Field in table Prisoners.

In the assingment text box I have the following as its control source
=DLookup("Position", "Prisoners", "Number =" & '" & Forms!frmDataEntry!Number & "'")

I have changed things around a bit, but everything I have tried returns and #Error

Any help woud be appreciated.

Thanks in advance...

 

[Duane]

Thanks Al. I got it to work, and it did work with the field in the ohter
table being [Number].

Duane,
Check out the DMax function in Help. It explains all the arguments in
the DMax syntax.

DMax(SomeValue, FromSomeTable, WhereSomeExpression)
The Where in out DMax...
"PrisonerNo = '" & PrisonerNo & "'")
says in words...
"Where the field name value (in my table) is equal to the field name
value (on my open form)"

However, I can not guarantee correct functioning if you do not change
the table field name [Number] to something else. It may work... it may
not.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thank you for your response.

I might not be understanding you correctly and/or the DLookup function.
I can change the field in my local table from Number to PrisonerNo and I
can change the Name of the Text Box on the form to PrisonerNo, but I
don't have any control over the field name in the linked table
([Prisoners].[Number]).

Please correct me if I am wrong, the first argument "Position" is the
field I am looking for. "Prisoners" is the linked table. The first
PrisonerNo is the Keyed Field in the linked table, which I cannot change.
The second "PrisonerNo" is the field in my local table OR the name of the
control on the form???

Once again, Thanks in advance.

Duane,
First, don't use "Number" as the name of a field. It's a reserved
word in Access, and will cause no end of problems.
Try something like PrisonerNo, or PNo, or etc...

=DLookup("[Position]", "Prisoners", "PrisonerNo = '" & PrisonerNo & "'")

(expanded for clarity to show quotes... don't use)
=DLookup("[Position]", "Prisoners", "PrisonerNo = ' " & PrisonerNo & " '
")
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I am trying to lookup the value of a field in another table. The table
is a linked table in a database which I didn't design, so I am working
with what I have.

Reference Table:
Table: Prisoners
Target Field: Position - Text Field
Key Field for Reference: Number - Text Field. I know, it say "Number",
but it is a text field.

Calling Form
frmDataEntry
Text box - Assignment
Text box - Number Same value as the Key Field in table Prisoners.

In the assingment text box I have the following as its control source
=DLookup("Position", "Prisoners", "Number =" & '" &
Forms!frmDataEntry!Number & "'")

I have changed things around a bit, but everything I have tried returns
and #Error

Any help woud be appreciated.

Thanks in advance...