Help with IIf(InStr) Formula (1 Viewer)

TonyNursey

New member
Local time
Today, 13:56
Joined
Dec 21, 2021
Messages
4
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??
 

plog

Banishment Pending
Local time
Today, 08:56
Joined
May 11, 2011
Messages
11,611
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.

 

TonyNursey

New member
Local time
Today, 13:56
Joined
Dec 21, 2021
Messages
4
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

Passionate Learner
Local time
Today, 06:56
Joined
Sep 22, 2014
Messages
1,159
"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

I’m here to help
Staff member
Local time
Today, 06:56
Joined
Oct 29, 2018
Messages
21,357
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...
 

Users who are viewing this thread

Top Bottom