Using a combo box exclude Null values

[Triacona]

Dear All,
Thanks for a great forum
I have the following problem...

I have a form MainScreen

It has a drop down box Council's Proposed Action

This dropdown box has the followng values

problem starts with the query...

The query PolConsultationReport

It has a field Council'sProposedAction

Which has a mixture of null and filled in values.

I used the following criteria for Council'sProposedAction field
[Forms]![MainScreen]![cmbCpa]
And in the Combo box Row Source: Is Not Null.
This did not then filter Council'sProposedAction to exclude null values...
Where am I going wrong?
Please help...
Thank you


Thank you,

Kind regards

Triacona

 

[PHV]

in the Combo box Row Source: Is Not Null
?????

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

 

[Triacona]

Dear PHV,

Thank you for your response.

I want the user to filter the query themselves by using combo boxes...
So if user chooses Is Not Null via the combo box then clicks run query, the query will run with is not null criteria in the field into which I input [FORMS]![MainScreen]![cmbCpa].

I hope that helps.
Thank you again

Thank you,

Kind regards

Triacona

 

[PHV]

You can't use keywords as parameter value.
A workaround:

SELECT ...
FROM ...
WHERE ([Council'sProposedAction]=[Forms]![MainScreen]![cmbCpa] OR ([Forms]![MainScreen]![cmbCpa]='Is Not Null' AND [Council'sProposedAction] IS NOT NULL))

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

 

[PHV]

Triacona, did you try my suggestion ?

 

[Triacona]

Dear PHV,

Thank you for your response, sorry really had a busy day yesterday...I will try and implement your suggestion today
Thank you again

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear PHV,

I implemented your solution and had to do a few changes.
I did this because it did not give the Is Not Null values when I filled in the combo box with Is Not Null.
Only problem is now if I leave the combo cmbCpa blank it gives no results...
The SQL is below.

WHERE ((([FirstName] & " " & [LastName]) Like "*" & [Forms]![MainScreen]![cmbFullName] & "*") AND ((Representations.WhichDocDoesCommentRelateTo)=[Forms]![MainScreen]![cmbDocsCommentsRelateTo]) AND ((Representations.[Council'sProposedAction])=[Forms]![MainScreen]![cmbCpa]) AND (([OnBehalfOf] & "") Like "*" & [Forms]![MainScreen]![cmbOnBehalfOf] & "*") AND (([Policydropdown] & "") Like "*" & [Forms]![MainScreen]![cmbPolicy] & "*") AND (([Legallycompliant] & "") Like "*" & [Forms]![MainScreen]![cmbLegallyCompliant] & "*") AND (([Sound] & "") Like "*" & [Forms]![MainScreen]![cmbSound] & "*") AND (([TestOfSoundness] & "") Like "*" & [Forms]![MainScreen]![cmbTestOfSoundness] & "*")) OR ((([FirstName] & " " & [LastName]) Like "*" & [Forms]![MainScreen]![cmbFullName] & "*") AND ((Representations.[Council'sProposedAction]) Is Not Null) AND (([OnBehalfOf] & "") Like "*" & [Forms]![MainScreen]![cmbOnBehalfOf] & "*") AND (([Policydropdown] & "") Like "*" & [Forms]![MainScreen]![cmbPolicy] & "*") AND (([Legallycompliant] & "") Like "*" & [Forms]![MainScreen]![cmbLegallyCompliant] & "*") AND (([Sound] & "") Like "*" & [Forms]![MainScreen]![cmbSound] & "*") AND (([TestOfSoundness] & "") Like "*" & [Forms]![MainScreen]![cmbTestOfSoundness] & "*") AND (([Forms]![MainScreen]![cmbCpa])='Is Not Null') AND (([Forms]![MainScreen]![cmbDocsCommentsRelateTo]) Is Null));

Any thoughts?
Thank you again for your help

Thank you,

Kind regards

Triacona

 

[PHV]

I hate the SQL generated by the query grid ...
I'd use this where clause (IN SQL VIEW):

WHERE [FirstName] & " " & [LastName]) Like "*" & [Forms]![MainScreen]![cmbFullName] & "*"
AND (Representations.WhichDocDoesCommentRelateTo=[Forms]![MainScreen]![cmbDocsCommentsRelateTo] OR [Forms]![MainScreen]![cmbDocsCommentsRelateTo] Is Null)
AND (Representations.[Council'sProposedAction]=[Forms]![MainScreen]![cmbCpa] OR [Forms]![MainScreen]![cmbCpa] IS NULL OR(Representations.[Council'sProposedAction] IS NOT NULL AND [Forms]![MainScreen]![cmbCpa]='Is Not Null'))
AND [OnBehalfOf] & "" Like "*" & [Forms]![MainScreen]![cmbOnBehalfOf] & "*"
AND [Policydropdown] & "" Like "*" & [Forms]![MainScreen]![cmbPolicy] & "*"
AND [Legallycompliant] & "" Like "*" & [Forms]![MainScreen]![cmbLegallyCompliant] & "*"
AND [Sound] & "" Like "*" & [Forms]![MainScreen]![cmbSound] & "*"
AND [TestOfSoundness] & "" Like "*" & [Forms]![MainScreen]![cmbTestOfSoundness] & "*"

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

 

[Triacona]

Dear PHV,

Thank you very much for your reply
I have been on holiday, sorry for getting back so late.
I will implement your solution to see if it works.

Thank you again

Thank you,

Kind regards

Triacona