Reporting

[Guest]

I want to create a new report based on a query...using the wizard and
selecting the query for the report, I noted a in the list of available fields
"expr 1006", yet I don't see the field on the tables that are linked for the
query or in the fields for the query? Where and what is this? Do I need to
be concerned? How do I get rid of it? Thanks!

 

[jahoobob via AccessMonster.com]

Do you have fields in two tables that come together in the query that have
the same name or do you have the same field from one table in there twice?
This will cause this.

 

[John W. Vinson]

I want to create a new report based on a query...using the wizard and
selecting the query for the report, I noted a in the list of available fields
"expr 1006", yet I don't see the field on the tables that are linked for the
query or in the fields for the query? Where and what is this? Do I need to
be concerned? How do I get rid of it? Thanks!

Sounds like you have selected the same field in two different contexts. Access
will create an alias for the field if you need it in the query in two
different ways. Perhaps you could post the SQL view of the query here.

John W. Vinson [MVP]

 

[Guest]

SELECT CommentsTrial.CommentsID, CommentsTrial.Comments,
CommentsTrial.CommentsEntryDate, CommentsTrial.StatusChange,
CommentsTrial.TID, CommentsTrial.RequestorID, CommentsTrial.DataEntryID,
CommentsTrial.ResearchTimeID, CommentsTrial.TopicTypeID, Topics2.TopicsID,
Topics2.TopicName, Topics2.[Program ManagerID], CommentsTrial.DataEntryID
FROM Topics2 INNER JOIN CommentsTrial ON Topics2.TopicsID = CommentsTrial.TID
WHERE (((CommentsTrial.CommentsEntryDate) Between [Enter start date using
format mm/dd/yyy] And DateAdd("d",1,[Enter end date using format
mm/dd/yyyy])));


I renamed TopicsID at some point to TID for some reason similar to this...it
was thinking the fields were the same. Why do I see the Expr 1006 only in
the available fields list of the report wizard and not anywhere else?

Is this going to effect the functionality of the query?
Thanks!

 

[Guest]

If you run the query in datasheet view and scroll horizontally you will see
the field labeled Expr 1006 as an output field and not the field name you
expected.

You selected CommentsTrial.DataEntryID twice.
--
KARL DEWEY
Build a little - Test a little

SELECT CommentsTrial.CommentsID, CommentsTrial.Comments,
CommentsTrial.CommentsEntryDate, CommentsTrial.StatusChange,
CommentsTrial.TID, CommentsTrial.RequestorID, CommentsTrial.DataEntryID,
CommentsTrial.ResearchTimeID, CommentsTrial.TopicTypeID, Topics2.TopicsID,
Topics2.TopicName, Topics2.[Program ManagerID], CommentsTrial.DataEntryID
FROM Topics2 INNER JOIN CommentsTrial ON Topics2.TopicsID = CommentsTrial.TID
WHERE (((CommentsTrial.CommentsEntryDate) Between [Enter start date using
format mm/dd/yyy] And DateAdd("d",1,[Enter end date using format
mm/dd/yyyy])));


I renamed TopicsID at some point to TID for some reason similar to this...it
was thinking the fields were the same. Why do I see the Expr 1006 only in
the available fields list of the report wizard and not anywhere else?

Is this going to effect the functionality of the query?
Thanks!

Sounds like you have selected the same field in two different contexts. Access
will create an alias for the field if you need it in the query in two
different ways. Perhaps you could post the SQL view of the query here.

John W. Vinson [MVP]

 

[John W. Vinson]

SELECT CommentsTrial.CommentsID, CommentsTrial.Comments,
CommentsTrial.CommentsEntryDate, CommentsTrial.StatusChange,
CommentsTrial.TID, CommentsTrial.RequestorID, CommentsTrial.DataEntryID,
CommentsTrial.ResearchTimeID, CommentsTrial.TopicTypeID, Topics2.TopicsID,
Topics2.TopicName, Topics2.[Program ManagerID], CommentsTrial.DataEntryID
FROM Topics2 INNER JOIN CommentsTrial ON Topics2.TopicsID = CommentsTrial.TID
WHERE (((CommentsTrial.CommentsEntryDate) Between [Enter start date using
format mm/dd/yyy] And DateAdd("d",1,[Enter end date using format
mm/dd/yyyy])));


I renamed TopicsID at some point to TID for some reason similar to this...it
was thinking the fields were the same. Why do I see the Expr 1006 only in
the available fields list of the report wizard and not anywhere else?

Is this going to effect the functionality of the query?
Thanks!

You have CommentsTrial.DataEntryID in the query twice. Access will pick an
arbitrary name like this in such cases.

How and why the wizard does such things is anyone's guess - Microsoft isn't
telling.

John W. Vinson [MVP]

 

[Guest]

OK, I guess that I can accept that, but part of being an Access user is an
inherent sense of tenacity. So, my last question remains...will this violate
the functionality of the query?
Cheese

SELECT CommentsTrial.CommentsID, CommentsTrial.Comments,
CommentsTrial.CommentsEntryDate, CommentsTrial.StatusChange,
CommentsTrial.TID, CommentsTrial.RequestorID, CommentsTrial.DataEntryID,
CommentsTrial.ResearchTimeID, CommentsTrial.TopicTypeID, Topics2.TopicsID,
Topics2.TopicName, Topics2.[Program ManagerID], CommentsTrial.DataEntryID
FROM Topics2 INNER JOIN CommentsTrial ON Topics2.TopicsID = CommentsTrial.TID
WHERE (((CommentsTrial.CommentsEntryDate) Between [Enter start date using
format mm/dd/yyy] And DateAdd("d",1,[Enter end date using format
mm/dd/yyyy])));


I renamed TopicsID at some point to TID for some reason similar to this...it
was thinking the fields were the same. Why do I see the Expr 1006 only in
the available fields list of the report wizard and not anywhere else?

Is this going to effect the functionality of the query?
Thanks!

You have CommentsTrial.DataEntryID in the query twice. Access will pick an
arbitrary name like this in such cases.

How and why the wizard does such things is anyone's guess - Microsoft isn't
telling.

John W. Vinson [MVP]

 

[John W. Vinson]

OK, I guess that I can accept that, but part of being an Access user is an
inherent sense of tenacity. So, my last question remains...will this violate
the functionality of the query?

No, just so long as you don't have a control somewhere bound to the alias
field.

John W. Vinson [MVP]