DLookup pull from another table

[alexasha]

I have two tables connected by Medical Record number.
I would like to pull DOB from Main table to Encounter form, that is based on
the encounter table.
I tried this
=DLookUp("DOB","[Main]","[Medical Record] = " &
Forms("Encounter_New")("[Medical Record]"))
but it does not work.
Please help.

 

[Allen Browne]

If you open the table in design view, what data type is the [Medical Record]
field?

If Number, try:
=DLookUp("DOB","Main","[Medical Record] = " & Nz([Medical Number],0))

If Text:
=DLookUp("DOB","Main","[Medical Record] = """ & [Medical Number] & """"))

I'm assuming that this expression is on the form named [Encounter New]. If
you are looking on another form to get the [Medical Number], you will need
the longer expression, e.g.:
=DLookUp("DOB","Main","[Medical Record] = " &
Nz(Forms![Encounter New]![Medical Number],0))

(Make sure the underscores and spaces match exactly the way your forms and
fields are named.)

For an explanation, here's info on the basics of DLookup():
http://allenbrowne.com/casu-07.html
and here's an explanation of the extra quotes for the text field:
http://allenbrowne.com/casu-17.html

 

[Beetle]

If your Medical Record number field is a number data type then;

=DLookUp("DOB","[Main]","[Medical Record] = " & [Medical Record])

If it is a text data type then;

=DLookUp("DOB","[Main]","[Medical Record] = """ & [Medical Record]) & """")

 

[alexasha]

None of the expressions in either post work.
DOB is date of birth and set up as a date/time field type. Should I change
it to text or do I need to adjust my formula for it.
Main is the main database.
Encounter is the secondary database
Encounter_new is the form for encounter that I would like to add this new
field to pull date of birth from Main table.
Please help. I am stuck on this.

If your Medical Record number field is a number data type then;

=DLookUp("DOB","[Main]","[Medical Record] = " & [Medical Record])

If it is a text data type then;

=DLookUp("DOB","[Main]","[Medical Record] = """ & [Medical Record]) & """")

--
_________

Sean Bailey

I have two tables connected by Medical Record number.
I would like to pull DOB from Main table to Encounter form, that is based on
the encounter table.
I tried this
=DLookUp("DOB","[Main]","[Medical Record] = " &
Forms("Encounter_New")("[Medical Record]"))
but it does not work.
Please help.

 

[Pete D.]

If medical record number is linked to encounter TABLE by medical record
number then I would base the form on a query that already includes this
information. No reason to record the DOB in every encounter record as you
already have it. Also if you correct the DOB later due to data error it
will be correct in all form views.

 

[alexasha]

My reports are based on encounter form. I have to have DOB on my report. So
far, I copy DOB from Main database to Encounter. But I would like to have an
easier solution.

If medical record number is linked to encounter TABLE by medical record
number then I would base the form on a query that already includes this
information. No reason to record the DOB in every encounter record as you
already have it. Also if you correct the DOB later due to data error it
will be correct in all form views.

I have two tables connected by Medical Record number.
I would like to pull DOB from Main table to Encounter form, that is based
on
the encounter table.
I tried this
=DLookUp("DOB","[Main]","[Medical Record] = " &
Forms("Encounter_New")("[Medical Record]"))
but it does not work.
Please help.

 

[Pete D.]

Reports are based on tables and quryies using those tables. Your reports
should be based on a query using both tables if in fact your link between
the tables is as you say. If you start with John and add an encounter then
all data previosly colected from John should be linked to the event. You
seem (if your link/relationship is as you say) to understand relational data
but you are not using the power. If I have a zip code and related data I'll
never ever have to type in town again. When I type in that zip the records
are related and the persons address will have the town and zip already. In
other words, I have an encounter, Bill has been here before. I open a new
encounter record and select Bill. His DOB, Address, Sex, Previous encounter
records all show up on my form/subform as it is pulled from the orginal
encounter record and personnel record created. Search google for relational
database, normalizeation. Your working too hard to pull this together.

My reports are based on encounter form. I have to have DOB on my report.
So
far, I copy DOB from Main database to Encounter. But I would like to have
an
easier solution.

If medical record number is linked to encounter TABLE by medical record
number then I would base the form on a query that already includes this
information. No reason to record the DOB in every encounter record as
you
already have it. Also if you correct the DOB later due to data error it
will be correct in all form views.

I have two tables connected by Medical Record number.
I would like to pull DOB from Main table to Encounter form, that is
based
on
the encounter table.
I tried this
=DLookUp("DOB","[Main]","[Medical Record] = " &
Forms("Encounter_New")("[Medical Record]"))
but it does not work.
Please help.

 

[alexasha]

I am getting # error

None of the expressions in either post work.
DOB is date of birth and set up as a date/time field type. Should
I change it to text or do I need to adjust my formula for it.
Main is the main database.
Encounter is the secondary database
Encounter_new is the form for encounter that I would like to add
this new field to pull date of birth from Main table.
Please help. I am stuck on this.

When you say "Don't work", do you get an error message in the
textbox, the wrong date in the textbox, or what?

No need to change the formula because you are returning a date
datatype.

Q

If your Medical Record number field is a number data type then;

=DLookUp("DOB","[Main]","[Medical Record] = " & [Medical Record])

If it is a text data type then;

=DLookUp("DOB","[Main]","[Medical Record] = """ & [Medical
Record]) & """")

--
_________

Sean Bailey


:

I have two tables connected by Medical Record number.
I would like to pull DOB from Main table to Encounter form,
that is based on the encounter table.
I tried this
=DLookUp("DOB","[Main]","[Medical Record] = " &
Forms("Encounter_New")("[Medical Record]"))
but it does not work.
Please help.

 

[alexasha]

I entirely agree. I created this database couple years ago and did not use it
for about a year, so I already forgot some design details.
I have created a lot of highly customized forms and reports and do not want
to lose it. I also have a lot of code inserted in the forms, that will be
hard and time consuming to reproduce.
I need very simple solution

 

[Allen Browne]

Take a deep breath, and re-read the earlier posts.

Check the data types are right, and apply the corrections suggested.

We cannot debug it for you.