How to set date period at runtime from VB6 (Eg.. 'Weekly' to 'Monthly'

I am trying to adjust the date range or period of a group in a crystal report at run time.
I am using a .RPT file, saved without the data, as a template. I was able to figure out how to adjust the date scope of the whole report but now I want to be able to have the user adjust the summary period interval at run time - say from weekly to monthly.

I am sure this has been addressed before so If someone can provide a link to the answer and it works for me that wwould be great.

I am doing this in VB6 and Crystal Reports 8.5

I explored a bit and found a collection, ReportObject.GroupNameFields, the 'name' property had the word 'weekly' at the end of a string but I do not know if I just need to replace that part of the string or are there more factors than just this property?

Thanks in advance for any help,
Regards, Tom
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't know how to adjust the grouping from VB although I guess it must be possible.
But it would be easy to add another group based on the same date field and copy the header and footer details (where required) from the existing group to the new group.  So you would base one group on weekly intervals and one on monthly intervals.  Use a parameter field to indicate which to use/suppress.  Then suppress the Group header and footer of the one you don't want based on the parameter.

FicusAuthor Commented:
mlmcc, There are easier methods to change the sort order and I do not need to change the sort field.  I need to change the period in days that the report groups.  Thanks anyway though.

Thanks Pete, Not sure thats the best method but i'll consider it.
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference ithrough the Colors of STEM program.

FicusAuthor Commented:
I think I found it.  After hours on the internet...  I'm going to go test it but here is what I found:

I figure I'll delete all the groups each time the user changes the value of the group scope and then add the group with the new value to the report.  Hopefully this will work.  Maybe my sub totals and other calculated fields will be messed up?

2. Adding a New Group to the Report

For this scenario, let's say a report is created which does not contain any groups. To add a new group to the report at runtime, the following code can be used.

* Please note that using this method will not display the group name on the report.

'General Declarations
Dim Report As New CrystalReport1 'The existing report (ActiveX Designer)
Dim crxApp As CRAXDRT.Application
Dim crxDBField As CRAXDRT.DatabaseFieldDefinition

'In the FORM_LOAD event of the form
Private Sub Form_Load()

'Want to add a group which is based on the REGION field.
'Basically, accessing the first table to get the 12th field,
'which is the REGION field.
set crxDBField = Report.Database.Tables.Item(1).Fields.Item(12)

'Add the new group to the report.
' 0 - is the GroupNumber in case you add more than one group
' crxDBField - is the ConditionField
' crGCAnyValue - is the condition on which the group changes
' crAscendingOrder - is the SortDirection
Report.AddGroup 0, crxDBField, crGCAnyValue, crAscendingOrder

'Display the report
CRViewer1.ReportSource = Report

End Sub


This is from the pdf files that came with CR

Constant       Value
crGCAnnually      7
crGCAnyValue    14
crGCBiweekly     2
crGCByAMPM     18
crGCByHour       17
crGCByMinute    16
crGCBySecond   15
crGCDaily          0
crGCEveryNo     11
crGCEveryYes    10
crGCMonthly      4
crGCNextIsNo    13
crGCNextIsYes   12
crGCQuarterly      5
crGCSemiAnnually 6
crGCSemimonthly  3
crGCToNo            9
crGCToNo            8
crGCWeekly          1
FicusAuthor Commented:
Well I found a method that so far seems to work.  I would like to comment that Crystal Reports is an awsome tool for those how need to create reports from databases (really spruces up an application), but there code examples and documentation kind of suck.

There is some extra junk on the code pasted below that pretains to my project but the example shows a method to what I set out to do.  There may be other better solutions...

My project used a mdi child form with the CRViewer Control on it.   These objects form a collection:

Public Type CRV_FormState
    Deleted As Boolean
    ReportStyle As Integer
    ReportObject As CRAXDRT.Report
    DefaultFormula As String
End Type
Public CRV_FormState() As CRV_FormState
Public CRV_Form() As New Frm_CrView

In the above some of the properties of my struct are obtained from a database with information about my available reports (.RPT templates) as well as the default recordselectionformula and the actual file name while others are from the actual CRXReport object...

Below is the code on the actaul child form.  I always use Me.object to prevent refrencing the wrong or non init. collection member:

'***MDI Child CRViewer Form***
'TNG Nov 12, 2003
' - Includes Code to Change Scope of Crystal Reports group summary period
' - As well as over all date scope of records to include in report
' - (Does not trigger any prompts about non inclusion of function in runtime licsense)

Option Explicit

Private Sub chk_alldates_Click()
'/ Toggle all dates / specify dates
  If Me.chk_alldates.Value = 1 Then
    Me.DTP_Start.Visible = False
    Me.DTP_stop.Visible = False
    Me.lbl_date(0).Visible = False
    Me.lbl_date(1).Visible = False
    Me.DTP_Start.Visible = True
    Me.DTP_stop.Visible = True
    Me.lbl_date(0).Visible = True
    Me.lbl_date(1).Visible = True
  End If
End Sub

Private Sub Cmd_refresh_Click()
'/ Update RecordSelectionFormula based on scope of 2 DatePicker Controls
'/ Update GroupCondition (weekly, monthly etc...) based on Combobox listindex value
'/ Also place the RecordSelectionFormula in our formulafield ({@Title}) so we know the query behind the report
'/ Finally Refresh Report Viewer

  '*** CRGroupCondition                     ***
  'Constant          Value
  'crGCAnnually      7
  'crGCAnyValue      14
  'crGCBiweekly      2
  'crGCByAMPM        18
  'crGCByHour        17
  'crGCByMinute      16
  'crGCBySecond      15
  'crGCDaily         0
  'crGCEveryNo       11
  'crGCEveryYes      10
  'crGCMonthly       4
  'crGCNextIsNo      13
  'crGCNextIsYes     12
  'crGCQuarterly     5
  'crGCSemiAnnually  6
  'crGCSemimonthly   3
  'crGCToNo          9
  'crGCToNo          8
  'crGCWeekly        1
  '*** Our Combo Box Indexs -> Value        ***
  '0 = Daily
  '1 = Weekly
  '2 = Bi-Weekly
  '3 = Semi Monthly
  '4 = Monthly
  Dim LStr_DateScope As String
  Dim ff
  Dim i As Integer
  Dim LInt_NewGC As Integer
  '//Set Record Selection (two methods; one for when date is from caja table, one for date from venta table)
  If Me.chk_alldates.Value = 1 Then
    LStr_DateScope = CRV_FormState(Me.Tag).DefaultFormula
    If CRV_FormState(Me.Tag).DefaultFormula <> "" Then
      LStr_DateScope = CRV_FormState(Me.Tag).DefaultFormula & " AND "
    End If
    If CRV_FormState(Me.Tag).ReportStyle = 1 Then
      'caja madedate range
       LStr_DateScope = LStr_DateScope & "{cajas.madestamp} >= DATE(" & _ Format$(DTP_Start.Value, "yyyy,mm,dd") & ") AND {cajas.madestamp} <= DATE(" & _ Format$(DTP_stop.Value, "yyyy,mm,dd") & ")"
      'Tour date range
      LStr_DateScope = LStr_DateScope & "{ventas.tourdate} >= DATE(" & _ Format$(DTP_Start.Value,

"yyyy,mm,dd") & ") AND {ventas.tourdate} <= DATE(" & _ Format$(DTP_stop.Value, "yyyy,mm,dd") & ")"
    End If
  End If
  'set the record selection critera for report and refresh
  CRV_FormState(Me.Tag).ReportObject.RecordSelectionFormula = LStr_DateScope
  '//Adjust the date period of our groups summary
  LInt_NewGC = Me.cmb_GroupPeriodType.ListIndex
  For i = 1 To CRV_FormState(Me.Tag).ReportObject.Areas.Count
    If CRV_FormState(Me.Tag).ReportObject.Areas(i).Kind = crGroupFooter Or _
CRV_FormState(Me.Tag).ReportObject.Areas(i).Kind = crGroupHeader Then
      'We are in the Group Header or Group Footer Area
      If CRV_FormState(Me.Tag).ReportObject.Areas(i).GroupCondition <> crGCAnyValue Then
        ' The current group condition is of a date type (it is not 'AnyValue' which in our case is  
        ' anything besides a date type)
        CRV_FormState(Me.Tag).ReportObject.Areas(i).GroupCondition = LInt_NewGC
      End If
    End If
  Next i
  '//place report scope subtitle
  For Each ff In CRV_FormState(Me.Tag).ReportObject.FormulaFields
    If ff.Name = "{@Title}" Then
      ff.Text = Chr$(34) & LStr_DateScope & Chr$(34)
    End If
End Sub

Private Sub Form_Activate()
'/ Depending on the attributes of this report display the apropriate controls on the form
  If CRV_FormState(Me.Tag).ReportStyle > 0 Then
    chk_alldates.Visible = True
    cmb_GroupPeriodType.Visible = True
    'TO DO: Set DTPs to High and Low Ranges of availabe Records
    '       (not the stupid default value of the date when control was placed on form
    chk_alldates.Visible = False
    cmb_GroupPeriodType.Visible = False
  End If
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
'/ Key Preview -> Display Report Wizard on F2
  If KeyCode = vbKeyF2 Then frm_SKYRmain.Show
End Sub

Private Sub Form_Load()
'/ set combo to 'weekly' our default value
  cmb_GroupPeriodType.ListIndex = 1
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
'/ Clean up Instance of Type CRV_FormState and flag Deleted = True to Recalim on next Spawning
  Set CRV_FormState(Me.Tag).ReportObject = Nothing
  CRV_FormState(Me.Tag).Deleted = True
End Sub

Private Sub Form_Resize()
'/ Resize Scope Frame and CR Viewer Obj
  Me.CRViewer.Top = Me.fra_scope.Height
  Me.CRViewer.Left = 0
  If Me.ScaleHeight > Me.fra_scope.Height Then Me.CRViewer.Height = Me.ScaleHeight -

  Me.CRViewer.Width = Me.ScaleWidth
  Me.fra_scope.Width = Me.CRViewer.Width
End Sub
Question closed and points refunded

Cs Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.