I am new to crystal reports. And am using Crystal reports 9 with VB6 and an access Database.

I have my report designed an it is displaying the data in a cross tab.

It is displaying 3 fields from my database, and is displaying ALL the data from those fields.

I need to filter it down by date. In VB, the user will select two dates from DTPicker controls, then when they click a button to show the report, the report should only contain data BETWEEN those two dates.

I have no idea how to do this.

The date field in the DB is called "DateFinished".


I then have to send this report automatically by email. which isnt working either, it works once, then doesnt work after that.


Here is the code I am using:



visual basic code:--------------------------------------------------------------------------------
' this exports the report to PDF format automatically.
Dim pathname As String
pathname = "C:\VB_Projects\MICRO_LAB\REPORTS\Micro1.rpt"

' Use the .OpenReport method of the Application object to set your Report object to a RPT file
Set crxReport = crxApplication.OpenReport(pathname)

' Use the DiscardSavedData method to ensure that your report hits the Database and refreshes the data
crxReport.DiscardSavedData
Screen.MousePointer = vbHourglass

Form3.Show
crxReport.ExportOptions.FormatType = crEFTPortableDocFormat
crxReport.ExportOptions.DestinationType = crEDTDiskFile
crxReport.ExportOptions.DiskFileName = "C:\VB_Projects\MICRO_LAB\REPORTS\Micro1.pdf"
crxReport.ExportOptions.PDFExportAllPages = True
crxReport.Export (False)
Screen.MousePointer = vbDefault


' email the report using this code: works only once

'Set CrystalOptions = crxReport.ExportOptions
crxReport.ExportOptions.FormatType = crEFTPortableDocFormat
crxReport.ExportOptions.DestinationType = crEDTEMailMAPI

'crxReport.ExportOptions.PDFExportAllPages = True
crxReport.ExportOptions.MailSubject = "Testing Report Export via Email"
crxReport.ExportOptions.MailMessage = "Here is the latest Micro Report"
crxReport.ExportOptions.MailToList = "[email protected]"

crxReport.Export (False)



--------------------------------------------------------------------------------



Thx for any help.

Hi
1. U have to take crystal report control on the form.
2. then u write code on command button click event.
  see this code that will definatly work proper
Private Sub cmdShow_Click()
objsave.InsuranceRegister
ObjStock.StockBalance
Dim sql As String
If DTPFrom.Value > DtpTo.Value Then
    MsgBox "Plz Enter Valide Date.."
    Exit Sub
Else

    sql = "select * FROM TempBondInsuranceReg,TempBondStockBalance where

TempBondInsuranceReg.InDate = TempBondStockBalance.InDate " & _
          " AND TempBondInsuranceReg.InDate BETWEEN '" & Month(DTPFrom.Value) & "/" &

Day(DTPFrom.Value) & "/" & Year(DTPFrom.Value) & "' and '" & Month(DtpTo.Value) & "/" &

Day(DtpTo.Value) & "/" & Year(DtpTo.Value) & "'"
          sql = sql & " Order By TempBondInsuranceReg.InDate"

End If

    Dim date_from As String
    Dim date_to As String
    date_from = Day(DTPFrom) & "/" & Month(DTPFrom) & "/" & Year(DTPFrom)
    date_to = Day(DtpTo) & "/" & Month(DtpTo) & "/" & Year(DtpTo)
         MDIBonded.CrystalReport1.Reset
         MDIBonded.CrystalReport1.ReportFileName = GetName(App.Path) &

"\Reports\rptInsuranceRegister.rpt"
         MDIBonded.CrystalReport1.WindowTitle = ""
         MDIBonded.CrystalReport1.SQLQuery = sql

    MDIBonded.CrystalReport1.Formulas(0) = "From='" & date_from & "' "
         MDIBonded.CrystalReport1.Formulas(1) = "To='" & date_to & "'"
         MDIBonded.CrystalReport1.WindowTitle = ""
         MDIBonded.CrystalReport1.Destination = 0
         MDIBonded.CrystalReport1.DiscardSavedData = True
         MDIBonded.CrystalReport1.WindowState = crptMaximized
         MDIBonded.CrystalReport1.Action = 1
         MDIBonded.MousePointer = vbDefault
End Sub

Sachin