Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 24th, 2005, 06:36 AM
Authorized User
 
Join Date: Mar 2004
Location: Watford, , United Kingdom.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sort Report by MONTH field in an ADP View

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
 
Old January 24th, 2005, 07:35 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

you have to reference the original column name (MemRenewalDueDate)

because monthnumber is an alias

check and group by


Jaime E. Maccou
 
Old January 24th, 2005, 08:09 AM
Authorized User
 
Join Date: Mar 2004
Location: Watford, , United Kingdom.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 24th, 2005, 02:54 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

are you using SQL Version ? as your back end

Jaime E. Maccou
 
Old January 24th, 2005, 03:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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



 
Old January 24th, 2005, 08:55 PM
Authorized User
 
Join Date: Mar 2004
Location: Watford, , United Kingdom.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old January 24th, 2005, 10:03 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Need date comparison and month from same db field scottiegirl PHP Databases 13 December 1st, 2007 12:43 PM
Month and Year to Date on a Report Mitch Access 2 February 20th, 2007 10:23 AM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 08:25 PM
Login fail Err when view report on Report Server dillig BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 July 22nd, 2004 05:31 AM
Stored procedure as the rowsource for Report (ADP) Tero Access 1 May 12th, 2004 08:22 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.