Use Dlookup in my header

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here's one to get your heads around on a Friday!

In my database I have the following:

A table called tblCareNotes, which contains 2 fields: PartNumber, CareNote.
Partnumber is unique and will only appear in this table once.
A table called tblInspectionRecords with many fields, one of which is
PartNumber, and will be the same as the part number in the above table.

I have created a report (called rptInspectionRecords) that that uses a query
to retrieve all instances of a part number and list them. What I want to do
is add the CareNote field from the CareNotes table to the header of the
report as the contents of this field will apply to all records.

I have used an unbound textbox in the header and set its control source to:

DLookUp("[CareNote]","[tblCareNotes]","([PartNumber])=REPORTS![rptInspectionRecords]![PartNumber]")



But I get the following error message,

Syntax Error (Missing operator) in query expression


it also displays my Dlookup line, but it's slightyly different:

First([DLookUp("[CareNote]","[tblCareNotes]","([PartNumber])=REPORTS]![rptInspectionRecords]![[PartNumber]")])'


As you can see it's inserted some extra [ ] in places, I suspect I've made a
stupid mistake somewhere but I can't see it

Anyone got any ideas?


Thanks


Neil
 
I would add tblCareNotes to your report's record source and then bind a text
box to the CareNote field.
 
Thanks for the pointer Duane,

I'm a bit new to all this Access stuff, and I'm not sure what you mean.
The record source for the report is currently a query, do you mean add the
table and its subsequent fields to the original query?

Duane Hookom said:
I would add tblCareNotes to your report's record source and then bind a text
box to the CareNote field.

--
Duane Hookom
MS Access MVP
--

Neil said:
Here's one to get your heads around on a Friday!

In my database I have the following:

A table called tblCareNotes, which contains 2 fields: PartNumber,
CareNote.
Partnumber is unique and will only appear in this table once.
A table called tblInspectionRecords with many fields, one of which is
PartNumber, and will be the same as the part number in the above table.

I have created a report (called rptInspectionRecords) that that uses a
query
to retrieve all instances of a part number and list them. What I want to
do
is add the CareNote field from the CareNotes table to the header of the
report as the contents of this field will apply to all records.

I have used an unbound textbox in the header and set its control source
to:

DLookUp("[CareNote]","[tblCareNotes]","([PartNumber])=REPORTS![rptInspectionRecords]![PartNumber]")



But I get the following error message,

Syntax Error (Missing operator) in query expression


it also displays my Dlookup line, but it's slightyly different:

First([DLookUp("[CareNote]","[tblCareNotes]","([PartNumber])=REPORTS]![rptInspectionRecords]![[PartNumber]")])'


As you can see it's inserted some extra [ ] in places, I suspect I've made
a
stupid mistake somewhere but I can't see it

Anyone got any ideas?


Thanks


Neil
Click to expand...
Click to expand...
 
Don't know how else to suggest that you add tblCareNotes to your report's
record source query and join the PartNumber fields.

--
Duane Hookom
MS Access MVP
--

Neil said:
Thanks for the pointer Duane,

I'm a bit new to all this Access stuff, and I'm not sure what you mean.
The record source for the report is currently a query, do you mean add the
table and its subsequent fields to the original query?

Duane Hookom said:
I would add tblCareNotes to your report's record source and then bind a
text
box to the CareNote field.

--
Duane Hookom
MS Access MVP
--

Neil said:
Here's one to get your heads around on a Friday!

In my database I have the following:

A table called tblCareNotes, which contains 2 fields: PartNumber,
CareNote.
Partnumber is unique and will only appear in this table once.
A table called tblInspectionRecords with many fields, one of which is
PartNumber, and will be the same as the part number in the above table.

I have created a report (called rptInspectionRecords) that that uses a
query
to retrieve all instances of a part number and list them. What I want
to
do
is add the CareNote field from the CareNotes table to the header of the
report as the contents of this field will apply to all records.

I have used an unbound textbox in the header and set its control source
to:

DLookUp("[CareNote]","[tblCareNotes]","([PartNumber])=REPORTS![rptInspectionRecords]![PartNumber]")



But I get the following error message,

Syntax Error (Missing operator) in query expression


it also displays my Dlookup line, but it's slightyly different:

First([DLookUp("[CareNote]","[tblCareNotes]","([PartNumber])=REPORTS]![rptInspectionRecords]![[PartNumber]")])'


As you can see it's inserted some extra [ ] in places, I suspect I've
made
a
stupid mistake somewhere but I can't see it

Anyone got any ideas?


Thanks


Neil
Click to expand...
Click to expand...
Click to expand...
 
Duane,

You're a star, had a couple of little traumas but got there in the end!

I might just get to go home tonight!

Thanks

Neil

Duane Hookom said:
Don't know how else to suggest that you add tblCareNotes to your report's
record source query and join the PartNumber fields.

--
Duane Hookom
MS Access MVP
--

Neil said:
Thanks for the pointer Duane,

I'm a bit new to all this Access stuff, and I'm not sure what you mean.
The record source for the report is currently a query, do you mean add the
table and its subsequent fields to the original query?

Duane Hookom said:
I would add tblCareNotes to your report's record source and then bind a
text
box to the CareNote field.

--
Duane Hookom
MS Access MVP
--

Here's one to get your heads around on a Friday!

In my database I have the following:

A table called tblCareNotes, which contains 2 fields: PartNumber,
CareNote.
Partnumber is unique and will only appear in this table once.
A table called tblInspectionRecords with many fields, one of which is
PartNumber, and will be the same as the part number in the above table.

I have created a report (called rptInspectionRecords) that that uses a
query
to retrieve all instances of a part number and list them. What I want
to
do
is add the CareNote field from the CareNotes table to the header of the
report as the contents of this field will apply to all records.

I have used an unbound textbox in the header and set its control source
to:

DLookUp("[CareNote]","[tblCareNotes]","([PartNumber])=REPORTS![rptInspectionRecords]![PartNumber]")



But I get the following error message,

Syntax Error (Missing operator) in query expression


it also displays my Dlookup line, but it's slightyly different:

First([DLookUp("[CareNote]","[tblCareNotes]","([PartNumber])=REPORTS]![rptInspectionRecords]![[PartNumber]")])'


As you can see it's inserted some extra [ ] in places, I suspect I've
made
a
stupid mistake somewhere but I can't see it

Anyone got any ideas?


Thanks


Neil
Click to expand...
Click to expand...
Click to expand...
 
Back
Top