Filter list box on form by using a control on the form (1 Viewer)

[TimTDP]

On a form I have a:
control called "FilterListBox"
list box called "lstCustomer"
option Box called "optCustomerType"

When the user selects an option in the option box, "FilterListBox" is updated
to either "1", "2" or "1 or 2"

One of the fields in the query for "lstCustomer" is "CustomerType" and its criteria is set as follows:

[Forms]![frmPrintHowCustomersPaidInvoice]![FilterListBox]

if "FilterListBox" = 1 the query for "lstCustomer" returns the correct records
if "FilterListBox" = 2 the query for "lstCustomer" returns the correct records

But if "FilterListBox" = 1 or 2, no records are returned.

What am I doing wrong?

Many thanks in advance

 

[CJ_London]

What am I doing wrong?

"FilterListBox" = 1 or 2 should be written as

FilterListBox = 1 or FilterListBox=2

the way you have written this does not make grammatical sense whether written in a query or VBA so I'm guessing you have paraphrased it

However there is an alternative, but you'll need to work out how this translates into your coding

change

either "1", "2" or "1 or 2"

to

either "1", "2" or "1,2"

and

FilterListBox in (1,2)

 

[TimTDP]

Thanks CJ

Understand using In(), however

FilterListBox = In (1,2)

does not work!

Am I missing a quote?

 

[CJ_London]

only if your numbers are actually text in which case it will be

FilterListBox = In ("1","2")

 

[TimTDP]

only if your numbers are actually text in which case it will be

FilterListBox = In ("1","2")

My numbers are numbers!

Is the following correct?

One of the fields in the query for "lstCustomer" is "CustomerType" and its criteria is set as follows:

[Forms]![frmPrintHowCustomersPaidInvoice]![FilterListBox]

when FilterListBox = In (1,2)

 

[CJ_London]

I think I need to see how you are building your sql - is it in a query or vba - if the former, please post the query string and the latter the vba code

 

[TimTDP]

Hi CJ

I have taken the liberty of attaching a breakdown
There is only on form and I have highlighted the option button not working.

Many thanks

 

[CJ_London]

you need a different construct for your query - copy this into your lstcustomer rowsource

 SELECT DISTINCT tblCustomer.CustomerID, tblCustomer.CustomerName, tblCustomer.CustomerStatusId, tblInvoice.TradePrices
FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID = tblInvoice.CustomerId
WHERE (((tblCustomer.CustomerName) Is Not Null) AND ((tblInvoice.TradePrices) Like [txtTradeCustomer]) AND ((tblInvoice.InvoiceDate) Between [dteStartDate] And [dteEndDate]) AND (([CustomerStatusId]=[optCustomerActive] Or [optCustomerActive]=3)=True));

Note you do not need the form reference since the options are on the same form (although they do need the square brackets).

You also don't need the txtActiveCustomer control

 

[TimTDP]

Hi CJ

Many thanks for this

Can you please explain:
[CustomerStatusId]=[optCustomerActive] Or [optCustomerActive]=3

I don't understand the logic!

 

[CJ_London]

the record will be included if [CustomerStatusId]=[optCustomerActive]

Or [optCustomerActive]=3


So if you have selected option 1 in optCustomerActive, you will see all records where CustomerStatusId=1

So if you have selected option 2 in optCustomerActive, you will see all records where CustomerStatusId=2

So if you have selected option 3 in optCustomerActive, you will see all records