Solved Query criteria on string to return all or something (1 Viewer)

[Romio_1968]

I have a string Query field with the following structure
;1;14;8;17;62;
So they are numbers that are allways sided by semicolons
The field will contain at least one number ;n;
I need a criteria that based on a control value to return all the records for the case control is Null or empty, or the record containin ";x;", for the case the control is filled.

I tried

Like (IIf(IsNull([Forms]![SearchTitle_Frm]![Inventory_No]) Or [Forms]![SearchTitle_Frm]![Inventory_No], "*", ";" & [Forms]![SearchTitle_Frm]![Inventory_No]) &";")

Works good for the numerical values, but fails to return all records in case of null or empty
What am I doin wrong?

Thank You

 

[cheekybuddha]

 WHERE YourField LIKE '*;' & [Forms]![SearchTitle_Frm]![Inventory_No] & ';*' OR [Forms]![SearchTitle_Frm]![Inventory_No] IS NULL

 

[Romio_1968]

 WHERE YourField LIKE '*;' & [Forms]![SearchTitle_Frm]![Inventory_No] & ';*' OR [Forms]![SearchTitle_Frm]![Inventory_No] IS NULL

this is not working at all.
Same result. Numerical is found, but emty fails to retrieve all

 

[Gasman]

this is not working at all.
Same result. Numerical is found, but emty fails to retrieve all

So 'empty' is not Null?

Try =""

 

[cheekybuddha]

Where are you using this code? Is it in the query definition or in VBA code or ... ?

Copy and paste here exactly what you tried.

 

[cheekybuddha]

Where did you place that code?

 

[Romio_1968]

Where are you using this code? Is it in the query definition or in VBA code or ... ?

Copy and paste here exactly what you tried.

I tried this
Like (IIf(IsNull([Forms]![SearchTitle_Frm]![Inventory_No]) Or [Forms]![SearchTitle_Frm]![Inventory_No], "*", ";" & [Forms]![SearchTitle_Frm]![Inventory_No]) &";")

Failed to retrieve all records for Null or Empty

And also tried yours, with same result

 

[cheekybuddha]

I know that's what you tried, but where did you try it?

 

[Romio_1968]

Where did you place that code?

In the criteria field of the query design (without the WHERE condition)

 

[cheekybuddha]

Ok, maybe try it like:

LIKE '*;' & [Forms]![SearchTitle_Frm]![Inventory_No] & ';*' OR Trim([Forms]![SearchTitle_Frm]![Inventory_No] & '') = ''

 

[Romio_1968]

Yes... this is working.
Thank You

 

[cheekybuddha]

 

[ebs17]

I have a string Query field with the following structure

Why are people here so uncritical and take such a condition for granted?
You can only build a palace out of a chicken coop if you tear down the coop beforehand.

 

[Romio_1968]

Why are people here so uncritical and take such a condition for granted?
You can only build a palace out of a chicken coop if you tear down the coop beforehand.

?

 

[cheekybuddha]

Why are people here so uncritical and take such a condition for granted?

Why don't you just explain to the OP the disadvantages of storing multiple values in a single field and the benefits of normalising further to use a related child table with foreign key, both in form input and subsequent querying.

Sometimes when I ask for directions to the nearest petrol station I just want to be given directions and not be told that I really should have an electric car and then I could have a charging point of my own right outside my house.

I have never come across the OP before, so I would rather have one or two interactions with them before I pass judgement on their technical ability. Yes, storing the values in a separate table is almost guaranteed to be a better option, but the OP may also have a valid reason for wanting to use separated values.