Ignore Wildcard if Criteria is Blank (1 Viewer)

[Damo1412]

Hi,

In a database am building, I want to run a query with the criteria dependant on which field the user populates in a form.

The form has a number of fields that the user can select from including our reference number, the client's reference number and the site address.

I would like the user to be able to select the site address using a wildcard so that they can enter a part of the address such as "This Street" instead of "45 This Street" and the user be presented with all of the records matching "This Street".

I tried using the criteria:

Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"

Which works perfectly as long as this field is populated. If this field is not populated, entering details in any other field bring up every record in the database.

After some searching, I believe that I need to use an IIF function to resolve this however I cannot get this to work. The code I have played around with so far is:

IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*")

If it is of any help, the full sql of my query is:

SELECT Jobs.JobsSalesEnquiryRecordNumber, Jobs.JobsJobStatus, Jobs.JobsEnquiryDate, Jobs.JobsTakenBy, ClientDetails.ClientDetailsURN, ClientDetails.ClientDetailsName, ClientDetails.ClientDetailsAddressLine1, ClientDetails.ClientDetailsAddressLine2, ClientDetails.ClientDetailsAddressLine3, ClientDetails.ClientClientPostCode, Jobs.JobsClientJobNumber, Jobs.JobsAlternativeClientJobNumber, Jobs.JobsClientContact, ClientDetails.ClientDetailsTelephone, SiteDetails.SiteDetailsContactName, SiteDetails.SiteDetailsContactTelephone, SiteDetails.SiteDetailsURN, SiteDetails.SiteDetailsAddressLine1, SiteDetails.SiteDetailsAddressLine2, SiteDetails.SiteDetailsAddressLine3, SiteDetails.SiteDetailsPostCode1, SiteDetails.SiteDetailsPostCode2, AgentDetails.AgentDetailsName, AgentDetails.AgentDetailsAddressLine1, AgentDetails.AgentDetailsAddressLine2, AgentDetails.AgentDetailsAddressLine3, AgentDetails.AgentDetailsPostCode, AgentDetails.AgentDetailsTelephone, Jobs.JobsWorkRequired, Jobs.JobsQuoteDueBy, Jobs.JobsQuotesURN, Jobs.JobsQuotesStarted, Jobs.JobsQuoteCompletedBy, Jobs.JobsQuoteSentDate, Jobs.JobsQuoteSentBy, Jobs.JobsQuoteSentTo, Jobs.JobsKillQuote, Jobs.JobSubcontractorURN, SubContractorDetails.SubContractorDetailsName, Jobs.JobsSubcontractorAllocationDate, SubContractorDetails.SubContractorDetailsEmail, Jobs.JobsOperativeURN, OperativesDetails.OperativesDetailsFirstName, OperativesDetails.OperativesDetailsSecondName, Jobs.JobsOperativeAllocationDate, OperativesDetails.OperativesDetailsEmail, Jobs.JobsLive, Jobs.JobDueBy, Jobs.JobsPlannedDate, Jobs.JobsPropertyAssessmentURN, Jobs.JobsPropertyAssessmentCompleted, Jobs.JobsCompletionDate, Jobs.JobsJobCompleted, Jobs.JobsPicturesSent, Jobs.JobsPicturesSentBy, Jobs.JobsPicturesSentDate, Jobs.JobsPicturesSentTo, Jobs.JobsCompletionPicturesSent, Jobs.JobsCompletionPicturesSentBy, Jobs.JobsCompletionPicturesSentTo, Jobs.JobsCompletionPicturesSentDate, Jobs.JobsInvoiceDate, Jobs.JobsInvoiced, Jobs.JobsJobNote1, Jobs.JobsJobNoteDate1, Jobs.JobsJobNote2, Jobs.JobsJobNoteDate2, Jobs.JobsJobNote3, Jobs.JobsJobNoteDate3, Jobs.JobsJobNote4, Jobs.JobsJobNoteDate4, Jobs.JobsJobNote5, Jobs.JobsJobNoteDate5, Jobs.JobsJobNote6, Jobs.JobsJobNoteDate6, Jobs.JobsJobNote7, Jobs.JobsJobNoteDate7, Jobs.JobsJobNote8, Jobs.JobsJobNoteDate8, Jobs.JobsJobNote9, Jobs.JobsJobNoteDate9, Jobs.JobsJobNote10, Jobs.JobsJobNoteDate10, Jobs.JobsPropertyAssessmentSentTo, Jobs.JobsPropertyAssessmentSentDate, Jobs.JobsQuoteCompleted, Jobs.JobsRecall
FROM OperativesDetails RIGHT JOIN (SubContractorDetails RIGHT JOIN (AgentDetails RIGHT JOIN (SiteDetails RIGHT JOIN (ClientDetails INNER JOIN Jobs ON ClientDetails.ClientDetailsURN = Jobs.JobsClientURN) ON SiteDetails.SiteDetailsURN = Jobs.JobsSiteURN) ON AgentDetails.AgentDetailsURN = Jobs.JobsAgentURN) ON SubContractorDetails.SubContractorDetailsURN = Jobs.JobSubcontractorURN) ON OperativesDetails.OperativesDetailsURN = Jobs.JobsOperativeURN
WHERE (((Jobs.JobsSalesEnquiryRecordNumber)=[Forms]![SearchJobs]![SearchAdvancedReferenceNumber])) OR (((SiteDetails.SiteDetailsAddressLine1)=IIf(IsNull([Forms]![SearchJobs]![SearchAddressLine1]),Null,[Forms]![SearchJobs]![SearchAddressLine1] & "*"))) OR (((ClientDetails.ClientDetailsURN)=[Forms]![SearchJobs]![SearchClientCombo]) AND ((Jobs.JobsClientJobNumber)=[Forms]![SearchJobs]![SearchClientReferenceNumber])) OR (((SiteDetails.SiteDetailsPostCode1)=[Forms]![SearchJobs]![SearchPostCode1]) AND ((SiteDetails.SiteDetailsPostCode2)=[Forms]![SearchJobs]![SearchPostCode2])) OR (((ClientDetails.ClientDetailsURN)=[Forms]![SearchJobs]![SearchClientCombo]) AND ((Jobs.JobsAlternativeClientJobNumber)=[Forms]![SearchJobs]![SearchClientReferenceNumber])) OR (((SiteDetails.SiteDetailsURN)=[Forms]![SearchJobs]![Combo38]))
ORDER BY Jobs.JobsSalesEnquiryRecordNumber DESC;

Can you please show me where I am going wrong.

Thanks

 

[JHB]

Try the below combination.

Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*" OR  [Forms]![SearchJobs]![SearchAddressLine1] Is Null

 

[Damo1412]

Hi JHB,

Unfortunately that still shows every result if the "[Forms]![SearchJobs]![SearchAddressLine1]" field is left blank. I also tried using:

[Forms]![SearchJobs]![SearchAddressLine1] Is Null Or Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"

but this had the same result.

 

[BlueIshDan]

What is the result?

 

[BlueIshDan]

Also, would this work?

WHERE field LIKE "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"
      AND [Forms]![SearchJobs]![SearchAddressLine1] IS NOT NULL

 

[CazB]

Try this format for your WHERE?

WHERE (((IIf(Not IsNull[Forms]![SearchJobs]![SearchAddressLine1]),SiteDetails.SiteDetailsAddressLine1 Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*",-1))=True)

Basically it says: if searchaddressline1 is blank, ignore it, otherwise match it

 

[Damo1412]

Hi BlueIshDan,

With my code, if the user enters the criteria in any filed the search works fine but no matter what they enter in the address field (full or part address) the result is always blank.

Unfortunately, with your code entering any address produces no results but entering any other criteria displays all records in the database.


CazB,

when I tried your code I receive an error message referring to the "wrong number of arguments". looking at the code I think there is either a "(" missing or a surplus ")" though I do not know enough about these statements to work out which one.

 

[BlueIshDan]

WHERE field LIKE "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"
      AND LEN([Forms]![SearchJobs]![SearchAddressLine1] & "") > 0

How about this. Maybe it isn't null. Maybe it's just an empty string.

 

[JHB]

Hi JHB,

Unfortunately that still shows every result if the "[Forms]![SearchJobs]![SearchAddressLine1]" field is left blank. I also tried using:

[Forms]![SearchJobs]![SearchAddressLine1] Is Null Or Like "*" & [Forms]![SearchJobs]![SearchAddressLine1] & "*"

but this had the same result.

Sorry I think I misunderstood your requirement, good luck.

 

[Damo1412]

Hi BlueIshDan,

Whilst playing around in between your posts I realised that I had done something that affected the query so I re-created it from a back-up and have tried both of your criteria again.

Using either of your codes, entering the criteria in any field works perfectly apart from the address field where it will not display any results for either a partial or full address.

 

[BlueIshDan]

dno lol
[SPACE FILLER]

 

[Brianwarnock]

Like JHB I too am perplexed by what is required, looking at the original SQL it is an odd mix of And and Or, normally when wanting to run a query with a selection of criteria that may be omitted the basic is

Where
(Fielda=param1 or param1 is null) AND
(fieldB = param2 or param2 is null) AND
etc

I think the Like construct first mentioned by JHB would fit into this scenario and work, the Is Null part is not really required for Like as Like "*""*" will presumably select everything and thus place the selection under the control o the other parameters anyway.

I would start the criteria again and slowly build it, but would do it all in SQL, using the Design grid adds complexity of excess brackets

Brian

 

[Damo1412]

Hi Brianwarnock,

Thanks for the feedback. Unfortunately the query was created using Access' query design as I know nothing about SQL and merely posted the code in case it assisted with resolving the problem.

To hopefully explain the layout, this is the form that the user uses to select the criteria:

The user can enter our reference number, select the client and their reference number, select the first line of the address from a combo box, or the post code for the site (which is in two parts).

They can currently enter the first line of the address as text but currently this much be the full and exact text. This is the field where I would like them to be able to enter part of the address and perform a wildcard search on that field if it has been completed.

I hope this makes sense.