search record within time interval gives trouble

[stefanocps]

Hello in my db
i have a form called RICERCHE (means search) wich i use to filter data according certain parameter
There is one parameter,the search within dates interval that "SPOIL" treturned record. They are shown more than once. As soon as i insert the table needed for the search (date_trattamenti) the record come out repeated



The form Ricerche is base on the table Anagrafica and on the table date_trattamenti, they are joined through id


I attach the db, start the form RICERCHE
thanks for helping

 

[CJ_London]

insert the table needed for the search (date_trattamenti) the record come out repeated

your date_trattamenti table has duplicate ID's

 

[stefanocps]

your date_trattamenti table has duplicate ID's

i know i need it like this
this table have has record 3 parameters
Dateform date to and treatment


The id of this table is linked to the id Of Anagrafica, whic is a person
If a person have the ANAGRAFICA ID= 4


then the date_trattamenti have this number 4 for each time he got treatment
for example
in ANAGRAFICA
id 8 is Stefano


Stefano have trattamenti form 1/1/2017 to 1/6/2017
so in DATE TRATTAMENTI table i ll have
id 8 1/1/2017 to 1/6/2017


then id 8 come again form 12/9/2017 to 1/1/2018
so the table will have
id 8 1/1/2017 to 1/6/2017
id 8 12/9/2017 to 1/1/2018


and so on


that is how i build the entire db, as the dates are the main criteria for search

 

[stefanocps]

in all the other queries, that are the main pourpouses of the db, this methd work, and i can get the final report as desidered


Now i though i could use the date filtering in RICERCHE also and the poroblem has come up

 

[CJ_London]

I'm not seeing those dates against id=8 in date_trattamenti

And your query criteria looks wrong. the first line has lots of like criteria and a between criteria for dal. The second line only has a between criteria against al.

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

alternatively you can simplify your criteria slightly

dal<=[Maschere]![Ricerche]![dataa]
al>=[Maschere]![Ricerche]![datada]

on the same criteria row as the Likes

Edit: forgot to mention date formats. When sql encounters a text representation of a date it will assume the US format of mm/dd/yyyy. This is not a problem when day is >12 because sql is smart enough to know that the are not 13 or more months in the year. But id the date is say 1/6/2018 it will read this as 6th January, not 1st June. The data you enter in the form is text so this will have an impact. Also, to tell sql that the bit of text your are using is to be treated as a date, you need to surround with the # character. #01/06/2018#, otherwise you are comparing a date datatype with a text datatype. You can uses the parameters part of the function to define the datatype.

 

[stefanocps]

ok the ID8 and subsequent datas where just example, are not in the db
Perhaps i don t undesrtand clearly what are you suggesting, the problem is not in the seacrh criteria but in the fact that if a patient come more than once in the period a choose he will be showed up more than one time, while i need him to be showed once

 

[CJ_London]

with respect, being casual about your examples and descriptions does not help me to help you, it just leads to confusion. You know your business, I don't.

I also added to my post, so check that out as well

 

[stefanocps]

in ricerche set start date at 1/1/2017 and final date today
you ll see that the 222 come up twice
but it is a duplicate
if you open th + you ll see the same datas for the fiorst 222 and the second 222


beware that 222 is not the id used as primari key. that 222 is just for anagrapgic use, hust like any other field


the 222 has id 8 an doyu ll find it if you open the +

 

[stefanocps]

with respect, being casual about your examples and descriptions does not help me to help you, it just leads to confusion. You know your business, I don't.

I also added to my post, so check that out as well

anyway the 8 it does exists, i did not check properly


as i said


the id used as primary key is not shown, the numeber you see is for operator use,and wqill be alphanumeric
the 222 has got a primary key id 8 and this 8 become the id for the table date_trattamenti

 

[CJ_London]

the problem is not in the seacrh criteria but in the fact that if a patient come more than once in the period a choose he will be showed up more than one time, while i need him to be showed once

Change the SELECT to SELECT DISTINCT - in query properties change Unique Values from No to Yes

 

[stefanocps]

arhg..i have just find out that after inserting the criteria search for datem the other criteria in the RICERCHE (id, cognome, sesso, titolo di studio,occupazione, sostanza primaria, sostanza secondaria) don t work anymore



i attach the db as it was until today
you open the ricerche ant it works correctly, but the date have not effect on it


To make use of them i have tried to insert the table "date_trattamenti" in the submask
but then everyhinbg spoil

 

[stefanocps]

Change the SELECT to SELECT DISTINCT - in query properties change Unique Values from No to Yes

tied it..no change

 

[CJ_London]

your query criteria is a mess.

For example

Like [Maschere]![Ricerche]![id]+"*"

should be

Like [Forms]![Ricerche]![id]&"*"

and naming - the above references the id control on the form - but is applied to your id Anagrafica field. Maybe that is correct, but why not use equivalent names?

 

[stefanocps]

your query criteria is a mess.

For example

Like [Maschere]![Ricerche]![id]+"*"

should be

Like [Forms]![Ricerche]![id]&"*"

and naming - the above references the id control on the form - but is applied to your id Anagrafica field. Maybe that is correct, but why not use equivalent names?

thanks i have changed the operator
but donn t seems related to the showing duplicate record issue

 

[stefanocps]

i correct
i have set again the select distinct which i deleted to restore and now no more duplicates
so most porbably depended on the + instead of and? looks like
i don t get properly this
and naming - the above references the id control on the form


anyway really thanks, ,now the query for dates works!

 

[stefanocps]

no there is still a problem
the date query works but not the other filters, don t work anymore any of them

 

[CJ_London]

I am unable to run your queries because the criteria are all

Like [Maschere]!...

I presume [Maschere] is your word for [Form]

However I see you don't have the &"*" at the end, perhaps that is why it is not working as expected.

Suggest turn your recordsource into a separate query and remove all the criteria, open your form, enter some data and then the query. Then add the criteria back one at a time until the criteria does not do as expected.

 

[stefanocps]

i think i don t undesratnd well what you are suggesting me
For now i have changed all the MASCHERE with FORMS in the FORM RICERCHE, so you shiuld be able to run it
you don t need to run any queries as they serve for the report, they all are for counting the records, they are ok

I deleted all the filter criteria, but the date and the cognome
Dates filtering works
cognome don t


(to refresh the forms you can click on body or move the mouse)
thanks for help again

 

[stefanocps]

i am doing some test
if i set a filter to only one of the 2 date field everything is fine
When i put the filter "first date OR filter second date", then problem start
and other filters stop workingwhile date filtering is ok

 

[stefanocps]

i am thinking
might it be a matter of priority between operators AND OR?


i have a filter between date managed by OR (1st date [datada] OR 2nd date[dataa]), and i have an AND that mix the date filter with another filter, in this case the ID

WHERE (((anagrafica.[id Anagrafica]) Like [forms]![Ricerche]![id] & "*") AND ((((date_trattamenti.al) Between [forms]![Ricerche]![datada] And [forms]![Ricerche]![dataa]))) OR ((((date_trattamenti.dal) Between [forms]![Ricerche]![datada] And [forms]![Ricerche]![dataa]))));

could be that these operators in some way dont cope together in this way?