OR problem

[scubadiver007]

Hello,

I have a form with three text boxes and a subform that lists records.

searchforename
searchsurname
SearchDOB

I have a query on which I would like to perform two different searches.

1) Partial surname only (this is for updating records)

First criteria line:

Cand_surname: Like [forms]![Form_candidates_search]![searchsurname] & "*"

2) Complete forename, complete surname and date of birth (this to check whether an individual has a record on the database prior to registration)

Second criteria line

Cand_forename: [forms]![Form_candidates_search]![searchforename]
Cand_surname: [forms]![Form_candidates_search]![searchsurname]
Cand_DOB: [forms]![Form_candidates_search]![searchDOB]

SELECT table_candidate.Cand_ID, table_candidate.Cand_forename, table_candidate.Cand_surname, table_candidate.Cand_addr1, table_candidate.Cand_addrpc, table_candidate.Cand_DOB
FROM table_candidate
WHERE (((table_candidate.Cand_forename) Is Not Null) AND ((table_candidate.Cand_surname) Like [forms]![Form_candidates_search]![searchsurname] & "*") AND ((table_candidate.Cand_DOB) Is Not Null)) OR (((table_candidate.Cand_forename)=[forms]![Form_candidates_search]![searchforename]) AND ((table_candidate.Cand_surname)=[forms]![Form_candidates_search]![searchsurname]) AND ((table_candidate.Cand_DOB)=[forms]![Form_candidates_search]![searchDOB]))
ORDER BY table_candidate.Cand_surname;

The first search works okay but if I put in my own complete name but a fake birth date, my record still appears in the form's subform.

 

[jdraw]

Does it work with your second set of criteria only? Have you tried it?

SELECT Cand_ID, Cand_forename
, Cand_surname, Cand_addr1
, Cand_addrpc, Cand_DOB
FROM table_candidate
WHERE  
(((Cand_forename)=[forms]![Form_candidates_search]![searchforename]) AND 
((Cand_surname)=[forms]![Form_candidates_search]![searchsurname]) AND 
((Cand_DOB)=[forms]![Form_candidates_search]![searchDOB]))
ORDER BY Cand_surname;

 

[scubadiver007]

I have tried it and it does work.

I then tried adding

[forms]![Form_candidates_search]![searchforename] is null
[forms]![Form_candidates_search]![searchDOB] is null

for the first search to get this query

SELECT table_candidate.Cand_ID, table_candidate.Cand_forename, table_candidate.Cand_surname, table_candidate.Cand_addr1, table_candidate.Cand_addrpc, table_candidate.Cand_DOB
FROM table_candidate
WHERE (((table_candidate.Cand_forename)=[forms]![Form_candidates_search]![searchforename]) AND ((table_candidate.Cand_surname)=[forms]![Form_candidates_search]![searchsurname]) AND ((table_candidate.Cand_DOB)=[forms]![Form_candidates_search]![searchDOB])) OR (((table_candidate.Cand_surname) Like [forms]![Form_candidates_search]![searchsurname] & "*") AND (([forms]![Form_candidates_search]![searchforename]) Is Null) AND (([forms]![Form_candidates_search]![searchDOB]) Is Null))
ORDER BY table_candidate.Cand_surname;

The second query works but the first one does not.

 

[scubadiver007]

I can only think having two different queries and applying a different data source to the subform would be the solution but then that is probably copping out.

 

[jdraw]

[forms]![Form_candidates_search]![searchforename] is null
[forms]![Form_candidates_search]![searchDOB] is null

When I read these it's saying If I don't have criteria for
forename or DOB --- What do you want to return if you done fill these search fields?

 

[scubadiver007]

When I read these it's saying If I don't have criteria for
forename or DOB --- What do you want to return if you done fill these search fields?

Search 1 example: Type "Art" into the surname box and all surnames starting with those three letters (or any other number of letters) is listed in the subform. The criteria you highlighted is for this search because the forename and DOB will be blank.

Search 2 example: Type a full forename, full surname and date of birth to see if a single record exists.

 

[scubadiver007]

I've decided to take out the date of birth and simplify the query.