Solved

Crystal Query Won't work

Posted on 2011-09-11
8
270 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 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 

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 50 total points
ID: 36519291
Yes

mlmcc
0
 

Author Closing Comment

by:GiaHughes
ID: 36519529
Excellant response as usual
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

728 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