Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

crystal report.

Posted on 1999-07-21
19
Medium Priority
?
381 Views
Last Modified: 2010-05-02
is that a way i can do to filtering the records display in the report which i create with crystal report according to the period date i key in a form. if yes, please show me the way. thanks.
0
Comment
Question by:pohkong
[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
  • 4
  • 4
  • +2
19 Comments
 
LVL 2

Expert Comment

by:JaganMohan
ID: 1526338
You can do this using the Parameter concept in Crystal Reports:

You have to create a Parameter called "InvoiceNo" in the Crystal reports

Below I am giving some sample code:

    Dim strParm As String
    Dim strInvRecNo As Integer
    ' strInvRecNo is the value you can key in in the form
    strParm = "InvoiceNo;" & strInvRecNo & ";TRUE"
    'CrtInvoice is the Crystal Report Control Name
    CrtInvoice.ReportFileName = App.Path & "\rptinvoice.rpt"
    'Passing Parameter
    CrtInvoice.ParameterFields(0) = strParm
    'Options to show/Hide in the report
    CrtInvoice.WindowMinButton = False
    CrtInvoice.WindowMaxButton = False
    CrtInvoice.WindowState = crptMaximized
    CrtInvoice.DiscardSavedData = True
    CrtInvoice.Destination = crptToWindow
    CrtInvoice.Action = 1
0
 
LVL 1

Expert Comment

by:apratima
ID: 1526339
pohkong can you explain your question in simpler manner, if you don't satisfy with jaganmohan.
I don't understad why do you need complicated and unsecure ways to pass parameter when SelectionFormula is the simplest way available.
0
 

Author Comment

by:pohkong
ID: 1526340
actually i have try to do the filtering using the SelectionFormula and the field in the report is date/time format. and i want get the records according to the period date i key in. and the formula i key in is like this :
{incident.logged date} > ' " & start_txb.text & " ' and  {incident.logged date} > ' " & end_txb.text & " '
but no successful. why???
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:pohkong
ID: 1526341
actually i have try to do the filtering using the SelectionFormula and the field in the report is date/time format. and i want get the records according to the period date i key in. and the formula i key in is like this :
{incident.logged date} > ' " & start_txb.text & " ' and  {incident.logged date} > ' " & end_txb.text & " '
but no successful. why???
0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1526342
In your syntax you compare a datefield from your database to a textfield from your vb-app, right?
If so, you need to apply datatype conversion.
You also need to add brackets () at the right places and to apply the same format to your date as used in Crystal Reports.

The correct syntax to get records according to a certain period would be something like:

YourReport.SelectionFormula = "({Incident.LoggedDate}
in Date(" & Format(cDate(start_txb) , "yyyy,mm,dd") & ")
to Date(" & Format(cDate(end_txb), "yyyy,mm,dd") & "))"

I was not able to test this because i have no reports available right now, but I used it before in a vb-app and it works for me!
0
 
LVL 1

Expert Comment

by:apratima
ID: 1526343
try pietjepuk's solution, it will do.
There is one more method by which you can pass the parameters to selection formula, if you want I can tell you.
0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1526344
Apratima,

If you can spare the time, i'd sure like to know. In my experience writing dynamic code to create complicated SelectionFormulas can be time consuming, with all the brackets and stuff. So, if you know a better way please share it with us.
0
 
LVL 1

Expert Comment

by:apratima
ID: 1526345
Thanks,
I prefer this way because for that you don't have to change VB code.
In your database you have to create a table say temp which will contain only one record having necessary fields such as FrDate (Date Type), ToDate (Date Type), Code1 (Text 50), Code2 (Text 50).
Generally Code1 and Code2 can be used for multipurpose parameter passing

In your VB code whenever you will run Report option before giving command CrystalReport1.Action you will edit the one and only one record of your temporary table. Necessary Field values will be accepted from controls in which we took input from the user and save the record.

Now in Your Report in design mode you will add this temp table and Create a Record Selection Formula in which you will use required fields from temp table.

Whenever changes required in selection formula you can chage it from report without changing code.

No of fields in Temp table depends on everyone's requirement.
Only in code you have to make sure that in there is one and only one record exists in Temp table.

0
 

Accepted Solution

by:
samirtapde earned 200 total points
ID: 1526346
Make slight variation to pietjepuk's code:

YourReport.SelectionFormula =
"(
{Incident.LoggedDate}
>=
Date(" & Format(cDate(start_txb) , "yyyy,mm,dd") & ")
AND
{Incident.LoggedDate}
<=
Date(" & Format(cDate(end_txb), "yyyy,mm,dd") & ")
)"

for all dates from start_txb to end_txb
0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1526347
I fail to see what samirtapdes 'answer' adds to the proposed solutions.
0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1526348
Apratima,

Thanks for your solution. In development surroundings where rules on database design aren't too strict this can be a very practical way of handling parameters.

Earth to pohkong..........

0
 

Author Comment

by:pohkong
ID: 1526349
pietjepuk,
i also get the error in formula after try your method. if i input the date using three text box, and insert the "/" among month, day, year in code, i can get the report during the period i key in.
i display the formula i a textbox, it seen that the formula must in {incident.logged date} = '1998/03/19' for the formula to function.
0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1526350
The reason for formatting the date with commas instead of slashes is for Crystal Reports 'Date' function to work. The syntax used is Date(YYYY, MM, DD)

{incident.logged date} is a field from your database, right?
Is the datatype of this field in your database 'Date' or 'Date/Time', because in that case my syntax should work. If the datatype of {incident.logged date} is text then you can leave out the datatype conversion stuff because you are not comparing dates but strings.

Can you specify the error you get?
0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1526351
Pohkong,

I have just tested my syntax and it works with every possible date format. The text in your textboxes can be
12-12-1998, 12/12/98, 12,dec,1998, 12 12 98, it makes no diffence and works perfectly every time. So my guess is your database field does not have a date datatype, or else something went wrong while copying my syntax.

If my first guess is right you'll have to correct this problem in your database or in your report before you can compare dates. You simply cannot compare a string to a date without some kind of dataconversion. You can compare a string to a string like in your syntax "{incident.logged date} = '1998/03/19'" but you can only check whether it's equal, not whether it's greater or smaller, before or after.
0
 
LVL 1

Expert Comment

by:apratima
ID: 1526352
pohkong even if use of cdate won't work then why don't you try the solution one I given using temp table. It works without fail.
0
 

Author Comment

by:pohkong
ID: 1526353
pietjepuk,

{incident.logged date} is a  field in database and it's in date/time format. any problem if the format is like this?
i still can compare the whether it's equal, greater and smaller using my syntax. the problem is the syntax is too long,


textselectionformula = "{incident.logged date} > '" & start_year_txb.text & / & start_month_txb.text & / & start_day_txb.text & "' and {incident.logged date} < '" & end_year_txb.text & / & end_month_txb.text & / & end_day_txb.text & "' "

0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1526354
Well, I'm out of useful proposals.
I have used SelectionFormulas which were much longer than the one printed in your last comment and never encountered a problem as to their length.
Besides, I do not see much difference between reading "12/12/98" from one textbox, or reading the numbers from three textboxes and inserting slashes afterwards. The only thing that matters is the way your SelectionFormula looks, not the source of the values.
As I mentioned, I have extensively tested my syntax and it works every time, so the problem has to be somewhere else.
Try Apratima's solution, it can't fail.
0
 

Expert Comment

by:samirtapde
ID: 1526355
pietjepuk,
In my answer I have used >= and <= instead of in - to;

It works! And it should in your case too.
0
 
LVL 1

Expert Comment

by:pietjepuk
ID: 1526356
Samirtapde,

I realise your syntax is not exactly the same as mine. However, it provides no functionality mine doesn't, it's just longer. Your answer is a rephrase of my solution. It will surely work, as will mine, but somehow not for Pohkong.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Suggested Courses

715 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