Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

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


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
baldocute
Asked:
baldocute
  • 16
  • 11
  • 7
2 Solutions
 
mlmccCommented:
0
 
ClivenCommented:


try this

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = #" & datevalue(text1) & "#"
0
 
ClivenCommented:
or try this

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = #" & Formay(text1.text,"dd/mm/yyyy") & "#"
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ClivenCommented:
or try this

CrystalReport1.selectionformula="select <list of fields> from <table name> where trandate = #" & Format(text1.text,"dd/mm/yyyy") & "#"
0
 
mlmccCommented:
Did you look at the duplicate where other comments were supplied?

mlmcc
0
 
baldocuteAuthor Commented:


what if a selected field is needed to display in the crystal report? how would i specify the needed field?
0
 
mlmccCommented:
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
 
baldocuteAuthor Commented:
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
 
baldocuteAuthor Commented:

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
 
mlmccCommented:
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
 
baldocuteAuthor Commented:
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
 
mlmccCommented:
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
 
baldocuteAuthor Commented:
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
 
baldocuteAuthor Commented:
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
 
ClivenCommented:
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
 
baldocuteAuthor Commented:
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
 
mlmccCommented:
ARe you using the OCX or the RDC?

mlmcc
0
 
baldocuteAuthor Commented:
i am using OCX
0
 
mlmccCommented:
Try
CrystalReport1.ReplaceSelectionFormula "{trans.transdate}='"&datevalue(text1)&"'"

What database are you using?

mlmcc
0
 
baldocuteAuthor Commented:
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
 
baldocuteAuthor Commented:


i am using ( .mdb) msacess database
0
 
mlmccCommented:
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
 
baldocuteAuthor Commented:

 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
 
mlmccCommented:
Try this

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

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

mlmcc
0
 
baldocuteAuthor Commented:

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
 
baldocuteAuthor Commented:

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
 
ClivenCommented:
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
 
mlmccCommented:
Try

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

mlmcc
0
 
baldocuteAuthor Commented:
sure thing cliven: what is your email add. try to send it tomorrow:
0
 
baldocuteAuthor Commented:
done that but still i encounter and error.
0
 
baldocuteAuthor Commented:
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
 
ClivenCommented:
Sorry Baldocute
i forgot to put my email address

Cliven@itzone.com.my

0
 
ClivenCommented:
Sorry Baldocute
i forgot to put my email address

Cliven@itzone.com.my

0
 
mlmccCommented:
>>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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 16
  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now