Solved

Crystal Query Won't work

Posted on 2011-09-11
8
262 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

730 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