?
Solved

Crystal Query Won't work

Posted on 2011-09-11
8
Medium Priority
?
274 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
[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
  • 5
  • 2
8 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1800 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
Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

 

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 101

Assisted Solution

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

mlmcc
0
 

Author Closing Comment

by:GiaHughes
ID: 36519529
Excellant response as usual
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

800 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