Re: DLookup Function

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


"Al Campagna" <alcampagna@msnewsgroups> wrote in message
news:uAVN8RfdHHA.284@xxxxxxxxxxxxxxxxxxxxxxx
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."

"Duane" <skydiver_2@xxxxxxxxx> wrote in message
news:eMAp5VZdHHA.4684@xxxxxxxxxxxxxxxxxxxxxxx
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.



"Al Campagna" <alcampagna@msnewsgroups> wrote in message
news:OLfH3DYdHHA.4032@xxxxxxxxxxxxxxxxxxxxxxx
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" <skydiver_2@xxxxxxxxx> wrote in message
news:uu0IN0XdHHA.5044@xxxxxxxxxxxxxxxxxxxxxxx
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...









.

Quantcast