Results 1 to 6 of 6

Report From CrossTab Query

  1. #1
    GSS
    GSS is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    3

    Report From CrossTab Query

    Hi,

    I am trying to build a report that uses a crosstab query as its data source. The crosstab query has three input parameters and functions as expected by itself. My problem is when I attempt to connect the query to my report, the report behaves as if I am not passing the parameters. The default parameter dialog box keeps appearing - three times, once for each parameter. My three parameters are: Factory, Year, Month. My VBA code looks like below:

    DoCmd.OpenReport "SHByFactoryDetail", acPreview, "Factory=''3M'', Year=2011, Month=12

    It just ignores the parmeters that I provide above and asks me to re-enter the parameters again via the default parameter dialog box.

    How can I fix this?

    GSS

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    11,866
    Your syntax is incorrect. Try

    DoCmd.OpenReport "SHByFactoryDetail", acPreview, , "Factory='3M' And Year=2011 And Month=12"
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    GSS
    GSS is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    3
    Thank you. When I change my syntax to be as you stated above, I get error 2580: The record source 'SHByFactoryDetail, '3M', 2011, 12' specified on this form or report does not exist.

    ??

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    11,866
    What exactly is your code? It should not have jammed it all together like that.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    GSS
    GSS is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    3
    My apologies, I misspelled the name of the report. But when I corrected that error, I get my original behavior where the report just acts like it is not receiving any parameters from me. My exact code is pasted below:

    Private Sub btnPrint_Click()
    Dim sWhereClause As String

    sWhereClause = "Factory='" & cboFactory.Value & "' AND Year=" & txtYear.Value & " AND Month=" & txtMonth.Value
    MsgBox "sWhereClause = " & sWhereClause
    DoCmd.OpenReport "SalesHistoryByFactory", acViewPreview, , sWhereClause
    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    11,866
    I use that technique all the time, but I've never tried it on a crosstab query/report. Normally crosstab queries need their parameters declared, which I'm not sure is an issue here:

    Crosstab Query with a Parameter

    Can you post the db here so we can play with it?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report using a Crosstab Query
    By Paul H in forum Reports
    Replies: 2
    Last Post: 02-09-2012, 02:35 PM
  2. Report based on crosstab query
    By pbuecken in forum Reports
    Replies: 7
    Last Post: 01-16-2012, 07:59 PM
  3. Replies: 2
    Last Post: 08-17-2011, 02:02 AM
  4. Crosstab Report (unbound)
    By belebala in forum Reports
    Replies: 0
    Last Post: 07-06-2011, 02:56 PM
  5. Replies: 0
    Last Post: 05-09-2011, 12:51 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums