I have a solution to my problem, but it's clumsy. I'm sure I can do this with better written Query Criteria.
Has anyone got a slicker solution?
So.
Two tables (linked)
[TbClient] and [TbJobs]
Clients are unique but can have several Jobs
Two Forms:
[FmClientSearch] and [FmJobSearch]
The user will only have one of these open at a time (and we're too far down the rabbit hole to amalgamate them into one form)
My Query [QySearch] objective is to populate a listbox on a third form [FmResults], for user to click on choice of one property.
(Chosen property then used within a variety of reports.)
Query takes source data from [TbJobs] table, but criteria from the forms.
Query Field 1 is [ClientName]
Query Field 2 is [JobReference]
(Actually about 6 fields, but 2 for the sake of this discussion)
[ClientName] Criteria: Like "*" & [Forms]![FmClientSearch]![CliTargetField] & "*" OR [Forms]![FmClientSearch]![CliTargetField] Is Null
(this criteria is looking at a target field on a form coming originally from TblClient)
[JobReference] Criteria: Like"*" & Forms![FmJobSearch]![JobRefSrch] & "*" OR Forms![FmJobSearch]![JobRefSrch] Is Null
(this criteria is looking at a user-populated field on a form coming from TblJobs)
So it doesn't matter if one of the fields is blank, it simply runs the query on the other criteria.
Which is fine if both of my forms are open. But not if one of them is closed.
The user will have one form open in front of them.
My solution is to use the "Run Query" button macro to open the other form in the background with a blank field, and then close it all again afterwards, but it all feels a bit clumsy and likely to fall over at some point.
Is there a simple argument I can add to the criteria along the lines of " iif [FmClientSearch] isn't open then treat [CliTargetField] as Null "
I've been banging my head on the wall for hours about this, and know it has to be simple.
It's been many years since I used Access regularly, so I thought you guys might be able to help!
Cheers. :)