shows right answer but multiple times

[chaffy2]

I have some code i run on a query it produces many multiples of the same answer.

The situation is that i have a listbox with the query 'zearchall3' and i have form controls that affect the data shown in the listbox

i then click on the button (the code for is supplied at the end of this) which then filters that query depending on what is writen in the [name] query.

but, but, but

I have to put the query 'zearchall' instead of 'zearchall3'.
For some reason when I try to put 'zearchall3' the output is repeated many times. whereas when i put 'zearchall' the answer is in correct quantities.

incidently , the query 'zearchall' is the query that 'zearchall3' is based on, it just has references to form controls to edit it.

does anyone understand why i can't get sensible quantity from 'zearchall 3'


CODE FOR BUTTON

Dim sql As String
Dim WHERE As String

sql = "SELECT [ContactDetailID],[FirstName],[Surname],[DOB],[Nationalitys], [Gender],[IsStudent] , [IsHousecouncil], [IsSuperior], [IsVicarsofFriendlyParishes], [IsBenefactor], [IsKeepinTouch], [IsStaff], [IsIntConf],[IsHousecouncil], [IsDeceased],[HaveChildren], [SpouseTitle], [HomeNumber], [HomeStreet], [HomeCity], [HomePostcode/ZipCode], [Country], [ChurchAddressingTerm], [SpouseSurname], [SpouseAddressingTerm], [SpouseFullEnvelopeTitle], [SuperiorTitle], [ContactDetailID], [DateOfBirth], [Nationality], [ChurchAddressingTerm], [Country], [HomePostcode/ZipCode], [HomeStreet], [HomeName] FROM zearchall WHERE FirstName = Forms!searchall!FirstName"


List0.RowSource = sql
List0.Requery



END END END





SQL FOR ZEARCHALL3



SELECT zearchall.ContactDetailID AS Contactid, Trim([FirstName] & "") AS [First Name], Trim([Surname] & "") AS Surnames, Trim([Gender] & "") AS Genders, Trim([DateOfBirth] & "") AS DOB, Trim([Nationality] & "") AS Nationalitys, zearchall.HaveChildren AS [Have Children], zearchall.IsStudent AS Students, zearchall.IsHousecouncil AS HouseCouncils, zearchall.IsSuperior AS Superiors, zearchall.IsVicarsofFriendlyParishes AS Vicars, zearchall.IsBenefactor AS Benefactors, zearchall.IsKeepinTouch AS KeepinTouch, zearchall.IsStaff AS Staffs, zearchall.IsIntConf, zearchall.IsDeceased, zearchall.HomeName, zearchall.HomeNumber, zearchall.HomeStreet, zearchall.HomeCity, zearchall.[HomeCounty/state], zearchall.[HomePostcode/ZipCode], zearchall.Country, zearchall.ChurchAddressingTerm, zearchall.HaveChildren, zearchall.SpouseFirstName, zearchall.SpouseTitle, zearchall.SpouseSurname, zearchall.IsHousecouncil, zearchall.SpouseAddressingTerm
FROM zearchall
WHERE (((Trim([FirstName] & "")) Like [forms]![searchall]![FirstName] & "*") AND ((Trim([Surname] & "")) Like [forms]![searchall]![Surname] & "*") AND ((Trim([Gender] & "")) Like [forms]![searchall]![Gender] & "*") AND ((Trim([DateOfBirth] & "")) Like [forms]![searchall]![DateOfBirth] & "*") AND ((Trim([Nationality] & "")) Like [forms]![searchall]![Nationality] & "*") AND ((zearchall.HaveChildren)=[forms]![searchall]![HaveChildren]) AND ((zearchall.IsDeceased)="no")) OR (((zearchall.IsStudent)=[forms]![searchall]![Student]) AND ((zearchall.IsDeceased)="no")) OR (((zearchall.IsHousecouncil)=[forms]![searchall]![HouseCouncil]) AND ((zearchall.IsDeceased)="no")) OR (((zearchall.IsSuperior)=[forms]![searchall]![Superior]) AND ((zearchall.IsDeceased)="no")) OR (((zearchall.IsVicarsofFriendlyParishes)=[forms]![searchall]![Vicars]) AND ((zearchall.IsDeceased)="no")) OR (((zearchall.IsBenefactor)=[forms]![searchall]![isbenefactor]) AND ((zearchall.IsDeceased)="no")) OR (((zearchall.IsKeepinTouch)=[forms]![searchall]![KeepinTouch]) AND ((zearchall.IsDeceased)="no")) OR (((zearchall.IsStaff)=[forms]![searchall]![Staff]) AND ((zearchall.IsDeceased)="no")) OR (((zearchall.IsIntConf)=[forms]![searchall]![isintconf]) AND ((zearchall.IsDeceased)="no"));

 

[PHV]

Double check the parenthesis balancing as you're mixing AND & OR operators.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chaffy2]

the sql on the button has no AND or OR operators. So why is it getting repeated data? The zearchall3 query works fine and that is the one with the AND and OR operators.

 

[PHV]

Which of your assertions is true ?
1) i can't get sensible quantity from 'zearchall 3'
2) The zearchall3 query works fine

 

[chaffy2]

1 IS TRUE , 2) is only true in regards to filling the listbox. when the button is clicked the listbox shows repeated data and therefore doesn't work well.