Set Filter in Tdatabase/tdata

[Silvio.Falconi]

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: Select all | Expand

 
#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: Select all | Expand

 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: Select all | Expand

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

it run ok

[nageswaragunupudi]

Please send your DBF to my email address

[karinha]

Please DEMO.ZIP in:

https://mega.nz

Regards, saludos.

[nageswaragunupudi]

Mr. Silvio

Please see these lines of code:

Code: Select all | Expand

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: Select all | Expand

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: Select all | Expand

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

OR use Date literals

Code: Select all | Expand

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.

[Silvio.Falconi]

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

[nageswaragunupudi]

Ok. Make another test and show, please.

[Silvio.Falconi]

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: Select all | Expand

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

save some informations

Code: Select all | Expand

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

and not run ok