Solved

Passing date field from VB to crystal report

Posted on 2004-08-07
8
1,028 Views
Last Modified: 2013-12-25
I have a VB program where i am prompting user to enter the fromdate and todate in a datepicker.

This i am trying to pass to an crystal report.

The crystal report is getting pupolated using a stored procedure. This stored procedure takes 2 parameters as datetime: fromdate and todate

Now when i am trying to open my reports in crystal report everything is working fine but when i am trying to pass value from Vb using following code:

        .ParameterFields(0) = "fromdate;DateTime(" & l_intFromYY & "," & l_intFromMM & "," & l_intFromDD & ");TRUE"
        .ParameterFields(1) = "todate;DateTime(" & l_intToYY & "," & l_intToMM & "," & l_intToDD & ");TRUE"


its throwing error as

err.description : Invalid parameter field value & err.number : 20553

However my other reports which are getting populated from simple table is working fine.

I tried using

        .ParameterFields(0) = "fromdate;Date(" & l_intFromYY & "," & l_intFromMM & "," & l_intFromDD & ");TRUE"
        .ParameterFields(1) = "todate;Date(" & l_intToYY & "," & l_intToMM & "," & l_intToDD & ");TRUE"

Need help of experts..
0
Comment
Question by:pg_india
8 Comments
 
LVL 3

Author Comment

by:pg_india
ID: 11746186
i am using crystal report 8.0 version.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 11746240
You need to include the time with the parameter.  Here is how I did it with the OCX

    cr_Generic_Report.ParameterFields(0) = txt_First_Param_Name & ";Date(" & _
                            Mid(txt_First_Date, 7, 4) & "," & _
                            Mid(txt_First_Date, 4, 2) & "," & _
                            Mid(txt_First_Date, 1, 2) & _
                            ")Time(00,00,00,000);True"
    cr_Generic_Report.ParameterFields(1) = txt_Second_Param_Name & ";Date(" & _
                            Mid(txt_Second_Date, 7, 4) & "," & _
                            Mid(txt_Second_Date, 4, 2) & "," & _
                            Mid(txt_Second_Date, 1, 2) & _
                            ")Time(00,00,00,000);True"


Another example from Crystal
http://support.businessobjects.com/communityCS/FilesAndUpdates/SCR_VB_OCX_Parameter.exe.asp

mlmcc
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 11746486
have you made sure that the months and days come out as 2 digits?

e.g.  
 .ParameterFields(0) = "fromdate;Date(" & l_intFromyy & RIGHT('00' & l_intfromMM, 2) & right('00' & l_intfromdd,2) & ");TRUE"
        .ParameterFields(1) = "todate;Date("l_inttoyy & RIGHT('00' & l_inttoMM, 2) & right('00' & l_inttodd,2)  & ");TRUE"

0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 13

Expert Comment

by:EwaldL
ID: 11747090
i guess you are using the ocx?

It seems that it would be bes to use the .StoredProcParam when passing a datetime value to a stored procedure. something like
CrystalReport1.StoredProcParam(1) = "1998-01-01 00:00:00.000"

check this out
http://support.businessobjects.com/library/kbase/articles/c2005624.asp
0
 

Expert Comment

by:JOAT_MON
ID: 11785397
Another possible solution:
We pass our datetime fields as text strings parameters from VC++ through the report to the stored proc and then convert them at the beginning of the proc.
0
 

Expert Comment

by:AtifAqeel
ID: 11924866

A Line from my module, it's working, Try this

Formula = "{cmd_tOrder.DateUpdated} >= DateTime(" & dtpFrom.Year & "," & dtpFrom.Month & "," & dtpFrom.Day & ",00,00,00)"

'Explanation
{cmd_tOrder.DateUpdated} = Table.Field
DateTime is Crystal Report 9's Date/Time function
dtpFrom is the date control in VB

pass Formula to RecordSelectionFormula

hope it will work
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 12152930
PAQed - no points refunded (of 200)

GhostMod
Community Support Moderator
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA saving file message display 5 63
MS Access - Capture pressed key onclick 4 32
Question about INSTR  - need to extract data MS ACCESS query 6 54
Problem to With line 4 57
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now