Create Filter Values from Listbox

[#1]

Hi,

I have a straight table that shows all my information from a specific database. 

I have created a few list boxes (thanks to this forum) that filters the year and the month separately. 

However I need to create another list box to filter specific data.  In my Database and straight table, there is a column called probcode.  In my list box I want to filter by just Fault or Request. 

However in the probcode column the data shows as such

FLT0-CRPR-CYBR-PSS1

FLT0-CRPR-RCL0-SS00

RQST-RSFT-QLKV

RQST-RSFT-SVRN-NBLL

Basically the first 4 characters will ever only be FLT0 or RQST.  FLT0 meaning Fault and RQST meaning Request. 

Is there way that my listbox can show a tickbox for Fault and a Tickbox for Request, that when ticked it either brings up all the FLT0 or the RQST.

Regards,

Jon Ditchfield

[swuehl]

Try a field expression in your list box like

=if(left(probcode,4)='FLT0', 'Fault','Request')

[#3]

Hi swuehl,

I have been given wrong information.  Apparently there may be some extra ones that are either blank or start with NQRY.  Is there anyway we can get this into the expression so we have the following

Fault = 'FLT0'

Request = 'RQST'

Enquiry='NQRY'

Blank= 'NULL'

[swuehl]

Just double checked, seems to work on my side. If you say, "brings up the RQST", what do you mean in detail.

I check by creating a list box with above field expression and by creating a list box with field probcode.

If I select either Fault or Request, I only see the correct values in probcode list box selected.

[#5]

Hi swuehl,

You are correct, its the information I was given,  I have amended my previous update.  Sorry for the incorrect information.

Regards,

Jon

[swuehl · Accepted solution]

Ah, ok, try something like:

=if(len(trim(probcode))=0,'BLANK',

pick(

match(left(probcode,4),'FLT0','RQST','NQRY')

,'Fault','Request','Enquiry')

)

[#7]

Hi Swuehl,

That exactly how I wanted it.  Thanks for the help yet again.

[gfisch13]

Hi Stefan - I'm working on a similar procedure.  Using the example above, what if the search values are not in the first 4 characters.   I have a field in my table where values can be up to 50 characters long.

Thanks!

I've created content on my issue if you would like to review:  Keyword Listbox