Record Source not showing field names

[Pascoe via AccessMonster.com]

Hi there,

I have a Report which is based on two tables.

I have a query set up within the Record Source of the Report to return values
to the report. In the Record Source Query Tools I have set up the query to
only accept certain values. The SQL is here:

SELECT [Outstanding Actions].*, [Outstanding Actions].[Date Finished],
Tenants.[First Name], Tenants.[Last Name], Tenants.[Mobile Number],
[Outstanding Actions].Owner, [Outstanding Actions].[Job Date]
FROM Tenants INNER JOIN [Outstanding Actions] ON Tenants.ID = [Outstanding
Actions].[Reported By]
WHERE ((([Outstanding Actions].[Date Finished]) Is Null)) OR ((([Outstanding
Actions].[Date Finished]) Is Null) AND (([Outstanding Actions].Owner)=[Forms]!
[Job Lists Form]![Owner Choice]) AND (([Outstanding Actions].[Job Date])=
[Forms]![Job Lists Form]![Job Date Choice]));


However, the report does not display the information I want, it appears to
display everything, not just what I have "only" asked to see. When I look at
the Table View of the query, there are three fields I am unfamiliar with,
Field0, Field1 and Field2. I presume two fields I have put limitations on
included here, Field1 is populated with "Maciej" which is the limitation on
[Owner Choice], however the [Job Date] is neither present, nor populated in
Field2 or Field0, although in the original table it is populated.

I think this is something to do with the relationships, but I can't seem to
spot where I have messed up.

Your help will be appreciated!

Thanks.
Russell.

 

[Jeff Boyce]

Have you opened the query in design view and inspected the fields it
outputs?

Since it all starts with the data, have you inspected the table definitions
for those two tables?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Pascoe via AccessMonster.com]

Hi Jeff,

Thanks for your reply.

I don't understand your first question, "fields it outputs"??. I have opened
the query in Design View, and entered in the criteria I want to sort by. I
have opened the Datasheet View and that's where I see the Field0 Field1
Field2 Column headers, one column, Field1, is displaying "Maciej", which is
one of the criteria I am filtering by in the Design View ([Outstanding
Actions].[Owner]=[Forms]![Job Lists Form]![Owner Choice], where in this case
[Owner Choice] = "Maciej"). I do not understand why the Datasheet View is
showing Field0 Field1 when there are the correct headings used in Design View.


Your second question, I can confirm I have checked the table definitions.

Thanks
Russell.

Have you opened the query in design view and inspected the fields it
outputs?

Since it all starts with the data, have you inspected the table definitions
for those two tables?

Hi there,

[quoted text clipped - 36 lines]

Thanks.
Russell.

 

[Duane Hookom]

Typically you shouldn't use SELECT TableName.* if you can help it. This will
often create duplicate column values in your query with names like Expr1 or
similar.

 

[Jeff Boyce]

Aha!

"I do not understand why the Datasheet View is
showing Field0 Field1 when there are the correct headings used in Design
View."

It is possible to add Captions to fields ... for use as alternative display
headings. Do your fields in question have Captions?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi Jeff,

Thanks for your reply.

I don't understand your first question, "fields it outputs"??. I have
opened
the query in Design View, and entered in the criteria I want to sort by. I
have opened the Datasheet View and that's where I see the Field0 Field1
Field2 Column headers, one column, Field1, is displaying "Maciej", which
is
one of the criteria I am filtering by in the Design View ([Outstanding
Actions].[Owner]=[Forms]![Job Lists Form]![Owner Choice], where in this
case
[Owner Choice] = "Maciej"). I do not understand why the Datasheet View is
showing Field0 Field1 when there are the correct headings used in Design
View.


Your second question, I can confirm I have checked the table definitions.

Thanks
Russell.

Have you opened the query in design view and inspected the fields it
outputs?

Since it all starts with the data, have you inspected the table
definitions
for those two tables?

Hi there,

[quoted text clipped - 36 lines]

Thanks.
Russell.