Query Criteris - Searching for Text in String (1 Viewer)

music_al · Nov 13, 2018

Hi

I have a table for Status Values...

ID Status Value Preceeding_Status_ID
26 CV Received 0
27 Invited for Interview 26, 29
28 Rejected at Shortlisting 26
29 Interviewed 27
30 Rejected after Interview 29
31 Contract Offered 29, 32
32 Contract Accepted with Conditions 31
33 Contract Accepted 31
34 Contract Started 33

The preceeding status ID field is used in a query and the criteria is the ID field. So, as soon as CV Received is selected from a Combo Box, the Combo Box requeries itself and shows any list items that have 26 in the preceeding status ID field. Its a sort of Workflow Combo Box.

However, I want to enter more than one value in the preceeding status ID field. So, in this example, how would I find 26 in the preceeding status ID field ? What syntax would I use in my query criteria ?

Ive managed to do it by adding a SECOND field for Preceeding Status ID but I'd like to know how I would do it this way.

I hope this is clear. Thank you in advance.

music_al · Nov 13, 2018

That looks untidy. How do I insert a table in here ?

isladogs · Nov 13, 2018

In answer to your last question, use code tags to display tables more clearly.

To get more than one value in a field, you would need to use a multivalue field.
However, these cause significant issues when it comes to searching and filtering.
For that reason most developers avoid them.
Instead create a separate record for each previous statusID.
Do not add a second field nor a third, fourth field as time goes on ....

Ranman256 · Nov 13, 2018

create a table, called tPicked.
enter (1 per cell) all the values you want to search for.
join this table to your data table on the common field.
run the query, only those values will return.

for ease of use, make a form, with a listbox. (2 list boxes)
dbl-click the list box of all possible values (lstSrc) to run an append query to the tPicked table. (lstTarg)

see image:

music_al · Nov 13, 2018

I don't think these are the right solutions. I'm just trying to find a value within a field.

So, if I had (and this is just an example), if I had a field with Fullname and Age (John Smith 45) and I wanted to search for anyone with 45 in that field, this is what I am effectively trying to do. Its just that the 45 is coming from the value in the Combobox.

music_al · Nov 13, 2018

So if I had a field with

26, 30
29, 30
31, 36

I might want to return the rows that have 30 in them.

The source of the '30' comes from the combo box.

I tried this criteria but it doesn't work...

Code:

Like "* [Forms]![frm_Testing_Status]![cbo_Status]"

isladogs · Nov 13, 2018

I don't think these are the right solutions. I'm just trying to find a value within a field.

Yes I realise that.
The problem you have is the use of a multivalue field (MVF)

I'll try to explain in more detail - possibly too much detail!
The answer to your question is included in there but in my view its important you understand what's going on

A basic rule of all relational databases including Access is that each item (i.e. field in a table) should only one one item of data. This is important for normalisation of data
However in A2007 they introduced the idea of MVFs which appear to break the rule. As I'm sure you know, the data listed in the MVF can be based either on a lookup table or you can type in the values. However you create it, in reality what you see in the MVF is masking the fact that the records are actually stored as normalised data in a deep hidden system table with a long name that starts with 'f_' followed by a long string then ends in '_MVF' or whatever your MVF field is called.

This is an example table with an MVF field I've created specially for this purpose - see attached. I typed in the values in this case

Notice although I'm using numbers, its a text field which is why 13 is before 2 etc.

Here are the contents of the system table f_35E0136C98BF44A68A22B48C4CF1B2EC_MVF showing how Access stores that data

To show you that, I had to resort to some trickery....
Unfortunately, you can't see the table in the nav pane even if system tables are made visible. However if you search the MSysObjects system table (TAKE CARE) you will find its name.
Aha you may think - I'll just search that table using a query
Try copying that name and create a query in SQL view such as

Code:

SELECT * FROM f_35E0136C98BF44A68A22B48C4CF1B2EC_MVF

This fails with a message saying Access cannot find the table '' (it claims not to recognise the name of the table with your data)
So forget that table completely - you can't use it!

Returning to the original table, make a query based on the MVF field and try filtering for the value 3 which is in several records

Code:

SELECT Table1.ID, Table1.MVF
FROM Table1
WHERE (((Table1.MVF.Value) Like "*3*"));

This gives the following - almost certainly not what you want:

Instead you need to query on MVF.Value:

Code:

SELECT Table1.ID, Table1.MVF.Value
FROM Table1
WHERE (((Table1.MVF.Value)="3"));

Notice the "" marks around 3 - remember its a text field. This gives:

Now you may be happy with this result and decide that MVFs are a great idea. I would strongly recommend you don't do so.

If you don't believe me try running an update or append query on the MVF field. Its possible but very messy and you will get even more confused about what data is being stored
Whereas if you had stored your data in a normalised table with one value per record, searching, filtering & editing would be trivial

For more details about MVFs see this article by MS. It both explains how they work and glosses over the issues with them
https://support.office.com/en-us/article/guide-to-multivalued-fields-7c2fd644-3771-48e4-b6dc-6de9bebbec31

Here is a link to another article/demo file by the DBGuy: http://www.accessmvp.com/thedbguy/demos/mvfattachment.asp

One last point - if you ever need to upsize your database to SQLServer, multivalue fields (& attachment fields) cannot be imported. So at that point they all need to be replaced with standard datatypes / methods of storage

Here endeth the sermon! I'll get off my hobby horse now!

music_al · Nov 13, 2018

I really appreciate the time and effort that you guys go to in replying to my post.

Just to be clear - I'm not using a multi-value field - its just a Text field with values separated by a comma.

Could I just ask though, what syntax I would use to just return a row from a query if a Number (the ComboBox ID) is found within a longer string in another field ?

isladogs · Nov 13, 2018

LOL...

I'd partly written it as an article for my website...so its not completely wasted!

Actually you are in effect using a 'multivalue field' in this context but not using its 'supposed advantages'. So you've got the worst of all scenarios!

I think the answer was included in my lengthy response but the correct syntax is

Code:

SELECT ID, TextFieldName
FROM TableName
WHERE (((TextFieldName) Like "*NumberValue*"));

However i think you are unlikely to be happy with the result

If I've misunderstood what you mean, feel free to ask again!

music_al · Nov 13, 2018

Thank you Isladogs.

How do I apply that query to a criteria box in my query. I am guessing I have to replace the criteria I have...

Code:

[Forms]![frm_Testing_Status]![cbo_Status]

Its just that I do have the same criteria but against 3 different fields in the query as shown in the attachment above.

MajP · Nov 13, 2018

Code:

SELECT 
 tblOne.ID, 
 tblOne.Field1
FROM 
 tblOne
WHERE 
 InStr([field1],[forms]![Form1]![Combo0])>"0";

Works for me. Not sure why it converts 0 to "0", but seems to work

isladogs · Nov 13, 2018

I'm no longer clear if you have separate fields as in post #1 or a pseudo MVF...

Try this

Code:

Like "*" & [Forms]![frm_Testing_Status]![cbo_Status] & "*"

If that errors, try this with extra quotes at start and end

Code:

Like ""*" & [Forms]![frm_Testing_Status]![cbo_Status] & "*""

music_al · Nov 13, 2018

Thanks guys but I'm struggling to convert your answers to what I actually need ?

How do I change this...

Code:

[Forms]![frm_Testing_Status]![cbo_Status]

so that it looks for the value in cbo_Status (which is a combo box) in the column/field in which it is shown in the attached file ?

music_al · Nov 13, 2018

Thanks Isladogs - I will try that when I can get back into the DB later

music_al · Nov 13, 2018

I will create a scaled down DB and post it later so you can see what I'm trying to do

music_al · Nov 13, 2018

I hope this better explains what I'm trying to do...

In the table, I'd rather have the preceding status IDs in ONE field as opposed to two as it is now.

As it is, it works exactly as I want it to, I'd just like to know how I can do it with ONE preceding status ID field.

View attachment Dynamic Combo Workflow.zip

music_al · Nov 13, 2018

Looks like your line of code below worked Isladogs. Thank you mate

Code:

Like "*" & [Forms]![frm_Testing_Status]![cbo_Status] & "*"

isladogs · Nov 13, 2018

I don't think you need the Like part at all
Have a look at the attached where i've added the query as a subform.
I've also changed the combo box to use the table instead of the query.

A better way would be to link the form and subform using the Status_ID field but i'll leave that to you....

AND of course I still say you should get rid of your MVF field (pseudo or otherwise)

Good luck with your project

Query Criteris - Searching for Text in String (1 Viewer)

Registered User.

Registered User.

MVP / VIP

Well-known member

Registered User.

Registered User.

MVP / VIP

Attachments

Registered User.

MVP / VIP

Attachments

Registered User.

You've got your good things, and you've got mine.

MVP / VIP

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread