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
baldocuteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.