• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Passing Date from VB6 to Crystal Reports

I am using VB 6, MS Access and Crystal Reports 8.  I need to have reports run for any date (only) entered by the user.  Therefore, I need to acquire the date from the user, which I have done via a combo boxes (month, day, and year).  And, I need to take this information and pass it to Crystal Reports to print the report for entered date only.
 
I am using MS Access as the database The field is date/time, although just the date (short date) occupies the field.  However, Crystal Reports seems to want a date/time stamp passed to it.  However, I must not be using the proper syntax as I keep getting a blank sheet.

Any assistance appreciated.
0
perezm
Asked:
perezm
  • 5
  • 4
  • 3
  • +2
1 Solution
 
mlmccCommented:
What code are you using?  

mlmcc
0
 
perezmAuthor Commented:
I am using the following code in VB

CrysDate = Yr & "-" & Mo & "-" & Dy (which = "2000-07-04")
   
CrystalReport1.Connect = "DSQ = " + App.Path + "\" + "db1.mdb"
   
CrystalReport1.ReportFileName = App.Path + "\" + "Perf.rpt"
   
CrystalReport1.StoredProcParam(0) = CrysDate
   
    If frmReports.optWindow Then
        CrystalReport1.WindowState = crptMaximized
        CrystalReport1.Destination = crptToWindow
    Else
        CrystalReport1.Destination = crptToPrinter
    End If
   
    CrystalReport1.Action = 1
    CrystalReport1.StoredProcParam(0) = ""
    CrystalReport1.Connect = ""

NOTE:  I have also tried converting Crysdate to a date before sending and it still didn't work.

If you have any more questions, please let me know.

Thanks.

MP
0
 
mlmccCommented:
Try

CrystalReport1.StoredProcParam(0) = Date(Yr,Mo,Dy)


You actually have to pass a date not a string that looks like a  date.

mlmcc
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
PiterCommented:
Hello,
 For passing date you have to declear CrysDate as public in you Module or in the form.
you can format date using 'format' command
0
 
perezmAuthor Commented:
I have tried Yr, Mo, and Dy as text and as integers for the following

CrystalReport1.StoredProcParam(0) = Date(Yr,Mo,Dy)

I keep getting an error.  The error is:  "Expected ("

Don't understand the error.

0
 
mlmccCommented:
Is this using the RDC or the ActiveX control?

mlmcc
0
 
PiterCommented:
You have to Format date like...
fromdt = CDate(Format(from_date1, "dd - MMM - yyyy"))
todt = CDate(Format(to_date1, "dd - MMM - yyyy"))
where fromdt and todt are decleared as public.


0
 
perezmAuthor Commented:
mlmcc,

I believe I am using ActiveX control.  Under components I added, the file name for Crystal Report Control is Crystl32.OCX.



0
 
perezmAuthor Commented:
Piter,

Using your code, I am prompted for a new date and time.  It does not accept the date that I am passing it.  When I enter just the date, I get a blank sheet.  When I enter date and time, I get a blank sheet.

I believe that something is missing in the syntax because "cdate" always gives me the date 2/19/2003 no matter how I format a valid date before hand.
0
 
PiterCommented:

In your report You have to declear two Default "Parameters" with name fromdt and todt.
Which you can do thro' -- INsert -> Parameter Field -- New
then assigne two parameters with fromdt and todt.
But in The form throught which you are sending these parameters should declear public in Module or on same form
and on you form two date controls should persent. and that two dates should have name from_date1 and to_date1.
i hope you understand.

 public fromdt as String
 public todt as public.

try out.
0
 
vbjohnCommented:
This is how I am doing it.

1.) I create an empty formula field and name it: EnterDate then I put it somewhere on the report.

2.) Go into your visual basic code and enter this in.

CrystalReport1.ReportFileName = App.Path & "\Report.rpt"
CrystalReport1.Connect = "pwd=*****;username=sa"
CrystalReport1.WindowTitle = "Your Report Name"

CrystalReport1.Formulas(0) = "EnterDate = '" & TextBox.text & "'"

CrystalReport1.Action = 1


I use a Crystal Report .OCX for this.  I hope this helps.


John-
0
 
mlmccCommented:
Assumption text is entered as dd/mm/yyyy

change

CrystalReport1.Formulas(0) = "EnterDate = '" & TextBox.text & "'"

to

CrystalReport1.Formulas(0) = "EnterDate = '" & date(right(TextBox.text,4), mid(TextBox.text,4,2), left(TextBox.text,2)) & "'"

Make adjustments to the left, right, and mid to accomodate the correct format.
the call to date is
Date(YYYY,MM,DD)

mlmcc
0
 
perezmAuthor Commented:
I would like to express my appreciation to everyone who attempted to provide me with assistance to solving my syntax problem.  I tried everyone’s alternative solutions several times with various twists, etc. without any success.  Therefore, I called Seagate for help.

Because I am using Access (date/time field) at the backend of my program, I need a date/time parameter to send to Crystal Reports.

According to Seagate, the syntax to solve my problem is as follows:

CrysDate = Yr & "-" & Mo & "-" & Dy & " " & "00:00:00.000"

CrystalReport1.StoredProcParam(0) = CrysDate

After a slight modification to my database, the above syntax worked fine.

Consequently, I would like to delete this question since I have a solution that works.
0
 
SpideyModCommented:
A request for a refund has been made.  Experts you have 72 hours to object.

SpideyMod
Community Support Moderator @Experts Exchange
0
 
SpideyModCommented:
PAQ'd and points refunded.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now