Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7

    Unanswered: just about there need help with Cross tab <<Fixit my self .... >>

    first I query my database get the last 2 day that were read


    Then I run a other query over the table
    using a left join with (with the last 2 days) so I only see the data collected for the Last 2 days

    Which work

    I crosstab this
    and the last 2 coloumns are the last 2 days data Collection

    which work

    now I want to pass the crosstab into a report the problem is the

    last 2 coloum will keep changing each day the run it

    so in the head part of the Printout I have this code

    [code]
    Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
    Dim MYCross As QueryDef
    Dim db As Database
    Set db = CurrentDb
    Set MYCross = db.QueryDefs("Cross this_Crosstab")
    Me.Lcol1.Caption = MYCross.Fields(3).Name
    Me.Lcol2.Caption = MYCross.Fields(4).Name
    MYCross.Close
    Set MYCross = Nothing
    Set db = Nothing
    End Sub
    [\code]

    Which Works

    I have to unbound feilds called col1 and col2 in the details part fo the report
    how do I change the controlSource on the Fly to point the
    MYCross.Fields(3).Name and MYCross.Fields(4).Name
    Last edited by myle; 08-13-06 at 01:10. Reason: Spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Fix it after typing all the above then I read the error message propley

    and all I need to do was

    [code]
    Private Sub Report_Open(Cancel As Integer)
    Dim MYCross As QueryDef
    Dim db As Database
    Set db = CurrentDb
    Set MYCross = db.QueryDefs("Cross this_Crosstab")
    Me.col1.ControlSource = "=[" & MYCross.Fields(3).Name & "]"
    Me.Col2.ControlSource = "=[" & MYCross.Fields(4).Name & "]"
    MYCross.Close
    Set MYCross = Nothing
    Set db = Nothing
    End Sub
    [\code]

    in the report open not the report details

    someone self should be able to use this
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    and adding
    tcol1 and tcol2
    to footer will give you totals



    Private Sub Report_Open(Cancel As Integer)
    Dim MYCross As QueryDef
    Dim db As Database
    Set db = CurrentDb
    Set MYCross = db.QueryDefs("Cross this_Crosstab")
    Me.col1.ControlSource = "=[" & MYCross.Fields(3).Name & "]"
    Me.Col2.ControlSource = "=[" & MYCross.Fields(4).Name & "]"
    Me.tcol1.ControlSource = "=Sum([" & MYCross.Fields(3).Name & "])"
    Me.tcol2.ControlSource = "=Sum([" & MYCross.Fields(4).Name & "])"
    MYCross.Close
    Set MYCross = Nothing
    Set db = Nothing
    End Sub
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •