Search Criteria

I have an sql statemet that is searching partial data

The problem is that it will only return recorda if the DOB has a value.

I want to return rows regardless if the DOB column has a value. I thought by using the Like "*" & [Forms]![frmSwitchboard]![txtDOB] & "*", it would return teh value even if null


SELECT tblMember.FirstName, tblMember.SecondName, [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] AS Address, tblMember.DOB, tblMember.CountyID, tblMember.MemberID, tblMember.GenderID, tblMember.TitleID, tblMember.Member, tblMember.BadgeHolder, lktblCounty.County
FROM tblMember INNER JOIN lktblCounty ON tblMember.CountyID = lktblCounty.CountyID
WHERE (((tblMember.FirstName) Like "*" & [Forms]![frmSwitchboard]![txtFirstName] & "*") AND ((tblMember.SecondName) Like "*" & [Forms]![frmSwitchboard]![txtSecondName] & "*") AND (([HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode]) Like "*" & [Forms]![frmSwitchboard]![txtAddress] & "*") AND ((tblMember.DOB) Like "*" & [Forms]![frmSwitchboard]![txtDOB] & "*"))
ORDER BY tblMember.FirstName, tblMember.SecondName;
BrogrimInformation Systems Development ManagerAsked:
Who is Participating?
 
mbizupCommented:
<<
Records that don't have a DOB value entered are not returning
>>

Do you mean records where DOB is null/Blank in the table, or where the DOB textbox is Null/Blank on your form?  I was focusing on cases where the textbox on the form was left blank.

Try this:

SELECT tblMember.FirstName, tblMember.SecondName, [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] AS Address, tblMember.DOB, tblMember.CountyID, tblMember.MemberID, tblMember.GenderID, tblMember.TitleID, tblMember.Member, tblMember.BadgeHolder, lktblCounty.County
FROM tblMember INNER JOIN lktblCounty ON tblMember.CountyID = lktblCounty.CountyID
WHERE (((tblMember.FirstName) Like "*" & [Forms]![frmSwitchboard]![txtFirstName] & "*") AND ((tblMember.SecondName) Like "*" & [Forms]![frmSwitchboard]![txtSecondName] & "*") AND (([HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode]) Like "*" & [Forms]![frmSwitchboard]![txtAddress] & "*") AND (((tblMember.DOB) BETWEEN NZ([Forms]![frmSwitchboard]![txtDOB], #1/1/1900#)  AND NZ([Forms]![frmSwitchboard]![txtDOB], #1/1/2099#)) OR tblMember.DOB IS NULL ))
ORDER BY tblMember.FirstName, tblMember.SecondName; 

Open in new window

0
 
mbizupCommented:
Give this a try:


SELECT tblMember.FirstName, tblMember.SecondName, [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] AS Address, tblMember.DOB, tblMember.CountyID, tblMember.MemberID, tblMember.GenderID, tblMember.TitleID, tblMember.Member, tblMember.BadgeHolder, lktblCounty.County
FROM tblMember INNER JOIN lktblCounty ON tblMember.CountyID = lktblCounty.CountyID
WHERE tblMember.FirstName Like "*" & [Forms]![frmSwitchboard]![txtFirstName] & "*" AND tblMember.SecondName Like "*" & [Forms]![frmSwitchboard]![txtSecondName] & "*" AND [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] Like "*" & [Forms]![frmSwitchboard]![txtAddress] & "*" AND (tblMember.DOB BETWEEN  NZ([Forms]![frmSwitchboard]![txtDOB], #1/1/1900#)  AND NZ(Forms]![frmSwitchboard]![txtDOB],#12/31/2100# ))

Open in new window

0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
I am getting a synthax error with following copied statement

SELECT tblMember.FirstName, tblMember.SecondName, [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] AS Address, tblMember.DOB, tblMember.CountyID, tblMember.MemberID, tblMember.GenderID, tblMember.TitleID, tblMember.Member, tblMember.BadgeHolder, lktblCounty.County
FROM tblMember INNER JOIN lktblCounty ON tblMember.CountyID = lktblCounty.CountyID
WHERE tblMember.FirstName Like "*" & [Forms]![frmSwitchboard]![txtFirstName] & "*" AND tblMember.SecondName Like "*" & [Forms]![frmSwitchboard]![txtSecondName] & "*" AND [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] Like "*" & [Forms]![frmSwitchboard]![txtAddress] & "*" AND (tblMember.DOB BETWEEN  NZ([Forms]![frmSwitchboard]![txtDOB], #1/1/1900#)  AND NZ(Forms]![frmSwitchboard]![txtDOB],#12/31/2100# ))
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
SELECT tblMember.FirstName, tblMember.SecondName, [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] AS Address, tblMember.DOB, tblMember.CountyID, tblMember.MemberID, tblMember.GenderID, tblMember.TitleID, tblMember.Member, tblMember.BadgeHolder, lktblCounty.County
FROM tblMember INNER JOIN lktblCounty ON tblMember.CountyID = lktblCounty.CountyID
WHERE tblMember.FirstName Like "*" & [Forms]![frmSwitchboard]![txtFirstName] & "*" AND tblMember.SecondName Like "*" & [Forms]![frmSwitchboard]![txtSecondName] & "*" AND [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] Like "*" & [Forms]![frmSwitchboard]![txtAddress] & "*" AND tblMember.DOB BETWEEN  NZ([Forms]![frmSwitchboard]![txtDOB], #1/1/1900#)  AND NZ(Forms]![frmSwitchboard]![txtDOB],#12/31/2100# )

Open in new window

0
 
aikimarkCommented:
Do any of the conditions need to match or all of them?  The way it is written, all of them have to match.  You would need to change the AND to OR for any matching
WHERE (tblMember.FirstName Like "*" & [Forms]![frmSwitchboard]![txtFirstName] & "*" )
OR (tblMember.SecondName Like "*" & [Forms]![frmSwitchboard]![txtSecondName] & "*" )
OR ([HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] Like "*" & [Forms]![frmSwitchboard]![txtAddress] & "*" )
OR (tblMember.DOB 
BETWEEN  NZ([Forms]![frmSwitchboard]![txtDOB], #1/1/1900#)  
     AND NZ(Forms]![frmSwitchboard]![txtDOB],#12/31/2100# ) )

Open in new window

0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
When I try to execute the above code I am recieving "Syntax error in query expression".

Sorry about the delay in replying
0
 
mbizupCommented:
Brogrim,

You have two people helping you, posting different solutions... did you try everything posted?

Which comment(s) did you try?  It helps if you refer to the posters by name or the comments by comment ID.
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
I have tried both of them and they are all returning "Syntax error in query expression".

I will take your advice on naming posters in my replies. Thank you
0
 
mbizupCommented:
Are you running these directly from the query window or from Visual Basic?
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
The query is the record sourse for a for a form and is directly from a query
0
 
mbizupCommented:
Try this:

SELECT tblMember.FirstName, tblMember.SecondName, [HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode] AS Address, tblMember.DOB, tblMember.CountyID, tblMember.MemberID, tblMember.GenderID, tblMember.TitleID, tblMember.Member, tblMember.BadgeHolder, lktblCounty.County
FROM tblMember INNER JOIN lktblCounty ON tblMember.CountyID = lktblCounty.CountyID
WHERE (((tblMember.FirstName) Like "*" & [Forms]![frmSwitchboard]![txtFirstName] & "*") AND ((tblMember.SecondName) Like "*" & [Forms]![frmSwitchboard]![txtSecondName] & "*") AND (([HouseName] & " " & [HouseNo] & " " & [HouseLetter] & " " & [Address1] & " " & [Address2] & " " & [Address3] & " " & [County] & " " & [PostCode]) Like "*" & [Forms]![frmSwitchboard]![txtAddress] & "*") AND ((tblMember.DOB) BETWEEN NZ([Forms]![frmSwitchboard]![txtDOB], #1/1/1900#)  AND NZ([Forms]![frmSwitchboard]![txtDOB], #1/1/2099#) ))
ORDER BY tblMember.FirstName, tblMember.SecondName; 

Open in new window

0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
mbizup, the query is still only returning records that have a DOB value. Records that don't have a DOB value entered are not returning
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
mbizup, Thank you for the solution. Sincere apoligies for the delay in getting back to you. Your assistance is greatly appreciated from this relieved Irish man.
0
 
mbizupCommented:
Glad to help out :-)


Slainte!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.