Hi everyone,

I am looking for the best way to approach this task
I have a sample name which is written by the user and not necessarily put in the same order every time. there are a few key strings to look for. What I want is a query or module that will update a field in the table based on whether or not it finds a str in the sample name.

these keywords would be things like "mdl" "std" and others. what I have com up with is using the instr function to find the keyword string and then update the field based on that. I cant figure out the syntax for instr though to save my life.

there must be an easier way because this would create multiple redunant queries and be too time consuming

hope someone can shed some light on this for me

Cory

InStr(StartPosition, StringToSearch, StringToFind) is the syntax. As an example, if the [Name] field of a table contains "Baron von Richtofen", and you want to find the string " von ", you would type this in a query:

Expr1: InStr(1, [Name], " von ")

Expr1 would be set equal to the character position where " von " starts. It is one-based so it would be 6.

I'm not at all sure how I would approach your problem. I hate multiple queries too. Do you know what strings you will be searching for? If so, you could put them in a table and use the entries in that table to feed the criteria values in a query. This would need to be done via a User-Defined Function.

To add to Frank's suggestion, you can do the following:

Expr1: (InStr([fieldname],"mdl")>0) Or (InStr([fieldname],"std")>0) Or (...)

Put in a criteria of TRUE for this expression field.

Now, your query will only return the rows that have a match for one of those strings. You can now make that an UPDATE query, if needed.

To add to Frank and Peter,

the Instr() function takes a final optional argument which is to do with the method of comparison.

The default is vbBinaryCompare which has a value of 0. This means that the comparison will be CASE-SENSITIVE.

If your users may be lax in entering the case so that you want to find 'Mdl' as well as 'mdl' then construct the criteria using the value for vbTextCompare (value = 1) - however, if you do so then you must also include the first optional argument for start position:

Expr1: (InStr(1, [fieldname],"mdl", 1)>0) Or (InStr(1, [fieldname],"std", 1)>0) Or (...)


hth,

d

THANK YOU GUYS FOR THE SUPERB HELP

I AM SO GLAD I FOUND THIS FORUM

WEALTH OF INFORMATION

CORY

Following up on this
Can I update a field in code

for example a case statement

instr(1,[sample_name],"mdl",1)>0
update [injection_type] with "MDL study" (These strings are from a lookup table called injection_type_lookup)

instr(1,[sample_name],"std",1)>0
update [injection_type] with "Standard"

instr(1,[sample_name],"hexane",1)>0
update [injection_type] with "Solvent Blank"

or is there a better way

Yes, you can. What I would do, since I despise writing SQL and avoid whenever I can, is develop an Update Query in the Query Designer but DON'T run it, then copy its SQL statement to the code window. You could even run the query from the code window if you wanted.

Hi there,

You may want to look into the Replace() function to help you in this task. Since you are looking for occurrences of 'mdl' in the string I assume you would only want to change that part to 'MDL Study' but not anything else in the string.

You could probably avoid using the Instr() function altogether.



The code is untested, but to use it first make a backup copy of your table ( ), then type in the immediate window (Ctrl + G):

?fUpdateField("mdl", "MDL Study")

You can then repeat for the other bits of text to update. If you have loads then you could write a sub that gathers all the text to be updated and replacement text in to an array, loop through it calling the update function on each pass.

hth,

d