?
Solved

Can anyone give me the correct parameter in using selection formula?

Posted on 2003-03-06
36
Medium Priority
?
304 Views
Last Modified: 2007-12-19

I am using crystal report 7 in my Vb programming. Problem is I could not pass on a selected value from my program to crystal report; heres how i could my selection:

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = '"&datevalue(text1)&"'"


error encountered is: "error in formula"

please help....thanks
0
Comment
Question by:baldocute
[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
  • 16
  • 11
  • 7
36 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 8086022
0
 
LVL 1

Expert Comment

by:Cliven
ID: 8087095


try this

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = #" & datevalue(text1) & "#"
0
 
LVL 1

Expert Comment

by:Cliven
ID: 8087097
or try this

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = #" & Formay(text1.text,"dd/mm/yyyy") & "#"
0
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.

 
LVL 1

Expert Comment

by:Cliven
ID: 8087098
or try this

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = #" & Format(text1.text,"dd/mm/yyyy") & "#"
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8087851
Did you look at the duplicate where other comments were supplied?

mlmcc
0
 

Author Comment

by:baldocute
ID: 8100780


what if a selected field is needed to display in the crystal report? how would i specify the needed field?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8103057
You cannot change the select fields from VB.  To do that you need to update the SQL for the report and verify the database.

mlmcc
0
 

Author Comment

by:baldocute
ID: 8108562
I both try the parameter that you advised me to put. though during run time I still encounter the error
"error in formula". when i select debug the highlighted part was the crystalreport1.action=1. if you hover the mouse the comment on the speed tip is
"property is write-only" how could i resolve the problem. can you please give me the step.. attach is the logic on how i do it in VB.

private sub command1_click()

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = #" & datevalue(text1) & "#"

CrystalReport1.action=1  ' this part is highlighted when  
                         'i click debug when you hover it
                         'the speed tip gave you an error
                         ' of property is write-only

end sub


i really need help on this one....please....
0
 

Author Comment

by:baldocute
ID: 8108583

i try your given parameter and still encounter same error that is "error in formula"  when i click debug.. the highlighted part was on crystalreport1.action =1; when you hover to it the speed tip tell you that
 "property is write-only" how could i possibly correct it.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8108656
If you had looked at your other question
you would see this comment
You can only pass the where clause.  Also you need to use the full field name.

CrystalReport1.ReplaceSelectionFormula "{ViewName.FieldName} = 'Value'"

Dates must be passed in th proper format.

CrystalReport1.ReplaceSelectionFormula "{ViewName.FieldName} = #"& date(text1)& "#"

mlmcc

Did you put this in exactly as shown?

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = #" & datevalue(text1) & "#"


If so you need to replace <list of fields> with the list of fields in the query that is driving your report.  If it is the table then just use *.

You also need to replace <table name> with the name of the table.

CrystalReport1.selectionformula="select * from  tblSales where trandate = #" & datevalue(text1) & "#"

or

CrystalReport1.selectionformula="select Customer, trandate, SaleAmount from  tblSales where trandate = #" & datevalue(text1) & "#"

mlmcc
0
 

Author Comment

by:baldocute
ID: 8108680
actually i specified the field like what you had advised i follow the

CrystalReport1.selectionformula="select Customer, trandate, SaleAmount from  tblSales where trandate = #" & datevalue(text1) & "#"


still when i run the report; i still get error in formula;

i appreciate any help you can extend..thanks...
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8108932
Were Customer, trandate, SaleAmount the only fields in the query the report is based on?

try
CrystalReport1.selectionformula="select Customer, trandate, SaleAmount from  tblSales where trandate = '" & datevalue(text1) & "'"

mlmcc
0
 

Author Comment

by:baldocute
ID: 8109160
nope; actually the following field are present both in crystal report and VB:
table name is: trans
fields: transcode,description,transdate,debit,credit

so in the command button for print since i only want to specify/display record base on given input date coming from a text box I place

crystalreport1.selectionformula="select transcode,description,transdate,debit,credit from trans where transdate=#"&datevalue(text1)&"#"
crystalreport1.action=1

an error "error in formula" is generated when i click the button for print....what could be wrong....

and furthermore what do u mean by "You can only pass the where clause"...i check the version of my crystal report;;i think that is v4.6 is the formula not supported?

please help





0
 

Author Comment

by:baldocute
ID: 8116935
a little help from mlmcc or anybody? i've been trying to solve this problem in a month and still can figure it out.
0
 
LVL 1

Expert Comment

by:Cliven
ID: 8117270
try this

CrystalReport1.DataFiles(0) = database path
CrystalReport1.SelectionFormula "{trans.transdate=#"&datevalue(text1)&"#"
CrystalReport1.ReportFileName = App.Path & "\xxx.rpt"
CrystalReport1.Action = 1
CrystalReport1.PageZoom (100)
CrystalReport1.SelectionFormula = ""


or this
CrystalReport1.DataFiles(0) = database path
CrystalReport1.SelectionFormula "{trans.transdate='"&datevalue(text1)&"'"
CrystalReport1.ReportFileName = App.Path & "\xxx.rpt"
CrystalReport1.Action = 1
CrystalReport1.PageZoom (100)
CrystalReport1.SelectionFormula = ""



i think should be ok already.
cause this code work well for me.






0
 

Author Comment

by:baldocute
ID: 8133010
My apology but still i get the same problem:
"error in formula". this simple routine really gave me a headache. i dont know if the problem is on the crystal report. i tried changing the search option, still i get the same error
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8133910
ARe you using the OCX or the RDC?

mlmcc
0
 

Author Comment

by:baldocute
ID: 8134158
i am using OCX
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8136554
Try
CrystalReport1.ReplaceSelectionFormula "{trans.transdate}='"&datevalue(text1)&"'"

What database are you using?

mlmcc
0
 

Author Comment

by:baldocute
ID: 8156705
done that already still I encounter error in formula:

i try this option:

CrystalReport1.ReplaceSelectionFormula "{trans.transdate}=Date(2003,18,3)"

the program work: crystal report open the record with date March 18, 2003: but what if the user will input a date on a text box? that's were my problem come in:
0
 

Author Comment

by:baldocute
ID: 8156713


i am using ( .mdb) msacess database
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8156877
Since you are using access try
CrystalReport1.ReplaceSelectionFormula "{trans.transdate}= #" & cstr(Date(2003,18,3) & "#"

Using a text box about the only thing you can do is assume it is in a given format an then convert it to a date.

Have you considered using the datepicker control or a third-party date/calendar control?  That ensures the date is in a known format and can be used directly as a date.

mlmcc

0
 

Author Comment

by:baldocute
ID: 8157592

 if the value of date is constant; i do not encounter a problem; i'll try using a date party tool:: if i will be using an inputbox as an option for entering date: can you please give me the logic on how i pass the value to crystal report: my idea is:


private sub command1_click()
dim str as string
dim quer as date
str=inputbox("Input a Date")
quer=format(str,"mm/dd/yyyy")
CrystalReport1.ReplaceSelectionFormula "{trans.transdate}= "&datevalue(quer)&""
Crystalreport1.action=1
end sub


is there something wrong with my code: why do i encounter error in formula










0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8158392
Try this

quer=Date(Mid(str, 7, 4) , Mid(str, 4, 2), Mid(str, 1, 2))

CrystalReport1.ReplaceSelectionFormula "{trans.transdate}= #" & cst(quer) & "#"

mlmcc
0
 

Author Comment

by:baldocute
ID: 8164280

does the format you gave represent the 'yyyy/dd/mm' because i think crystal report support only such format:

i tried using a third party toll (dtpicker1) but my problem is on how to pass it on the crystal report: because when i use it an error in formula occur: my code is as follows:


private sub command1_click()

CrystalReport1.ReplaceSelectionFormula "{trans.transdate}="& dtpicker1.value &""
CrystalReport1.action=1

end sub

* dtpicker1 is a third party tool that is use for date selection
 

is there something wrong with my code: why do i encounter error in formula
 



 
0
 

Author Comment

by:baldocute
ID: 8164283

does the format you gave represent the 'yyyy/dd/mm' because i think crystal report support only such format:

i tried using a third party toll (dtpicker1) but my problem is on how to pass it on the crystal report: because when i use it an error in formula occur: my code is as follows:


private sub command1_click()

CrystalReport1.ReplaceSelectionFormula "{trans.transdate}="& dtpicker1.value &""
CrystalReport1.action=1

end sub

* dtpicker1 is a third party tool that is use for date selection
 

is there something wrong with my code: why do i encounter error in formula
 



 
0
 
LVL 1

Accepted Solution

by:
Cliven earned 152 total points
ID: 8165173
Below the code is work well for me.
did you try before?
you must put the database put at the datafiles(0) =

CrystalReport1.DataFiles(0) = database path "c:\xxx\xx.mdb"
CrystalReport1.SelectionFormula "{trans.transdate='"&datevalue(text1)&"'"
CrystalReport1.ReportFileName = App.Path & "\xxx.rpt"
CrystalReport1.Action = 1
CrystalReport1.PageZoom (100)
CrystalReport1.SelectionFormula = ""


can you send your rpt file,database and simple program to call the report to my mail?

i try to solve to you
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 8167023
Try

CrystalReport1.ReplaceSelectionFormula "{trans.transdate}= #"& dtpicker1.value &"#"

mlmcc
0
 

Author Comment

by:baldocute
ID: 8172348
sure thing cliven: what is your email add. try to send it tomorrow:
0
 

Author Comment

by:baldocute
ID: 8172357
done that but still i encounter and error.
0
 

Author Comment

by:baldocute
ID: 8172363
from
quer=Date(Mid(str, 7, 4) , Mid(str, 4, 2), Mid(str, 1, 2))

a syntax error is pointing to Date(Mid
  error encounter is    ')' expected
0
 
LVL 1

Expert Comment

by:Cliven
ID: 8172837
Sorry Baldocute
i forgot to put my email address

Cliven@itzone.com.my

0
 
LVL 1

Expert Comment

by:Cliven
ID: 8172845
Sorry Baldocute
i forgot to put my email address

Cliven@itzone.com.my

0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 148 total points
ID: 8173444
>>does the format you gave represent the 'yyyy/dd/mm' because i think crystal report support only such format:

No it assumes dd/mm/yyyy

mlmcc
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

770 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