Help with IIf(InStr) Formula

[TonyNursey]

Help Please!!

I'm trying to populate a text field with a designated number from a precise naming convention.

An example of the formula I have been using is:

IIf(InStr([Name],"Prod Eng K.O")>0,"002","")

The problem I have is that if I have another task with the Name "Prod Eng K.O 2" then text field is also populated incorrectly with the designated number 002 because the string in the name field contains the character syntax the formula needs to see....

How can I change the formula to require an exact match to all characters contained within the Name field??

 

[oleronesoftwares]

IIf(InStr([Name],"Prod Eng K.O")>0,"002","")

you might have to use functions like
mid
left
right etc.

Write down the common names and share with us.

 

[plog]

Just compare the 2 strings exactly instead of inexactly:

iif([Name]="Your Exact String", True, False)

Also, you shouldn't use "Name" as a name of an object (field, table, input etc) in Access because its a reserved word and can cause issues with code and queries.

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com

 

[TonyNursey]

you might have to use functions like
mid
left
right etc.

Write down the common names and share with us.

So I have "Tryout Parts Ordered (VDTP 1T40 Parts Only)" and also "Tryout Parts Ordered (AFG29 & 1G50)" and Finally Just "Tryout Parts Ordered"

"Tryout Parts Ordered" needs to populate a separate text field with the code 167
"Tryout Parts Ordered (VDTP 1T40 Parts Only)" needs to populate a separate text field with the code
"Tryout Parts Ordered (AFG29 & 1G50)" needs to populate a separate text field with the code 168

 

[oleronesoftwares]

"Tryout Parts Ordered" needs to populate a separate text field with the code 167
"Tryout Parts Ordered (VDTP 1T40 Parts Only)" needs to populate a separate text field with the code
"Tryout Parts Ordered (AFG29 & 1G50)" needs to populate a separate text field with the code 168

If these are the only three conditions, you can create a table with two fields having values
Tryout Parts Ordered and 167
Tryout Parts Ordered (VDTP 1T40 Parts Only) and 1T40
Tryout Parts Ordered (AFG29 & 1G50) and 168



then in the other form create a combo box that looks up the name and codes (two column combo box}

in VBA
Under the after update event of the combo box
me.textfield=me.combobox.column(1)

 

[theDBguy]

Hi. If you have a "lot" of strings to compare, you might do better using a separate lookup table for them and then just use a JOIN in your query to get the return value.

Edit: Oops, too slow...

 

[Pat Hartman]

I see no pattern and that means you can't use code. Create a cross-reference table as theDBguy suggested.