Include field not in table in report?

[MikeB]

I'm writing a set of tables and forms and reports to manage a small
chess club at my daughter's school.

One of the things I create is a report on the previous chess match,
with the players as drawn and their match results.

I extract this from my Games table. The Games table has a foreign key
to the Matches table where I have a record recording the Match data
(title, date, time, etc.)

When I create the Game Results report, I'd like to include the title
of the match and it's date in the header. I created a text box and
inserted the following expression:

=(SELECT [Title] FROM [Matches] WHERE [Games].[MatchNumber] =
[Matches].[MatchNumber])

The query works. If I paste it into another SQL query, I get the data
I want. However, if I run the report, I get #Name? error.

What am I doing wrong? Do I have to create a query simply to include
the match title in every record in the query for the report?

Thanks.

 

[fredg]

I'm writing a set of tables and forms and reports to manage a small
chess club at my daughter's school.

One of the things I create is a report on the previous chess match,
with the players as drawn and their match results.

I extract this from my Games table. The Games table has a foreign key
to the Matches table where I have a record recording the Match data
(title, date, time, etc.)

When I create the Game Results report, I'd like to include the title
of the match and it's date in the header. I created a text box and
inserted the following expression:

=(SELECT [Title] FROM [Matches] WHERE [Games].[MatchNumber] =
[Matches].[MatchNumber])

The query works. If I paste it into another SQL query, I get the data
I want. However, if I run the report, I get #Name? error.

What am I doing wrong? Do I have to create a query simply to include
the match title in every record in the query for the report?

Thanks.

A Control's Control source cannot contain a SQLSelect statement.
You would use DLookUp instead.
=DLookUp("[Title]","Matches","[MatchNumber] = " & Me.[MatchNumber]

The above assumes Matches is the name of your table and your report
has a control named [MatchNumber]. and [MatchNumber] is a Number
datatype.

For more information, look up
DLookUp + Restrict data to a subset of records
in VBA help.

 

[MikeB]

I'm writing a set of tables and forms and reports to manage a small
chess club at my daughter's school.

One of the things I create is a report on the previous chess match,
with the players as drawn and their match results.

I extract this from my Games table. The Games table has a foreign key
to the Matches table where I have a record recording the Match data
(title, date, time, etc.)

When I create the Game Results report, I'd like to include the title
of the match and it's date in the header. I created a text box and
inserted the following expression:

=(SELECT [Title] FROM [Matches] WHERE [Games].[MatchNumber] =
[Matches].[MatchNumber])

The query works. If I paste it into another SQL query, I get the data
I want. However, if I run the report, I get #Name? error.

What am I doing wrong? Do I have to create a query simply to include
the match title in every record in the query for the report?

Thanks.

A Control's Control source cannot contain a SQLSelect statement.
You would use DLookUp instead.
=DLookUp("[Title]","Matches","[MatchNumber] = " & Me.[MatchNumber]

The above assumes Matches is the name of your table and your report
has a control named [MatchNumber]. and [MatchNumber] is a Number
datatype.

For more information, look up
DLookUp + Restrict data to a subset of records
in VBA help.

Thanks!

Wow, I had to install that help from the Office CD, but it explains
why I couldn't find help on a lot of stuff before.

Also, I had to delete the "[Me]." from the criteria. However, in
looking at your response, it didn't have the square brackets around
it, so those probably got added after I entered the criteria. I'll go
check if the query works as you supplied when I manually remove them.

Thanks again.

 

[fredg]

I'm writing a set of tables and forms and reports to manage a small
chess club at my daughter's school.

One of the things I create is a report on the previous chess match,
with the players as drawn and their match results.

I extract this from my Games table. The Games table has a foreign key
to the Matches table where I have a record recording the Match data
(title, date, time, etc.)

When I create the Game Results report, I'd like to include the title
of the match and it's date in the header. I created a text box and
inserted the following expression:

=(SELECT [Title] FROM [Matches] WHERE [Games].[MatchNumber] =
[Matches].[MatchNumber])

The query works. If I paste it into another SQL query, I get the data
I want. However, if I run the report, I get #Name? error.

What am I doing wrong? Do I have to create a query simply to include
the match title in every record in the query for the report?

Thanks.

A Control's Control source cannot contain a SQLSelect statement.
You would use DLookUp instead.
=DLookUp("[Title]","Matches","[MatchNumber] = " & Me.[MatchNumber]

The above assumes Matches is the name of your table and your report
has a control named [MatchNumber]. and [MatchNumber] is a Number
datatype.

For more information, look up
DLookUp + Restrict data to a subset of records
in VBA help.

Thanks!

Wow, I had to install that help from the Office CD, but it explains
why I couldn't find help on a lot of stuff before.

Also, I had to delete the "[Me]." from the criteria. However, in
looking at your response, it didn't have the square brackets around
it, so those probably got added after I entered the criteria. I'll go
check if the query works as you supplied when I manually remove them.

Thanks again.

I goofed.
That word Me. should NOT be used in the control source.
.... "[MatchNumber] = " & [MatchNumber]
is fine.

Me. is a VBA keyword, Not an Access word.
If this DLookUp was used in Code, then Me! is fine.
It refers to the name of the object it is used in, i.e. in place of
writing Forms!FormName!ControlName, you would write Me![ControlName].
Used in an Access control it will throw an error.