Difficult filtering by date...

[Kerbouchard]

I've been working on this for several days and can't seem to get it to filter by date.

There are 2 tables involved a_armast and a_arymst that I am pulling information from. lc_bdate and lc_edate are character fields on a grid that are then converted via function to dates(works fine). I need it to find all the invoices between the two dates selected. invdte in a_armast,a_arymst is the date field in the table. There are a number of other filters on the tables below, all of which work great I just can't get the dates to filter. Argggh! If I haven't given enough information about the program please let me know.

 ld_bdate = g_retdt(lc_bdate) 
 ld_edate = g_retdt(lc_edate)

    IF lc_include = "N"
      *-- New and changed invoices

      *-- Initialize filter and copy expressions
      STORE [VAL(invno) >= VAL(lc_binvno) AND ] + ;
        [VAL(invno) <= VAL(lc_einvno) AND ] + ;
        [&lc_malias.->balance = 0 AND ] + ;
        [&lc_malias.->salsmn1 = lc_salsmn OR ] + ;
        [&lc_malias.->salsmn2 = lc_salsmn OR ] + ;
        [&lc_malias.->salsmn3 = lc_salsmn AND ] + ;
        [&lc_malias.->prtid <> "P"  AND ] + ;
        [artype $ lc_type AND ] + ;
        [LEFT(custno, ln_lc) = lc_xcustno AND ] + ;
        [NOT DELETED()] ;
        TO lc_mfilt, ;
        lc_copy
    ELSE
      *-- All invoices

      *-- Initialize filter and copy expressions
      STORE [VAL(invno) >= VAL(lc_binvno) AND ] + ;
        [VAL(invno) <= VAL(lc_einvno) AND ] + ;
        [&lc_malias.->balance = 0 AND ] + ;
        [&lc_malias.->salsmn1 = lc_salsmn OR ] + ;
        [&lc_malias.->salsmn2 = lc_salsmn OR ] + ;
        [&lc_malias.->salsmn3 = lc_salsmn AND ] + ;
        [artype $ lc_type AND ] + ;
        [LEFT(custno, ln_lc) = lc_xcustno AND ] + ;
        [NOT DELETED()] ;
        TO lc_mfilt, ;
        lc_copy

 [B]**Trouble starts here filtering by date**[/B]
	 IF NOT EMPTY(ld_bdate) AND NOT EMPTY(ld_edate)
	 	SELECT A_ARMAST
	 	set order to invdte1 in a_armast
	 		IF NOT EMPTY(ld_bdate)
	 			SEEK DTOS(ld_bdate)
        		lc_mfilt = lc_mfilt + [DTOS(invdte) >= "] + ;
              		DTOS(ld_bdate) + [" AND ]
            ENDIF
     		IF NOT EMPTY(ld_edate)
     			SEEK DTOS(ld_edate)
        		lc_mfilt = lc_mfilt + [DTOS(invdte) >= "] + ;
              		DTOS(ld_edate) + [" AND ]
 	        ENDIF	
 		Else
 		SET ORDER TO 0 in a_armast
     ENDIF

 

[ramani]

Hi

IF NOT EMPTY(ld_bdate) AND NOT EMPTY(ld_edate)
SELECT A_ARMAST
set order to invdte1 in a_armast
IF NOT EMPTY(ld_bdate)
SEEK DTOS(ld_bdate)
lc_mfilt = lc_mfilt + [ AND ] + ;
[DTOS(invdte) >= DTOS(ld_bdate)]
ENDIF
IF NOT EMPTY(ld_edate)
SEEK DTOS(ld_edate)
lc_mfilt = lc_mfilt + [ AND ] + ;
[DTOS(invdte) <= DTOS(ld_edate)]
ENDIF

A couple of points on your code .. end date is validated for >= where as it should be <=. Also.. usage of [AND] will hang out if the second parameter is not used. The above should take care of that, I believe.





____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Kerbouchard]

Ramani,
Thanks so much for responding and trying to help me. I didn't get an error with the above code buy it didn't filter to the correct dates either.

These are the two tables opened and are used to filter the dates--

  *-- Invoice master file
  IF lc_file = "C"
    ln_armast = g_opnfl(gf_armastf, "a_armast", .f., .f., "invno")
    lc_malias = "a_armast"
  ELSE
    ln_arymst = g_opnfl(gf_arymstf, "a_arymst", .f., .f., "invno")
    lc_malias = "a_arymst"
  ENDIF

Using what I got from Ramanis code and others-- I set the order to the tables above to invdte1 which is DTOS(invdte) from the table and is already set to both tables.

IF NOT EMPTY(ld_bdate) OR NOT EMPTY(ld_edate)
select &lc_malias
set order to invdte1 ascending
go top
	DO CASE
	         CASE NOT EMPTY(ld_bdate) AND NOT EMPTY(ld_edate)
	                  SET NEAR ON
			          SEEK DTOS(ld_bdate)
			          SET NEAR OFF
	          		  lc_mfilt = lc_mfilt + [ AND ] + ;
	                    [DTOS(invdte)>=DTOS(ld_bdate)] + [ AND ] + ;
	                    [DTOS(invdte)<=DTOS(ld_edate)]	
	         CASE NOT EMPTY(ld_bdate)
	                 SET NEAR ON            
	                 SEEK DTOS(ld_bdate)
	                 SET NEAR OFF
	                 lc_mfilt = lc_mfilt + [ AND ] + ;
	                    [DTOS(invdte) >= DTOS(ld_bdate)]
	        
	         CASE NOT EMPTY(ld_edate)
	         		 SET NEAR ON
	                 SEEK DTOS(ld_edate)
	                 SET NEAR OFF
	                 lc_mfilt = lc_mfilt + [ AND ] + ;
	                    [DTOS(invdte) <= DTOS(ld_edate)]
	ENDCASE    
ENDIF

I don't get any errors and it seems OK but then it doesn't filter and it starts spitting out every single invoice in the table. I would really appreciate the help!

 

[justamistere]

I am assuming that there is more to the above code, that is not shown here, such as the following. "SET FILTER TO &lc_mfilt"

After the "GO TOP" I usually issue a "SET FILTER TO" to release any previous filters.

You could also "lc_mfilt = []" to reset the memory variable, unless there is more before or after the above "FILTER" clause. If there is more to this filter clause that is not shown here, the problem may be wth that portion.