• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Crystal Query Won't work

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
Gordon Hughes
Asked:
Gordon Hughes
  • 5
  • 2
2 Solutions
 
peter57rCommented:
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
 
Gordon HughesDirectorAuthor Commented:
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
 
Gordon HughesDirectorAuthor Commented:
Hi Peter57r
I guess I have to fill in the etc part
Gordon
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Gordon HughesDirectorAuthor Commented:
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
 
peter57rCommented:
You have missed out the : from a lot of your assignments..(Line 11 onwards)
0
 
Gordon HughesDirectorAuthor Commented:
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
 
mlmccCommented:
Yes

mlmcc
0
 
Gordon HughesDirectorAuthor Commented:
Excellant response as usual
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now