Return last value from a table with certain conditions 2

[trick66]

I need assistance with creating a function that will return the last date value from a table that fits two conditions.
This is what I have so far:

PARAMETER RTNO
LOCAL F,DBFTRACE
F=ALIAS()
RETVAL=''
USE DBFTRACE ORDER TAG ROUT_NO ASCENDING IN 0 ALIAS DT AGAIN
SELECT DT
SEEK RTNO
SCAN WHILE ROUT_NO=RTNO AND DESCRIPTIO='Release Work Order'
RETVAL=DTOC(LOG_DATE)
ENDSCAN
USE
SELECT (F)
RETURN RETVAL

But when I incorporate the function into my report, I do not get the correct date value. If I remove the "DESCRIPTIO" condition, then it will return the last date for the first condition with no problems.

Please excuse my ignorance, as I am only an amatuer with programming. Any help is appreciated.

Rick

 

[mspratt]

Hi Rick,

Try this (untested)

PARAMETERS RTNO
SELECT	DBFTRACE.LOG_DATE,  ;
	DBFTRACE.ROUT_NO,  ;	
	DBFTRACE.DESCRIPTIO,  ;
	FROM DBFTRACE ;
	WHERE DBFTRACE.ROUT_NO = RTNO ;
	.AND. DBFTRACE.DESCRIOPTIO = "Release Work Order"
	INTO CURSOR MyCursor

GO BOTTOM
RETURN DTOC(MyCursor.LOG_DATE)

Regards,

Mike

 

[mspratt]

Oops, sorry, missing semi-colon.

PARAMETERS RTNO
SELECT DBFTRACE.LOG_DATE,  ;
	DBFTRACE.ROUT_NO,  ;	
	DBFTRACE.DESCRIPTIO,  ;
	FROM DBFTRACE ;
	WHERE DBFTRACE.ROUT_NO = RTNO ;
	.AND. DBFTRACE.DESCRIOPTIO = "Release Work Order" ;
	INTO CURSOR MyCursor

GO BOTTOM
RETURN DTOC(MyCursor.LOG_DATE)

Regards,

Mike

 

[trick66]

Mike,

Thanks for the info. But when I try to compile it, I get an error message: "Command is missing required clause." for the INTO CURSOR... line. BTW, I am using VFP 6.0 to compile. I ignore the error message and applied the compiled function to the report, but I also get the same error when the report runs.

Any chance you know what clause I am missing. Thanks again.

Rick

 

[KDavie]

Rick,

Make sure you are using the correct code of Mike's. The first one he posted was missing a semicolon.

-Kevin

 

[trick66]

Kevin,

Thanks for the advice. But that is the code that I tried.

Rick

 

[Stefan5627]

Rick,

i noticed there's a comma between DBFTRACE.DESCRIPTIO and FROM that shoul be removed.

PARAMETERS RTNO
SELECT DBFTRACE.LOG_DATE,  ;
    DBFTRACE.ROUT_NO,  ;    
    DBFTRACE.DESCRIPTIO  ;
    FROM DBFTRACE ;
    WHERE DBFTRACE.ROUT_NO = RTNO ;
    .AND. DBFTRACE.DESCRIOPTIO = "Release Work Order" ;
    INTO CURSOR MyCursor

GO BOTTOM
RETURN DTOC(MyCursor.LOG_DATE)

 

[Stefan5627]

The query used gives you the date from the last record entered. If you want the most recent date i suggest you use:

select max(D.log_date) as log_date ;
from dbftrace D  ;
where D.rout_no = rtno and ;
      D.descrioptio = "Release Work Order ;
into cursor mycursor

return dtoc(mycursor.log_date)

 

[trick66]

You guys have been great help. I was able to modify each suggested code and combine them and got it to work. Although
it may not be the orthodox was of doing it.

Thanks for all of you guys help.

Can any one show me how to add that code box in my post?

Rick

 

[Stefan5627]

check out the Process TGML link below the edit box where you type your post. It shows you al lot more than just how to add a code box

good luck,

Stefan

 

[mspratt]

Hi Rick,

Sorry about the delay, I was out having surgery yesterday (Friday). I also noticed, after I added the semicolon, that I can't spell(DESCRIPTIO vs DESCRIOPTIO).

Glad you got it working.

Regards,

Mike