Report not recognizing label (1 Viewer)

terrytek · May 1, 2018

I have a series of three reports with similar data. One of the fields is Gender, and it has three possibilities: m, f, or unspecified.

My problem is that one of the reports' underlying queries does not return any records with the value "unspecified", since none of that particular recordset have that value. So when I make that query the underlying query of the report, I get the message when I run the report that the MS Office Access database engine does not recognize "unspecified" as a valid field name or expression. The report needs to have that field, though, in case any future records have the "unspecified" value for the field.

How can I get the report to recognize that field? I'm sure I have to do something to the underlying query, so here is that query:

Code:

SELECT qryDemographicInfoAllStudents.StudentID, qryDemographicInfoAllStudents.Gender, qryDemographicInfoAllStudents.Ethnicity, qryDemographicInfoAllStudents.Age, tblPairAssignment.AcademicYr
FROM tblPairAssignment INNER JOIN qryDemographicInfoAllStudents ON tblPairAssignment.StudentID = qryDemographicInfoAllStudents.StudentID
ORDER BY qryDemographicInfoAllStudents.StudentID;

Thank you!

The_Doc_Man · May 1, 2018

How are you specifying "unspecified"?

Yep, sounds like a crazy question but here's the thing: According to Old Programmer's Rule #2, Access won't tell you anything you didn't tell it first. So... how do you tell Access that the gender was unspecified, since you want that to be one of your possible values? If you have a table that lists M for male and F for female, what value, code, or symbol is unspecified? Hint: Null and empty aren't the best choices for answers here.

From the error description you gave us, I think the answer is that you have not specified "unspecified" so when Access sees it, it doesn't know that it means. But that error message tells me more than that. You have some type of expression not obviously related to your query that uses "unspecified" and since the word "unspecified" doesn't appear in the SQL, Access can't identify it.

I'm sure I have to do something to the underlying query, so here is that query:

I'm sure you need to look elsewhere because the query doesn't contain "unspecified" so that error message cannot have come from the query. I.e. the "does not recognize" message, which calls out "unspecified" as unrecognized, cannot have gotten that from the query, and you tell us that for your dataset, none of the records have that value anyway. So the only place you can have gotten that is in the binding of one or more of the report's controls or in a VBA segment underlying some section in the report. (Section in the formal Access sense of that word.)

terrytek · May 1, 2018

I have a table that has
Gender ID (Autonumber type) 1 is m
2 is f
3 is unspecified

I have three reports based on similar sets of data. Two of the datasets contain some Gender ID= 3, so they seem to have no issue. The one other dataset is smaller, and has no records returned in its underlying query with GenderID =3.

And I think I posted the wrong query. The query underlying the report that is having problems with "unspecified" is as follows:

Code:

TRANSFORM Count(qryDemographicInfoByAcademicYrPairs.StudentID) AS CountOfStudentID
SELECT qryDemographicInfoByAcademicYrPairs.AcademicYr, Count(qryDemographicInfoByAcademicYrPairs.StudentID) AS [Total Of StudentID]
FROM qryDemographicInfoByAcademicYrPairs
GROUP BY qryDemographicInfoByAcademicYrPairs.AcademicYr
PIVOT qryDemographicInfoByAcademicYrPairs.Gender;

But this query is built on underlying queries. The value for the Gender field comes from the Gender table ultimately, so "unspecified" is just a text value for that field, like "m" or "f". It's not a null or empty field.

The only difference between the two reports that work and the one that doesn't is that the one that doesn't happens to have no records in its recordset with the text value "unspecified". However, there may be records in that recordset in the future where that is the case, so the report needs to show a column for unspecified, even if it is blank now.

The_Doc_Man said:
How are you specifying "unspecified"?

Yep, sounds like a crazy question but here's the thing: According to Old Programmer's Rule #2, Access won't tell you anything you didn't tell it first. So... how do you tell Access that the gender was unspecified, since you want that to be one of your possible values? If you have a table that lists M for male and F for female, what value, code, or symbol is unspecified? Hint: Null and empty aren't the best choices for answers here.

From the error description you gave us, I think the answer is that you have not specified "unspecified" so when Access sees it, it doesn't know that it means. But that error message tells me more than that. You have some type of expression not obviously related to your query that uses "unspecified" and since the word "unspecified" doesn't appear in the SQL, Access can't identify it.

I'm sure you need to look elsewhere because the query doesn't contain "unspecified" so that error message cannot have come from the query. I.e. the "does not recognize" message, which calls out "unspecified" as unrecognized, cannot have gotten that from the query, and you tell us that for your dataset, none of the records have that value anyway. So the only place you can have gotten that is in the binding of one or more of the report's controls or in a VBA segment underlying some section in the report. (Section in the formal Access sense of that word.)

isladogs · May 1, 2018

Open this crosstab query you just posted in design view.
In the property sheet there is a column headers property.
This is used to force specific headers to appear.
Enter "M";"F";"U" or whatever you want for unspecified.
Or perhaps in your case you should enter 1, 2, 3

arnelgp · May 1, 2018

Not tested but may try.
Try changing this potion of xtab:

PIVOT qryDemographicInfoByAcademicYrPairs.Gender;

To:

PIVOT qryDemographicInfoByAcademicYrPairs.Gender In ("f", "m", "unspecifief")

The_Doc_Man · May 1, 2018

I am thinking that the REAL problem comes in building a report, not in the query.

Your report must somehow be trying to bind values from the pivot section which would have headers of Male, Female, and Unspecified. But for the short dataset, you say that there are no records with Unspecified, so in the resulting pivot, that column normally would not exist. It has been a while since I played with this feature, but I believe that you only get value headers if there is at least one record that would populate the column, and you say you don't have that. So you can't bind the report properly because that column ain't there. And it is that attempted binding that breaks your report.

Ridders and Arnel are suggesting ways to force the column header to exist. If the header exists, then you can bind with it. Hope that clarified the actual nature of this beast for you.

terrytek · May 4, 2018

Worked a treat, after I finally got the query properties to open instead of the field properties! Thank you!

ridders said:
Open this crosstab query you just posted in design view.
In the property sheet there is a column headers property.
This is used to force specific headers to appear.
Enter "M";"F";"U" or whatever you want for unspecified.
Or perhaps in your case you should enter 1, 2, 3

terrytek · May 4, 2018

That threw an error "Error in TRANSFORM statement", but I appreciate the try.

arnelgp said:
Not tested but may try.
Try changing this potion of xtab:

PIVOT qryDemographicInfoByAcademicYrPairs.Gender;

To:

PIVOT qryDemographicInfoByAcademicYrPairs.Gender In ("f", "m", "unspecifief")

isladogs · May 4, 2018

terrytek said:
Worked a treat, after I finally got the query properties to open instead of the field properties! Thank you!

You're welcome.

Report not recognizing label (1 Viewer)

terrytek

Registered User.

The_Doc_Man

Immoderate Moderator

terrytek

Registered User.

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

The_Doc_Man

Immoderate Moderator

terrytek

Registered User.

terrytek

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread