Use Dlookup in my header

[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

 

[Duane Hookom]

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

 

[Guest]

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?

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

 

[Duane Hookom]

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
--

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?

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

 

[Guest]

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

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
--

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?

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