techconcepts
asked on
Retrieve records by date parameter
In VB 6 sp5 using MDB data file, I wrote an application the uses Crystal reports pro 4.6 for its reporting engine. I need to open a report that uses a begining date criteria. I want all records to be displayed after the specified date variable. Actually once this works I want to specify a date span, but first things first. I have tried many ways to make this happen but have been unsuccessful. The following code was tried..
The table "History" has a Date/TIme field in it named OrderDate. The format of the date stored is 7/27/01
The variable is a Date type that gets passed into it the date value from the DTPicker control.
Reports.SelectionFormula = ""
strSelectCriteria = "{History.OrderDate} = " & BegDate
Reports.SelectionFormula = strSelectCriteria
Reports.ReportFileName = App.Path & "\custorderhistory.rpt"
Reports.DataFiles(0) = strPath
Reports.DiscardSavedData = True
Reports.Action = 1
I get a runtime error 20515
(Error in formula... A date is required here)
I also tried a few other things but this is the closest I got I think.
What would be the correct syntax used to do what I need? Can you also specify the correct syntax if I want to have my report generated by passing >= BegDate and <= EndDate?
Thanks in advance
The table "History" has a Date/TIme field in it named OrderDate. The format of the date stored is 7/27/01
The variable is a Date type that gets passed into it the date value from the DTPicker control.
Reports.SelectionFormula = ""
strSelectCriteria = "{History.OrderDate} = " & BegDate
Reports.SelectionFormula = strSelectCriteria
Reports.ReportFileName = App.Path & "\custorderhistory.rpt"
Reports.DataFiles(0) = strPath
Reports.DiscardSavedData = True
Reports.Action = 1
I get a runtime error 20515
(Error in formula... A date is required here)
I also tried a few other things but this is the closest I got I think.
What would be the correct syntax used to do what I need? Can you also specify the correct syntax if I want to have my report generated by passing >= BegDate and <= EndDate?
Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dick3425,
I notice that you have recently joined. Welcome to EE.
In the Crystal Reports topic area, like many other areas on EE, the accepted convention is to leave your suggestions for the questioner as a comment instead of proposing them as an answer. When you post a proposed answer, the question is moved out of the open questions area and into the locked questions area. Most longer term users here (the "experts") generally don't look there for questions to answer. This can deprive the questioner of other suggestions from other experts.
If you feel you have THE 110% right answer, then propose an answer.
Just wanted to save you some grief down the road.
DRRYAN3
I notice that you have recently joined. Welcome to EE.
In the Crystal Reports topic area, like many other areas on EE, the accepted convention is to leave your suggestions for the questioner as a comment instead of proposing them as an answer. When you post a proposed answer, the question is moved out of the open questions area and into the locked questions area. Most longer term users here (the "experts") generally don't look there for questions to answer. This can deprive the questioner of other suggestions from other experts.
If you feel you have THE 110% right answer, then propose an answer.
Just wanted to save you some grief down the road.
DRRYAN3
ASKER
DRRYAN3,
It seems that version 4.6 doesnt accept parameter statement. Maybe you could show me more of an example on how this could lay into the code staement if this works for 4.6. If this works, then you would get the points. I will reject your answer without disrespect in search for either a bit more info on your proposed answer or the final solution to my problem. In dick3425's comment, the links refered to version 5.0 and above that used the parameter statement. Maybe the solution here would be to upgrade to the later ocx report engine of crystal reports although am still hoping for a solution with what version I have.
It seems that version 4.6 doesnt accept parameter statement. Maybe you could show me more of an example on how this could lay into the code staement if this works for 4.6. If this works, then you would get the points. I will reject your answer without disrespect in search for either a bit more info on your proposed answer or the final solution to my problem. In dick3425's comment, the links refered to version 5.0 and above that used the parameter statement. Maybe the solution here would be to upgrade to the later ocx report engine of crystal reports although am still hoping for a solution with what version I have.
ASKER
I have figured out with a little help from all of the above comments the solution to my problem. Dick3425 was correct with saying the format must be converted to yyyy mm dd. DRRYAN3 was also correct with first needing to format the date so that CRW understands it properly. I finally called crystal decisions and with their help was able to pass an exact date criteria to crystal reports. This was fine, but I still needed to be able to pick a date and send it to my report as a variable. The following code is the solution that works if you are interested.
first I stored the value of the 2 date picker controls into variables that were dim'ed as strings. Then I formated the output that crystal reports could understand
BegDate = StartDT.Value
EndDate = EndDT.Value
BegDate = Format(BegDate, "yyyy, mm, dd")
EndDate = Format(EndDate, "yyyy, mm, dd")
then i created the criteria string like this
Dim strSelectCriteria as String
strSelectCriteria = "{History.OrderDate} >= DATE(" & BegDate & ") and_ {History.OrderDate} <= DATE(" & EndDate & ")"
Then passed this to the report
Reports.SelectionFormula = strSelectCriteria
and ta da!!!!
Now my last probelm with this question,
What do I do with the points that I posted? Who should get them?
first I stored the value of the 2 date picker controls into variables that were dim'ed as strings. Then I formated the output that crystal reports could understand
BegDate = StartDT.Value
EndDate = EndDT.Value
BegDate = Format(BegDate, "yyyy, mm, dd")
EndDate = Format(EndDate, "yyyy, mm, dd")
then i created the criteria string like this
Dim strSelectCriteria as String
strSelectCriteria = "{History.OrderDate} >= DATE(" & BegDate & ") and_ {History.OrderDate} <= DATE(" & EndDate & ")"
Then passed this to the report
Reports.SelectionFormula = strSelectCriteria
and ta da!!!!
Now my last probelm with this question,
What do I do with the points that I posted? Who should get them?
techconcepts
give them to me!
Just kidding. You can either decide to
1. Award all points to one expert by clicking on the accept comment as answer link
2. Split the points between two experts by leaving a message in the community support area (https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt) describing what you want to do
3. Award no points because you solved you own problem. Leave a message in community support asking them to PAQ the question and return your points.
4. Ask that your question be deleted. I would ask that you not do this because it does contain information others might find useful.
Glad you got it working
DRRYAN3
give them to me!
Just kidding. You can either decide to
1. Award all points to one expert by clicking on the accept comment as answer link
2. Split the points between two experts by leaving a message in the community support area (https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt) describing what you want to do
3. Award no points because you solved you own problem. Leave a message in community support asking them to PAQ the question and return your points.
4. Ask that your question be deleted. I would ask that you not do this because it does contain information others might find useful.
Glad you got it working
DRRYAN3
Setting up point split as requested here: https://www.experts-exchange.com/jsp/qShow.jsp?ta=commspt&qid=20161535
Reducing points to 50 and accepting dick3425's comment.
Techconcepts, you will still have to open a 50 points question for DRRYAN. Please post a link to this question in here, so that DRRYAN can put a comment in that question.
costello
Community Support Moderator~@ Experts-Exchange
Reducing points to 50 and accepting dick3425's comment.
Techconcepts, you will still have to open a 50 points question for DRRYAN. Please post a link to this question in here, so that DRRYAN can put a comment in that question.
costello
Community Support Moderator~@ Experts-Exchange
http://support.crystaldecisions.com/communityCS/TechnicalPapers/dateparm.exe.asp?AJID=4523819A15BB1046950A1ABA9CA60458
http://support.crystaldecisions.com/kbase/c2006910.asp?AJID=F51C603A73DF2E458956407F4B99E035
DRRYAN3