Need help so that search also shows blank records

[NeilT123]

Hello, I have an account search form that I use in my database.

I have a module with this code

Function RemovePunc(varText As Variant) As Variant
    Dim strRemoveThese As String
    Dim intI As Integer
    strRemoveThese = "'()/,"
    If Not IsNull(varText) Then
        For intI = 1 To Len(strRemoveThese)
            varText = Replace(varText, Mid(strRemoveThese, intI, 1), "")
        Next
    End If
    RemovePunc = varText
End Function

and then in the query behind the form I have

WHERE (((RemovePunc([tblFarmerDetails].[AccountName])) Like "*" & [Forms]![frmSearch]![Search2] & "*")

Today I have also tried to add search facility to ContactName as well as AccountName but the problem is that whilst all accounts have AccountName not all accounts have a ContactName.

I have added the "where" code to the search form for ContactName but when the search form 1st opens it only lists those records which have a ContactName and I need the form to list all of the accounts irrespective of whether they have a value in ContactName.

I am not sure whether I need to amend the WHERE criteria or whether I need a line in the module to deal with blank records so could I ask for help and suggestions please.

The module code is not mine, it was supplied by a colleague but on searching I see it has been used in this forum before so I am hoping someone may be able to help.

Thanks as always.

 

[PHV]

I need to amend the WHERE criteria
Which WHERE clause exactly ?

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

 

[dhookom]

Like *" will not match a NULL value since NULL is unknown.

WHERE RemovePunc([tblFarmerDetails].[AccountName]) & "" Like "*" & [Forms]![frmSearch]![Search2] & "*"

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Sorry for the lack of info. This is the sql of the query behind the search form

SELECT tblFarmerDetails.FarmAccountNumber, tblFarmerDetails.AccountName AS [Account Name], tblFarmerDetails.ContactName AS [Contact Name], tblFarmerDetails.Address1 AS Address, tblFarmerDetails.Address2 AS [Address 2], tblFarmerDetails.City, tblFarmerDetails.County, tblFarmerDetails.PostCode AS [Post Code], tblFarmerDetails.NutriFocusStatus AS [NutriFocus Status], RemovePunc([tblFarmerDetails].[AccountName]) AS Expr1, RemovePunc([tblFarmerDetails].[ContactName]) AS Expr2
FROM tblFarmerDetails
WHERE (((RemovePunc([tblFarmerDetails].[AccountName])) Like "*" & [Forms]![frmSearch]![Search2] & "*") AND ((RemovePunc([tblFarmerDetails].[ContactName])) Like "*" & [Forms]![frmSearch]![ContactSearch2] & "*"));

I wondered whether it was this WHERE section that was causing the problem or whether it is the module.

 

[NeilT123]

Is there something to use instead of "Like *

 

[PHV]

SELECT FarmAccountNumber, AccountName AS [Account Name], ContactName AS [Contact Name], Address1 AS Address, Address2 AS [Address 2], City, County, PostCode AS [Post Code], NutriFocusStatus AS [NutriFocus Status]
FROM tblFarmerDetails
WHERE RemovePunc(AccountName) Like "*" & [Forms]![frmSearch]![Search2] & "*"
AND (RemovePunc(ContactName) Like "*" & [Forms]![frmSearch]![ContactSearch2] & "*" OR ContactName IS NULL)

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

 

[NeilT123]

Thanks PHV for the code however because every account has an AccountName I changed my code to this which seems to have resolved the issue.

SELECT tblFarmerDetails.FarmAccountNumber, tblFarmerDetails.AccountName AS [Account Name], tblFarmerDetails.ContactName AS [Contact Name], tblFarmerDetails.Address1 AS Address, tblFarmerDetails.Address2 AS [Address 2], tblFarmerDetails.City, tblFarmerDetails.County, tblFarmerDetails.PostCode AS [Post Code], tblFarmerDetails.NutriFocusStatus AS [NutriFocus Status], RemovePunc([tblFarmerDetails].[AccountName]) AS Expr1, RemovePunc([tblFarmerDetails].[ContactName]) AS Expr2
FROM tblFarmerDetails
WHERE (((RemovePunc([tblFarmerDetails].[AccountName])) Like "*" & [Forms]![frmSearch]![Search2] & "*")) OR (((RemovePunc([tblFarmerDetails].[ContactName])) Like "*" & [Forms]![frmSearch]![Search2] & "*"));

 

[PHV]

And what happens to [Forms]![frmSearch]![ContactSearch2] ?

 

[NeilT123]

[Forms]![frmSearch]![ContactSearch2] gets deleted and I just use [Search2] as the search point for account name or contact name.

Neil