Solved

Passing date field from VB to crystal report

Posted on 2004-08-07
8
1,032 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA Shell can't Find Word document 11 155
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 64
Dinamic report to Crosstab query 9 59
checkbox to hide entire section 10 75
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

737 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