search record within time interval gives trouble

[CJ_London]

see post #5 where I raised this issue

 

[stefanocps]

i think you rfer to this
dal<=[Maschere]![Ricerche]![dataa]
al>=[Maschere]![Ricerche]![datada]
i am not sure it has the same effect though, i must make sure that when a patient has been tretated from-to (dal-al) it will be taken out if my 2 dates coiver that time

i am noit able to understand if it collect data properly anyway
there is no other way to write the sql instructions using the controls i use it now?

 

[stefanocps]

i had a go to try if it works..


WHERE (((date_trattamenti.dal)>=[Maschere]![Ricerche]![datada])) OR (((date_trattamenti.al)<=[Maschere]![Ricerche]![dataa]) AND ((anagrafica.[id Anagrafica]) Like [Maschere]![Ricerche]![id] & "*"));


don t work anyway, it only select the dates, nothing about id


say it again. it works if i only consider one of the 2 date date_trattamenti.dal or date_trattamenti.al


this could be an interisting point for find the solution and seems rerlated to AND and OR being used together

 

[CJ_London]

I am now totally confused about what you require. What you tried is not what I suggested.

i must make sure that when a patient has been tretated from-to (dal-al) it will be taken out if my 2 dates coiver that time

this is saying to me that if you have dal 1st Jan 2018 and al is 31st Jan 2018 you do not want any records returned where treatment started or ended between those dates - but you do want to see them if they started before 1st Jan and ended either before 1st Jan or after 31st Jan or they started (and ended_ after 31st Jan. Is this correct?

there is no other way to write the sql instructions using the controls i use it now?

yes - use the form filter property, or rewrite the form recordsource each time.

 

[stefanocps]

I am now totally confused about what you require. What you tried is not what I suggested.

this is saying to me that if you have dal 1st Jan 2018 and al is 31st Jan 2018 you do not want any records returned where treatment started or ended between those dates - but you do want to see them if they started before 1st Jan and ended either before 1st Jan or after 31st Jan or they started (and ended_ after 31st Jan. Is this correct?


yes - use the form filter property, or rewrite the form recordsource each time.

no probably i write bad the sql, it should be as you thought at the beginning, as it reasonable, want to know who is been treated within the 2 date i specify. this is easy
but this is not the point, what is not working is puitting together the date filter and any other filter




i don t undesrtand this
use the form filter property, or rewrite the form recordsource each time
what shall i do?


really to me it seems a very easy query, why don t work?

 

[CJ_London]

but this is not the point, what is not working is puitting together the date filter and any other filter

per post #5

this is saying 'get all records where this is like that AND this is also like that..... AND dal between day 1 and day2. OR just al between day 1 and day 2 (not likes)

So think you need the like criteria on both rows

 

[stefanocps]

don t know how to do that...sorry my knoledge is not so good

 

[stefanocps]

i have tried this
WHERE (((date_trattamenti.dal)<=[Maschere]![Ricerche]![dataa]) AND ((date_trattamenti.al)>=[Maschere]![Ricerche]![datada]) AND ((anagrafica.[id Anagrafica]) Like [Maschere]![Ricerche]![id] & "*"));
i put translation for better understanding


date_trattamenti.dal= treatment date from
date_trattamenti.al= treatment date to
dataa= date to
datada=datefrom

and both filter seems to work
but i am not sure if the date query in this way will get properly the record
can t figure out

 

[CJ_London]

looks like you've got it

date_trattamenti.dal= treatment date from
date_trattamenti.al= treatment date to
dataa= date to
datada=datefrom

in the dal column you put
<=[Maschere]![Ricerche]![dataa]

in the al column you put
>=[Maschere]![Ricerche]![datada]

all on the same row as your likes

but i am not sure if the date query in this way will get properly the record

draw it out on a piece of paper

difficult to do here but I'll give it a try. Code DF=datada, DT=data, S=dal, E=al. There are only 6 possibilities

…………………………………………..DF...………………………...DT...…………………
treatment 1...…..S...……..E
treatment 2...…..S...………………………..E
treatment 3...…..S...……………………………………………………………..E
treatment 4...………………………………………..S...…E
treatment 5...………………………………………..S...………………………..E
treatment 6...……………………………………………………………………...S...…...E

criteria
treatment 1 - S<=DT true, E>DF false - result exclude
treatment 2 - S<=DT true, E>DF true - result include
etc

 

[stefanocps]

seems ok, i will do some more test
For now i really thank you, your help has been really good, apreciate that!