How to set a Drop down box to select all records 3

[Triacona]

Dear All,
Thanks for a great forum

I have the following problem...

I have a form MainScreen
It has a drop down box cmbDocsCommentsRelateTo
This dropdown box has the followng values
"Core Strategy PD";"SA";"Proposals Map";"General Comment"
The problem starts with the query...
The query PolConsultationReport
There are serveral other dropdown boxes:
cmbPolicy
cmbLegallyCompliant
cmbSound
cmbTestOfSoundness
cmbFullName
cmbOnBehalfOf
The query usually has Like "*" & [Forms]![MainScreen].[cmbFullName] & "*" etc. for each of the fields, that relate to the dropdown boxes.
The problem started with the field WhichDocDoesCommentRelateTo
It has Propasals and SA as choices, so if I used the like statement it would provide both.
As the user can use multiple dropdown boxes to generate the report, things got a bit complex, the dropdown box
cmbDocsCommentsRelateTo had to be filled otherwise there would be not data displayed, but this is not right.
The user needs to be able to view all of them if needed.

please help...I have tried using a * as a selection data, but this does not work either...

Thank you


Thank you,

Kind regards

Triacona

 

[MajP]

You may try something like this for each control.

(((customer.customerID)=[forms]![formName].[ctrlName] Or [forms]![formName].[ctrlName] Is Null));

So if you leave a control blank (null) the second half of the Or is true. Hence all records returned for that field criteria.

 

[Triacona]

Dear MajP,

Thank you for your post
Please could you elaborate..

You may try something like this for each control.

Is this the properties for each control? It is the Row Source in the properties in the control?
Would I then have to have a query for the field list instead of a value list?

So I guess my question is where does

(((customer.customerID)=[forms]![formName].[ctrlName] Or [forms]![formName].[ctrlName] Is Null));

go?
I have tried running it in a query and it works to a certain extent, I have to set all the other criteria's or as is null, and there are some for which I do not know if it would work, for example:

[Sound] & "" Like "*" & [Forms]![MainScreen].[cmbSound] & "*"

Thank you again for your help

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear MajP,
I have tried what I suggested in my last post, i.e. add or [Forms]![MainScreen].[cmbSound] is null. for each field in the query which is linked to controls.
It works if WhatDocDoesCommentRelateTo, if that has data or blank.
But the others, the moment they have data they don't work...
Please help
Thank you

Thank you,

Kind regards

Triacona

 

[MajP]

Please post entire SQL from query.

 

[Triacona]

Dear MajP,

Thank you for your reply
Here is the modified SQL to include the or IS NULL criteria.

SELECT CustomersMainTable.CustomerID, Representations.RepresentationID, [FirstName] & " " & [LastName] AS FullName, CustomersMainTable.FirstName, CustomersMainTable.LastName, CustomersMainTable.OnBehalfOf, CustomersMainTable.CompanyOrganisation, CustomersMainTable.Address1, CustomersMainTable.Address2, CustomersMainTable.Address3, CustomersMainTable.Address4, CustomersMainTable.Address5, CustomersMainTable.PostalCode, CustomersMainTable.AgentName, CustomersMainTable.AgentOrganisation, CustomersMainTable.AgentAddress1, CustomersMainTable.AgentAddress2, CustomersMainTable.AgentAddress3, CustomersMainTable.AgentAddress4, CustomersMainTable.AgentAddress5, CustomersMainTable.AgentPostalCode, Representations.SummaryofRepresentation, Representations.Page, Representations.Paragraph, Representations.ChangesNecessary, Representations.OfficerResponse, Representations.ParticipateinExam, Representations.WhyParticipateinExam, Representations.WhichDocDoesCommentRelateTo, Representations.Policydropdown, Representations.Legallycompliant, Representations.Sound, Representations.TestOfSoundness, Representations.[Council'sProposedAction]
FROM CustomersMainTable INNER JOIN Representations ON CustomersMainTable.CustomerID = Representations.CustomerID
WHERE ((([FirstName] & " " & [LastName]) Like "*" & [Forms]![MainScreen].[cmbFullName] & "*") AND ((Representations.WhichDocDoesCommentRelateTo)=[Forms]![MainScreen].[cmbDocsCommentsRelateTo]) 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 ((([Forms]![MainScreen].[cmbFullName]) Is Null) AND (([Forms]![MainScreen].[cmbDocsCommentsRelateTo]) Is Null) AND (([Forms]![MainScreen].[cmbOnBehalfOf]) Is Null) AND (([Forms]![MainScreen].[cmbPolicy]) Is Null) AND (([Forms]![MainScreen].[cmbLegallyCompliant]) Is Null) AND (([Forms]![MainScreen].[cmbSound]) Is Null) AND (([Forms]![MainScreen].[cmbTestOfSoundness]) Is Null));

[Red]Or the non modified:[/red]

SELECT CustomersMainTable.CustomerID, Representations.RepresentationID, [FirstName] & " " & [LastName] AS FullName, CustomersMainTable.FirstName, CustomersMainTable.LastName, CustomersMainTable.OnBehalfOf, CustomersMainTable.CompanyOrganisation, CustomersMainTable.Address1, CustomersMainTable.Address2, CustomersMainTable.Address3, CustomersMainTable.Address4, CustomersMainTable.Address5, CustomersMainTable.PostalCode, CustomersMainTable.AgentName, CustomersMainTable.AgentOrganisation, CustomersMainTable.AgentAddress1, CustomersMainTable.AgentAddress2, CustomersMainTable.AgentAddress3, CustomersMainTable.AgentAddress4, CustomersMainTable.AgentAddress5, CustomersMainTable.AgentPostalCode, Representations.SummaryofRepresentation, Representations.Page, Representations.Paragraph, Representations.ChangesNecessary, Representations.OfficerResponse, Representations.ParticipateinExam, Representations.WhyParticipateinExam, Representations.WhichDocDoesCommentRelateTo, Representations.Policydropdown, Representations.Legallycompliant, Representations.Sound, Representations.TestOfSoundness, Representations.[Council'sProposedAction]
FROM CustomersMainTable INNER JOIN Representations ON CustomersMainTable.CustomerID = Representations.CustomerID
WHERE ((([FirstName] & " " & [LastName]) Like "*" & [Forms]![MainScreen].[cmbFullName] & "*") AND ((Representations.WhichDocDoesCommentRelateTo) Like "*" & [Forms]![MainScreen].[cmbDocsCommentsRelateTo] & "*") 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] & "*"));

Thank you so much


Thank you,

Kind regards

Triacona

 

[PHV]

WHERE [FirstName] & " " & [LastName] Like "*" & [Forms]![MainScreen]![cmbFullName] & "*"
AND (Representations.WhichDocDoesCommentRelateTo=[Forms]![MainScreen]![cmbDocsCommentsRelateTo] OR [Forms]![MainScreen]![cmbDocsCommentsRelateTo] IS 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 so very much for your help!
It works brilliantly!
So as to my understanding, within the query it has a IS NULL value in the combo box.

So does it bring the value into the query?

Thank you again

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear PHV,

Thank you again.
I have tried to give you a star by clicking on

Thank PHV for this valuable post

Is there a problem with the website today?
I gave you a star a few days ago, but it seems to have removed it??
Thanks again

Thank you,

Kind regards

Triacona