Passing Parameter from VB to Crystal Report

[daneb]

Hi.

I just would like to ask how to pass parameter data from VB to Crystal Report. I have created reports before but these only use groupings and sortings and just displaying data from the database. What if, for instance, certain values have to be user-inputted, must be displayed in the report itself and must be used in filtering records, such as in the sample report heading below:

Cost of Goods Sold
Branch: {branch_name}
From {date1} To {date2}
Printed on: {date_today}

where {branch_name}, {date1}, and {date2} are all user-supplied and {date_today} is the system date. How can I pass these data from VB to the report? Does the Crystal Report version matter in the procedure for passing data? I'm using Crystal Report v4.6.

Thanks for the info in advance!

[Cimperiali]

Have a look here (second example may help you)
http://codeguru.com/cgi-bin/bbs/wt/showpost.pl?Board=vb&Number=50703&page=&view=&sb=
You may also make a search here (look on top, where there are:
Forum Index|Faq|...|Search|Unanswered|... and click on "Serach") for
the word "Crystal".

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[daneb]

Thanks for the reply.

Actually, I tried passing values, displaying them in the report, and using them in record filtering. I tried different data types such as string and numeric (integer, currency) and encountered no problem. But when I used a date type, I encountered this error, pointing to the .Action = 1 line:

Run-time error: '20515':
Error in formula <Record_Selection>
'{Profile.BDAY}>=01/01/2000 AND {Profile.BDAY}<=12/31/2999'
A date is required here

Below is my code:

Private Sub CmdShowReport_Click()

Dim strFromBDay As String, strToBDay As String
Dim strFromAge As String, strToAge As String
Dim strName as String
Dim zAmt As Currency
Dim zCount As Integer

strName = "dan"
strFromBDay = "1/1/2000"
strToBDay = "12/31/2999"
strFromAge = "1"
strToAge = "10"
zAmt = 100
zCount = 5

With CrystalReport1
.Destination = crptToWindow
.DiscardSavedData = True
.Formulas(0) = "FromAge=" & strFromAge
.Formulas(1) = "FromBDay='" & strFromBDay & "'"
.Formulas(2) = "Name='" & strName & "'"
.Formulas(3) = "ToAge=" & strToAge
.Formulas(4) = "ToBDay='" & strToBDay & "'"
.Formulas(5) = "zAmt=" & zAmt
.Formulas(6) = "zCount=" & zCount
.SelectionFormula = "{Profile.BDAY}>=" & CDate(strFromBDay) & " AND {Profile.BDAY}<=" & CDate(strToBDay)
'.SelectionFormula = "{Profile.AGE}>=" & strFromAge & " and {Profile.AGE}<=" & strToAge
'.SelectionFormula = "{Profile.NAME}='dan'"
'.SelectionFormula = "{Profile.AMT}=" & zAmt
'.SelectionFormula = "{Profile.COUNT}=" & zCount
.WindowTitle = "Sample Report"
.ReportFileName = App.Path & "\Reports\Walalang.rpt"
.Action = 1
.Formulas(0) = "FromAge=''"
.Formulas(1) = "FromBDay=''"
.Formulas(2) = "Name=''"
.Formulas(3) = "ToAge=''"
.Formulas(4) = "ToBDay=''"
.Formulas(5) = "zAmt=''"
.Formulas(6) = "zCount=''"
End With

End Sub

Commented lines ran successfully without errors. The BDAY field is of Date/Time data type. FromAge, FromBDay, Name, ToAge, ToBDay, zAmt, and zCount are all formula fields in the report. Any suggestions? Thanks!

[Cimperiali]

.SelectionFormula = "{Profile.BDAY}>=" & CDate(strFromBDay) & " AND {Profile.BDAY}<=" & CDate(strToBDay)

Cdate is not the right function:
try this to see:

Dim strFRomBday As String
strFRomBday = "10/12/2001"
MsgBox Format(strFRomBday, "dd/mm/yyyy")
strFRomBday = "10,12,2001"
MsgBox CDate(strFRomBday)

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[daneb]

Thanks again for the response. I tried using the "01,01,2001" format and the CDate function again in the .SelectionFormula line, but still I got the same error. I don't think I got your answer right. Why is it that for date types, I'm having this problem whereas for other data types, the code worked fine. Please help.

[Cimperiali]

In your case it may be:
"01/01/2001"
or
"01:01:2001"
or
"01;01;2001"
depending on general settings in your control panel... Have a try.

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[daneb]

Hi again.

The date format in my Regional Settings is "01/01/2001". Actually, it is not in the assigning of values to the formulas in Crystal Report that produces the error, it is in the .SelectionFormula line itself. This is a simple record filtering process, what seems to be the error?

[Cimperiali]

sql = "{persona.Cognome}='Imperiali'"
this means you have a table named "persona" in which there is a field named "Cognome" and it is like you write
select....where persona.Cognome='Imperiali'
you have to:
1)match exact case (upper/lower letters)
2)be sure to have selected fields in query that you would like to use in where clause
3) match the type:
for string use 'xxx'
for number avoid the ' and '
for date:
try with: '#yourdate#'
or with no ' like if it was a number

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[daneb]

Yes, you're right. The .SelectionFormula can easily be translated into SQL statements, and I didn't have errors when using other data types. I have done #1, and #3, though it is in the date data type that I encounter errors. I also tried to put # before and after in the .SelectionFormula but another error appeared. For #2, where will this SQL query be placed? I have done everything, from manipulating codes in VB to manipulating formulas and record selection properties in Crystal, but to no avail. I can't figure out what's wrong with date data type!

[Cimperiali]

'You may use a parameter field for the date
'and then use a formula inside a section to filter records

'(Special thanks to SurrenderMonkey for this)
'A simply report with one parameter field
'called SelectMe in a omit formula for a section.
'Matter is not to make the selection but to pass a parameter
'value to a rpt file
'or: to make parameter fields to be filled
'via code form VB.
Cr1.Reset
'your rpt file
Cr1.ReportFileName = "D:\Condivisa\Cesarevb\Crystol\cryweb\Report1.rpt"
Cr1.ReportSource = 0
Cr1.DiscardSavedData = true
'instead of "1", use the data
Cr1.ParameterFields(1) = "SelectMe;1;false" 'parameter field name={?SelectMe}
'Cr1.ParameterFields(0) = "SelectMe;1;false"
Cr1.Action = 1 'make it print




Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[daneb]

I'm using Crystal Report v4.6. I didn't find Parameter Fields in any of the menus. In recent Crystal versions, there is a Field option in the Insert menu, where you can specify a parameter field. Where will I filter records?

[Cimperiali]

Then I think you should try different kinds of format till you find the correct
sintax. Sorry I could not help more.

Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[daneb]

Okay. Thanks for all the suggestions. I'll just try different formats for the Crystal Report and in the VB code.

[Shiv Kumar G.M.]

For date fields, you have use this function Datetime something like
CrystalReport.recordselectionformula = "{DataEnvironment1.Command1.BirthDate = DateTime(Year,Month,Day,0,0,0}"

This is the way how I am working, even today also.


If, this, doesn't work, reply back.

[daneb]

Thanks for the reply. I just would like to ask what's the DataEnvironment1.Command1 for? Well, I commented my .SelectionFormula line and just edited the formula in the Select Records Expert in Crystal Report. The report appeared, but couldn't get the correct records that pass the condition. My formula in the report is this:

ToText(Year ({Profile.BDAY}))>= {@FromBDay} [7 to 10] and ToText(Year ({Profile.BDAY}))<= {@ToBDay} [7 to 10]

BDAY is a Date/Time field in Long Date format in the Profile table. This means that only those records having BDAY year within the two years specified must appear in the report. For example, if @FromBDay = "01/01/1900" and @ToBDay = "12/31/2000", then {@FromBDay} [7 to 10] = "1900" and {@ToBDay} [7 to 10] = "2000", and the report must show only those records born from 1900 to 2000. But the report could not detect this. Any ideas?

[Shiv Kumar G.M.]

If, you want to show the report for only some conditions say from 1900 to 2000 then

ToText(Year ({Profile.BDAY}) Between 1900 and 2000)
can be used as the record selection formula.


For example, I am building crystal report based on Dataenvironment, in that I am adding a command1 which is linked to a table(EmpId,Name)
Then condition can be
{Dataenviroment1.Empid}='1' which is your recordselection formula

same way, you can try
right Click on the Year field in Crystal reports(Design view)
Choose Select Expert, chose formula,
copy the formula and alter it.

Any help,
mail back

Thank You.

[daneb]

Hi.

I tried your suggestion and put the code in VB but VB issued this error:

Run-time error: '20515':
Error in formula <Record_Selection>.
'ToText(Year ({Profile.BDAY}) Between 1900 and 2000)'
The ) is missing.

When I enclosed the values 1900 and 2000 with single quotes, the same error appeared.

In Crystal, the same error showed up in the Select Records Expert formula editor.