Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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;
0
Brogrim
Asked:
Brogrim
  • 7
  • 6
1 Solution
 
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
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now