Solved

Crystal Query Won't work

Posted on 2011-09-11
8
248 Views
Last Modified: 2012-06-21
I have a a database which moves data from one table to another when a puchase order is closed on the system
I am trying to set a formula which will pick the data from the appropriate table (@Period), but it only retrieves data from one table (REQ)
I have modified the formula (@Period2) whereby it retrieves the data from the other table (POREQHVW)
Attached word doc with screen shoot and formulae
CR-Period.doc
0
Comment
Question by:GiaHughes
  • 5
  • 2
8 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 450 total points
ID: 36518687
If I understand what you intend here then the if structure is incorrect as everthing depends on the result of the first if..

I think this is what I would be doing..

@Period =
Stringvar result ;
if isnull ({POREQHVW.APPROVEDDATE}) and  isnull({REQ.APPROVEDDATE}) then result:=”Invalid data”;
If not isnull ({POREQHVW.APPROVEDDATE}) and not  isnull({REQ.APPROVEDDATE}) then result:=”Invalid data”;

if isnull ({POREQHVW.APPROVEDDATE}) and  not isnull({REQ.APPROVEDDATE}) then
(
 if {REQ.APPROVEDDATE}in Date (2010,04,01 ) to  Date (2010,04,30 ) then result:= "2010-P04APR"
 Else if {REQ.APPROVEDDATE}in Date (2010,05,01 ) to  Date (2010,05,31 ) then result:=”2010-P05MAY"
Else if {REQ.APPROVEDDATE}in Date (2010,06,01 ) to  Date (2010,06,30 ) then result:= "2010-P06JUN"
Else if {REQ.APPROVEDDATE}in Date (2010,07,01 ) to  Date (2010,07,31 ) then result:= "2010-P07JUL"
else if….
. etc…..

 else Result:=”New Periods Required"
)
// otherwise ({POREQHVW.APPROVEDDATE}) must be null
Else
(
 if {REQ.APPROVEDDATE}in Date (2010,08,01 ) to  Date (2010,08,31 ) then result :=  "2010-P08AUG"
 else if  {REQ.APPROVEDDATE}in Date (2010,09,01 ) to  Date (2010,09,30 ) then result:= "2010-P09SEP"
else if {REQ.APPROVEDDATE}in Date (2010,10,01 ) to  Date (2010,10,31 ) then result:= "2010-P10OCT"
else  etc…
else result:= "New Periods Required"
);
Result
0
 

Author Comment

by:GiaHughes
ID: 36518708
Hi Peter57r

Good to hear from you
Not sure if I did the right thing
Copied all below @Period =
But it says the formula is incorrect
it says a number,etc is required after
Else if {REQ.APPROVEDDATE}in Date (2010,07,01 ) to  Date (2010,07,31 ) then result:= "2010-P07JUL"
else if…

Gordon
0
 

Author Comment

by:GiaHughes
ID: 36518710
Hi Peter57r
I guess I have to fill in the etc part
Gordon
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:GiaHughes
ID: 36518784
Hi Peter57r
I have added the new lines as shown in the code attached
It says a boolean is required at Else Result:="New Periods Required"

Also should the second part refer to the POREQHVW.APPROVALDATE field rather than repeating the REQ.APPROVALDATE field

Appreciate you help
Gordon
Stringvar result ;
if isnull ({POREQHVW.APPROVEDDATE}) and  isnull({REQ.APPROVEDDATE}) then result:="Invalid data";
If not isnull ({POREQHVW.APPROVEDDATE}) and not  isnull({REQ.APPROVEDDATE}) then result:="Invalid data";

if isnull ({POREQHVW.APPROVEDDATE}) and  not isnull({REQ.APPROVEDDATE}) then
(
 if {REQ.APPROVEDDATE}in Date (2010,04,01 ) to  Date (2010,04,30 ) then result:= "2010-P04APR"
Else if {REQ.APPROVEDDATE}in Date (2010,05,01 ) to  Date (2010,05,31 ) then result:="2010-P05MAY"
Else if {REQ.APPROVEDDATE}in Date (2010,06,01 ) to  Date (2010,06,30 ) then result:= "2010-P06JUN"
Else if {REQ.APPROVEDDATE}in Date (2010,07,01 ) to  Date (2010,07,31 ) then result:= "2010-P07JUL"
Else if {REQ.APPROVEDDATE}in Date (2010,08,01 ) to  Date (2010,08,31 ) then result = "2010-P08AUG"
Else if {REQ.APPROVEDDATE}in Date (2010,09,01 ) to  Date (2010,09,30 ) then result = "2010-P09SEP" 
Else if {REQ.APPROVEDDATE}in Date (2010,10,01 ) to  Date (2010,10,31 ) then result = "2010-P10OCT"
Else if {REQ.APPROVEDDATE}in Date (2010,11,01 ) to  Date (2010,11,30 ) then result = "2010-P11NOV"
Else if {REQ.APPROVEDDATE}in Date (2010,12,01 ) to  Date (2010,12,31 ) then result = "2010-P12DEC"
Else if {REQ.APPROVEDDATE}in Date (2011,01,01 ) to  Date (2011,01,31 ) then result = "2011-P01JAN"
Else if {REQ.APPROVEDDATE}in Date (2011,02,01 ) to  Date (2011,02,28 ) then result = "2011-P02FEB"
Else if {REQ.APPROVEDDATE}in Date (2011,03,01 ) to  Date (2011,03,31 ) then result = "2011-P03MAR"
Else if {REQ.APPROVEDDATE}in Date (2011,04,01 ) to  Date (2011,04,30 ) then result = "2011-P04APR"
Else if {REQ.APPROVEDDATE}in Date (2011,05,01 ) to  Date (2011,05,31 ) then result = "2011-P05MAY"
Else if {REQ.APPROVEDDATE}in Date (2011,06,01 ) to  Date (2011,06,30 ) then result = "2011-P06JUN"
Else if {REQ.APPROVEDDATE}in Date (2011,07,01 ) to  Date (2011,07,31 ) then result = "2011-P07JUL"
Else if {REQ.APPROVEDDATE}in Date (2011,08,01 ) to  Date (2011,08,31 ) then result = "2011-P08AUG"
Else if {REQ.APPROVEDDATE}in Date (2011,09,01 ) to  Date (2011,09,30 ) then result = "2011-P09SEP"
Else if {REQ.APPROVEDDATE}in Date (2011,10,01 ) to  Date (2011,10,31 ) then result = "2011-P10OCT"
Else if {REQ.APPROVEDDATE}in Date (2011,11,01 ) to  Date (2011,11,30 ) then result = "2011-P11NOV"
Else if {REQ.APPROVEDDATE}in Date (2011,12,01 ) to  Date (2011,12,31 ) then result = "2011-P12DEC"
Else if {REQ.APPROVEDDATE}in Date (2012,01,01 ) to  Date (2012,01,31 ) then result = "2012-P01JAN"
Else if {REQ.APPROVEDDATE}in Date (2012,02,01 ) to  Date (2012,02,28 ) then result = "2012-P02FEB"
Else if {REQ.APPROVEDDATE}in Date (2012,03,01 ) to  Date (2012,03,31 ) then result = "2012-P03MAR"
Else Result:="New Periods Required"
)
// otherwise ({POREQHVW.APPROVEDDATE}) must be null
Else
(
 if {REQ.APPROVEDDATE}in Date (2010,08,01 ) to  Date (2010,08,31 ) then result :=  "2010-P08AUG"
else if  {REQ.APPROVEDDATE}in Date (2010,09,01 ) to  Date (2010,09,30 ) then result:= "2010-P09SEP"
else if {REQ.APPROVEDDATE}in Date (2010,10,01 ) to  Date (2010,10,31 ) then result:= "2010-P10OCT"

else result:= "New Periods Required"
);
Result

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 36518848
You have missed out the : from a lot of your assignments..(Line 11 onwards)
0
 

Author Comment

by:GiaHughes
ID: 36519244
Hi Peter57r

Ok done that bit now shows no errors

Do I change the bottom section to {POREQHVW.APPROVALDATE} field rather than repeating the {REQ.APPROVALDATE} field

Gordon
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 50 total points
ID: 36519291
Yes

mlmcc
0
 

Author Closing Comment

by:GiaHughes
ID: 36519529
Excellant response as usual
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now