Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 22nd, 2005, 04:26 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default crosstab query and parameter

I have a crosstab query and in the criteria pane I put [Forms]![frmRptTrafficReport]![CurrentYear]. I am using a text box on unbound form for the user to enter the year.

I get an error saying it does not recognize [Forms]![frmRptTrafficReport]![CurrentYear] as a valid field name or expression. I know it is a valid field name because the value is displayed in the immediate window. The query also works if I change it to a select query.

Is there something special about crosstab query I am missing?

Thanks
Reply With Quote
  #2 (permalink)  
Old June 22nd, 2005, 07:58 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

What does your crosstab query look like?


Kevin

dartcoach
Reply With Quote
  #3 (permalink)  
Old June 22nd, 2005, 10:36 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dartcoach:

What info would help you to assist me. It is a standard crosstab query that works without the parameters on the unbound form. When I reference the unbound form and populate the criteria pane I get this error not recognizing the field as valid.

If I switch from a crosstab to a select query with the criteria intact I get the expected results. The data is not displayed as I would like the crosstab to do.

I hope that helps
Reply With Quote
  #4 (permalink)  
Old June 22nd, 2005, 10:43 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Stoneman,
Without seeing your form, and your crosstab query sql, it's real hard to say what the problem might be. If you can, cut and paste the sql into your reply. Also, cut and paste the sql from the query when it's just a select query.

Kevin

dartcoach
Reply With Quote
  #5 (permalink)  
Old June 23rd, 2005, 08:31 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default


The SQL below are copied from the qiery. As I said it does not recognise )=[Forms]![frmRptTrafficReport]![CurMonth] and [Forms]![frmRptTrafficReport]![CurYear] as valid fields although they work in the select query.

I hope this assist you. Thanks this roadblock is preventing me from completing this project.


TRANSFORM Count(tblVisit.VisitID) AS CountOfVisitID
SELECT tblVisit.AdSource, IIf([Sold]=Yes,"1","0") AS SoldUnits
FROM tblVisit INNER JOIN tblVisitDetails ON tblVisit.VisitID = tblVisitDetails.VisitID
WHERE (((tblVisit.MonthOf)=[Forms]![frmRptTrafficReport]![CurMonth]) AND ((tblVisit.VisitYear)=[Forms]![frmRptTrafficReport]![CurYear]))
GROUP BY tblVisit.AdSource, IIf([Sold]=Yes,"1","0"), tblVisit.MonthOf, tblVisit.VisitYear, tblVisit.DealershipID
PIVOT tblVisitDetails.VehicleCategory;


*******************


SELECT tblVisit.AdSource, IIf([Sold]=Yes,"1","0") AS SoldUnits, tblVisitDetails.VehicleCategory, Count(tblVisit.VisitID) AS CountOfVisitID
FROM tblVisit INNER JOIN tblVisitDetails ON tblVisit.VisitID = tblVisitDetails.VisitID
WHERE (((tblVisit.MonthOf)=[Forms]![frmRptTrafficReport]![CurMonth]) AND ((tblVisit.VisitYear)=[Forms]![frmRptTrafficReport]![CurYear]))
GROUP BY tblVisit.AdSource, IIf([Sold]=Yes,"1","0"), tblVisit.MonthOf, tblVisit.VisitYear, tblVisit.DealershipID, tblVisitDetails.VehicleCategory;
Reply With Quote
  #6 (permalink)  
Old June 23rd, 2005, 10:28 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Stoneman,
The sql looks fine. I'm not sure why it doesn't work. Are you using the Access query design to create these queries?

dartcoach
Reply With Quote
  #7 (permalink)  
Old June 23rd, 2005, 11:34 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes I am using the qurery design to create the queries and I am using the saved query as the recordsource of the report
Reply With Quote
  #8 (permalink)  
Old June 23rd, 2005, 12:02 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Stoneman,
This is probably too simple but - is your form open when you run the report?
If it is, and you open the report with a command button, do you close the form before you open the report?

Kevin

dartcoach
Reply With Quote
  #9 (permalink)  
Old June 23rd, 2005, 02:32 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks Dartcoach for trying to help but I am puzzled just like you.

The form is opened while the report is opened, in fact while I am getting the error on the report I can get the values on the form in the immediate window. That is why I am unable to understand why I am getting the error.

Any other suggestion, could this be a bug in Access 2003
Reply With Quote
  #10 (permalink)  
Old June 23rd, 2005, 02:47 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Stoneman,
Do you use the form variables in the output? Crosstabs work differently because of rowheadings, columns etc. In your query design, make sure that those fields are unchecked. That way they are only used at query time for criteria - but not for output.

Kevin

dartcoach
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Crosstab date query Brendan Bartley Access 3 March 29th, 2007 09:18 AM
Crosstab query with no results edubbelaar Access 0 November 22nd, 2004 06:12 AM
Crosstab Query Ben Access 7 November 22nd, 2004 06:08 AM
Crosstab query Berni016x SQL Server 2000 2 September 17th, 2004 10:43 AM
Crosstab query vladimir Access 1 December 1st, 2003 04:02 PM



All times are GMT -4. The time now is 05:55 AM.


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