Querie not working on the OR part

[etsoft]

Hello,

i have the following querie :

SELECT TOP 16 Activities.ID, Activities.WPID, Activities.ActivityID, Activities.ActivityDesc, Activities.progress, Activities.kritisch, Activities.Vergunning, Activities.Vergunningdatum, Activities.geplande_datum, Activities.TypeVergunning, Activities.Groep, Activities.Plankaart, Activities.VergunningVerwerkt, Activities.Werkpakket, Activities.selectie, Activities.startdatum, Activities.einddatum, Activities.gewijzigd, Activities.datum, Activities.Repsonibility, Activities.LA, Activities.Area FROM Activities
WHERE (((Activities.WPID)=43300) AND ((Activities.progress)<100) AND ((Activities.Plankaart)="plk02")) OR (((Activities.WPID)=43300) AND ((Activities.progress)=100) AND ((Activities.Plankaart)="plk02") AND ((Activities.gewijzigd)=True));




In the WHERE part the are two parts, the data of the second part is not showing.Both must apply. What am i missing here?
It is a single table, no joins.


The difference is in the 'progress' field and additional the 'gewijzigd' field.

Thanks in advance
Erwin

[davegri]

Maybe this simplification?

WHERE ((Activities.WPID=43300 AND Activities.progress<100 AND Activities.Plankaart="plk02") OR (Activities.WPID=43300 AND Activities.progress=100 AND Activities.Plankaart="plk02" AND Activities.gewijzigd=True));

You could also try it with just the second part to make sure that part works, then combine with the OR

[CJ_London]

you can also rearrange the criteria - although the query grid will probably arrange it back again when you save it

WHERE Activities.WPID=43300 AND Activities.Plankaart="plk02" AND (Activities.progress<100 OR (Activities.progress=100 AND Activities.gewijzigd=True))

[isladogs]

Is this a saved query or a sql statement in code?
If the latter change the double quotes "plk02" to single quotes 'plk02’

[etsoft]

No both suggestions does not show the 100 and true situation, only the first part. It will be used in code so saving the querie will not be nescesary.
The double quotes are working correctly, in code the will be changed of course.
Other sugesstions?

[CJ_London]

I presume your data does contain a progress value of 100 (and not a double with 99.999999) and gewijzigd of true


The criteria seems straightforward enough to me suggest provide some example data and the result you require in case my understanding is incorrect. or upload some sample data in a table

[etsoft]

Hereby and csv file with all relevant data, the red lines should me excluded in the querie

[CJ_London]

your gewijzigd is text, not a boolean in the example you sent

ONWAAR I believe is false so neither of those two records would be returned

what does get returned is ID 184280 which meets the 'OR' part of your criteria

So the criteria is doing what it is supposed to be doing. If that is not what you want, clarify what you do want with examples

This is the sql I used

SELECT TestSql.*
FROM TestSql
WHERE (((TestSql.[progress])<100) AND ((TestSql.[WPID])=43300) AND ((TestSql.[Plankaart])="plk02")) OR (((TestSql.[progress])=100) AND ((TestSql.[WPID])=43300) AND ((TestSql.[Plankaart])="plk02") AND ((TestSql.[gewijzigd])='WAAR'));

and this is the result

ID	WPID	Plankaart	gewijzigd	progress
184210	43300	Plk02	ONWAAR	0
184221	43300	Plk02	ONWAAR	0
184247	43300	Plk02	ONWAAR	0
184253	43300	Plk02	ONWAAR	0
184255	43300	Plk02	ONWAAR	0
184280	43300	Plk02	WAAR	100
184291	43300	Plk02	ONWAAR	0
184292	43300	Plk02	ONWAAR	0

[etsoft]

Yes thank you, this is the correct one.
In the conversion i made the 'gewijzigd' field was converted to text but the original field was Boolean.

Again thanks.

Erwin

[rpeare]

(((TestSql.WPID)=43300) AND ((TestSql.Plankaart)="plk02") AND ((TestSql.progress)<100)) OR (((TestSql.WPID)=43300) AND ((TestSql.Plankaart)="plk02") AND ((TestSql.progress)=100) AND ((TestSql.gewijzigd)=True));

substitute in your table name.

This is assuming

WAAR means true
ONWAAR means false

I got the same result from both queries (my test and and your query criteria), both are correctly excluding the two records you highlighted in red.

[CJ_London]

I see absolutely no difference between the criteria in the original post or mine or rpeare's query (other than the boolean/text swap)

So what is all the fuss about?

[etsoft]

Somehow, the original code did not work and the post from you did, perhaps i did not copy it right.
Did you all try it in Access or some other software?

[CJ_London]

all done in access

Somehow, the original code did not work and the post from you did, perhaps i did not copy it right.

other than changing the table name and the boolean/text swap, the criteria per your original post also worked