topic-40277

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Set Filter in Tdatabase/tdata
Posted: Mon Apr 19, 2021 09:44 AM

I have a problem with a Filter

I must filter the Reserva.dbf
local cTipoElemento:="01"
local nElemento:= 5
local dDataIniziale:= ctod("04/07/2021")
local dDataFinale:= ctod("08/07/2021")

and I made this command

oReservations:Exec( < ||
SET FILTER TO AllTrim( FIELD->TYPE ) = AllTrim( cTipoElemento ) .and. FIELD->NUM = nElemento ;
.AND. ( dDataIniziale <= FIELD->CHECK_OUT .AND. dDataFinale >= FIELD->CHECK_IN )
return nil
> )

as you can see on Picture the filter not run ok

If it is good for the type (01) and the number (5) , it is not good for the dates ie if I select dDataIniziale:= ctod("04/07/2021") and dDataFinale:= ctod("08/07/2021")

the records are not in the selected range

but for the date not run ok how I can resolve ?

this is the test

Code (fw): Select all Collapse

 
#include "FiveWin.ch"

request dbfcdx
request dbffpt



Function test()
local oReservations
local cTipoElemento:="01"
local nElemento:= 5
local dDataIniziale:= ctod("04/07/2021")
local dDataFinale:= ctod("08/07/2021")

 SET DATE ITALIAN

 oReservations:= TDatabase():Open( nil, "RESERVA", "DBFCDX", .t. )

   oReservations:SetOrder( "room_in" )   //ROOMS_ID + DToS( CHECK_IN )


        oReservations:Exec( < ||
              SET FILTER TO AllTrim( FIELD->TYPE ) = AllTrim( cTipoElemento ) .and. FIELD->NUM = nElemento ;
                 .AND. ( dDataIniziale <= FIELD->CHECK_OUT .AND. dDataFinale >= FIELD->CHECK_IN )
             return nil
             > )

    oReservations:gotop()


xbrowser oReservations

return nil

then I tried also with tdata but the function oReservations:Exec( < || make me an error

Code (fw): Select all Collapse

 Error description: Error BASE/1449  Errore sintattico: &
   Args:
     [   1] = C   {||}

Stack Calls
===========
   Called from: Lib\tdata\TData.prg => TRESERVA:SETFILTER( 523 )
   Called from: Source\sistema\PBeach.prg => CHANGE_PLAN( 367 )
   Called from: Source\sistema\PBeach.prg => (b)BEACH( 133 )
   Called from: .\source\classes\DIALOG.PRG => TFSDI:INITIATE( 864 )
   Called from: .\source\classes\DIALOG.PRG => TFSDI:ACTIVATE( 330 )
   Called from: Source\sistema\PBeach.prg => BEACH( 133 )

Please SomeOne can help me ?

If I make a test with EmagDbu

Code (fw): Select all Collapse

ROOMS_ID="0005"  .AND.  CTOD("04/07/2021") <= CHECK_OUT .AND. CTOD("08/07/2021") >= CHECK_IN  .AND.  TYPE ="01"

it run ok

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Set Filter in Tdatabase/tdata
Posted: Mon Apr 19, 2021 11:12 AM

Please send your DBF to my email address

Regards

G. N. Rao.

Hyderabad, India

karinha

Posts: 8515

Joined: Tue Dec 20, 2005 07:36 PM

Re: Set Filter in Tdatabase/tdata
Posted: Mon Apr 19, 2021 11:39 AM

Please DEMO.ZIP in:

https://mega.nz

Regards, saludos.

João Santos - São Paulo - Brasil - Phone: +55(11)95150-7341

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Set Filter in Tdatabase/tdata
Posted: Mon Apr 19, 2021 12:32 PM

Mr. Silvio

Please see these lines of code:

Code (fw): Select all Collapse

local dDataIniziale:= ctod("04/07/2021")
local dDataFinale:= ctod("08/07/2021")

 SET DATE ITALIAN

By default, the date format is American ( MM/DD/YY ) not ( DD/MM/YY ).
You wanted to assign 4th July 2021 and 8th July 2021 to dDataIniziale and dDataFinale, but in fact you assigned 7th April 2021 and 7th August 2021, because when you assigned ctod("04/07/2021") and ctod("08/07/2021") the date format was American, not Italian.

You could have either written your program like this:

Code (fw): Select all Collapse

local dDataIniziale, dDataFinale

SET DATE ITALIAN

dDataIniziale:= ctod("04/07/2021")
dDataFinale:= ctod("08/07/2021")

OR use STOD() which is safe whatever is the date format

Code (fw): Select all Collapse

local dDataIniziale:= STOD( "20210704" )
local dDataFinale  := STOD( "20210708" )

OR use Date literals

Code (fw): Select all Collapse

local dDataIniziale:= {^ 04/07/2021 }
local dDataFinale:= {^ 08/07/2021 }

If you do it the right way, you get the right results.

At least now discontinue the unsafe practice of using ctod(). Change to STOD() which is safer or use {^ dd/mm/yyyy }. You will save a lot of your time.

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Re: Set Filter in Tdatabase/tdata
Posted: Mon Apr 19, 2021 02:25 PM

Nages,
i insert the dates on that test.org but I have the application where the user select interval of date from datepick control.
and allready on the init of application there is "set date italian"that is only a test

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Set Filter in Tdatabase/tdata
Posted: Mon Apr 19, 2021 02:31 PM

Ok. Make another test and show, please.

Regards

G. N. Rao.

Hyderabad, India

Silvio.Falconi

Posts: 7317

Joined: Thu Oct 18, 2012 07:17 PM

Re: Set Filter in Tdatabase/tdata
Posted: Mon Apr 19, 2021 04:32 PM

nageswaragunupudi wrote:Ok. Make another test and show, please.

You won't believe it, I made another example you see below and it works great ( I sent the test to U)

But in the application I do a loop because I must change the bitmap of Umbrella if that umbrella is occupated on Interval dates

For nNumeroElementi= 1 to Len(aElementi)

I'm going to do a filter on oReservations as I wrote above

then make a loop type

Code (fw): Select all Collapse

   DO WHILE ! oReservations:EoF()
                                                              nStatus     := Val( oReservations:status )

save some informations

Code (fw): Select all Collapse

 oReservations:Skip()
                                   ENDDO
                                       oReservations:SetFilter( "" )
                                        oReservations:end()

and not run ok

Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)

I use : FiveWin for Harbour March-April 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com

FiveTech Support Forums

Continue the discussion