In my ADP I have a View which works fine. It has a field called MonthNumber which it is happy to sort by. This field is built using this expression: MONTH(dbo.Members.MemRenewalDueDate)

I have a report based on this view which prints the field/column MonthNumber correctly. The report works fine until I try to sort by MonthNumber.

In the Sorting and Grouping window Access offers the MonthNumber field as one of the fields available for sorting. However, if I select this field for sorting, when I run the report the following error occurs:
Cannot find column MonthNumber

So, Access can "find" the column MonthNumber when it is asked to print it and when it is offering me a list of fields to sort by, but when it tries to sort by it then it suddenly cannot find it any more!

Can anyone help Access find this field? (Or, perhaps, not lose it in the first place?!)

Below is the SQL for the View the report is based on.

SELECT TOP 100 PERCENT dbo.Code_MemStatus.MemStatusCodeCode, MONTH(dbo.Members.MemRenewalDueDate) AS MonthNumber, dbo.Members.MemRenewalDueDate AS Expr1, DATENAME(month, dbo.Members.MemRenewalDueDate) AS MonthName, dbo.Code_MemType.MemTypeCodeID, dbo.Members.*
FROM dbo.Members INNER JOIN dbo.Code_MemType ON dbo.Code_MemType.MemTypeCodeID = dbo.Members.MemTypeID INNER JOIN dbo.Code_MemStatus ON dbo.Code_MemStatus.MemStatusCodeID = dbo.Members.MemStatusID
ORDER BY dbo.Code_MemStatus.MemStatusCodeCode, MONTH(dbo.Members.MemRenewalDueDate), dbo.Members.MemRenewalDueDate

R

__________________
R

you have to reference the original column name (MemRenewalDueDate)

because monthnumber is an alias

check and group by


Jaime E. Maccou

please define "reference" in this context.

I thought you might mean me to put
MONTH(dbo.Members.MemRenewalDueDate)
in the Field/Expression column in the Sorting and Grouping window
but this just gives me
Cannot find column MONTH(Members.MemRenewalDueDate)

(which, incidently, is what I would expect to happen and why I used the alias in the first place!)

Why, by the way, does the drop down list in the Field/Expression column in the Sorting and Grouping window offer me the alias MonthNumber to sort by if Access cannot sort by aliases?

AN ASIDE (just out of curiosity)
You say "because monthnumber is an alias" as if that explained something but it just confuses me more. I mean, I know its an alias but to my mind that means I SHOULD use it.
To put it another way: what on earth is the point of an alias you cannot reference?



R

are you using SQL Version ? as your back end

Jaime E. Maccou

Hi Richard,

You are correct. Having aliased your field "MonthNumber" that is the name that will serve as the control source of your report control, appear in the sorting and grouping dialog, etc.

In fact, you can use the alias in your OrderBy clause instead of MONTH(dbo.Members.MemRenewalDueDate). I can't think of any good reason why your report is breaking, but what happens if you use your alias in your Order By clause in the query, or remove your Order By clause altogether and let your report handle the sorting.

Else, have you tried recreating the report?

- Bob

So I recreated it, mostly using cut and paste, and as far as I can tell the new version is identical - except it works!

I should have tried the recreate thing first but I have not had this problem resolution with a report before - I have had it a lot with views (its easier to recretae views because all you have to do is cut and paste the SQL from the old view to the new one).

An unexpected bonus of recreating it was that the new version does not print a blank page between each "real" page (a problem I had given up on). Sigh - now I Know I have to recreate every report with this problem.

I hope the next version of Access has a RECREATE option for Reports - there has to be a better way than all that cutting and pasting!

Thanks Bob, thanks jemacc

(I'm looking forward to the day someone posts a problem I can help with - no doubt it will be something I found out the hard way!)


R

Glad you got it going Richard. When there ain't no good reason for it being broke, sometimes you just gotta' rebuild it and give Acces a second chance to get it right.

- Bob