Complex query from combo box value

[Johnmad86]

Hi,

Hopefully someone may be able to assist me here, I have a query that is working, but would like to add some extra complexity to it.

the query currently uses a search form for input with multiple fields, and displays all the results perfectly. I have a tick box which I can then filter the results so it only displays results where there is an entry in "PhoneMobile1". This is working, however I want to expand this filter so it can use a combo box if possible.

Currently I have the following code from a tick box:

IIf([forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]=-1 And [Candidates].[PhoneMobile1] Is Not Null,True,False)=[forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]

The issue is that there are three fields for Phone Numbers (PhoneMobile1, PhoneMobile2 and PhoneMobile3), and I'd like this query to be able to filter on these columns also.

e.g.
Combo box values:
Has Mobile, No Mobile, All

the "Has mobile" choice should display all results if there is an entry in any one or more of the PhoneMobile fields,
"No Mobile" selected should display all results where there is no entry in any of the three Mobile fields, and All should display all results.

Is anyone able to assist me with creating the query?
Thanks in advance

 

[namliam]

The kind off thing you are trying to do is not easily managed with references to form controls... and if its managed, it becomes very complex fast and too complex not soon later.

Instead you directly want to manipulate the SQL where and when needed to the details you need... check this thread for some ideas:
http://www.access-programmers.co.uk/forums/showthread.php?p=1300908#post1300908

 

[Johnmad86]

Thanks, for the reply.
I've had a read and looks like I'll have to try this different approach to the search query. I'm not sure it'll be how they want it, but I can see the existing query getting large and unmanageable very quickly.

 

[Mihail]

I think that a new table

tblCandidatesPhones
ID_CandidatePhone - PK
ID_Candidate - FK on table Candidates
PhoneNumber

can simplifier a lot your work

You will end up with only one control (a subform) in your main form and the queries will be a lot easier to design