Find Function and Return Value

[Whughes98]

I am trying to write a query that looks within a field for a specific value and returns that value to me, but I can't figure out a formula to do it. The value is not is the same position for each row. Example below:

Table: GL Detail
Field: Modifiers


Looking for value contained in the field = 'U1' Return the value as '1'

Sample values in the field:

U1:U7
U1:U6
GT:U6:U1
GT:U1:U6

Can anyone give me a formula or SQL script that would help me with this?

Thanks
Wendy

[orange]

What exactly would be returned in these samples?
U1:U7
U1:U6
GT:U6:U1
GT:U1:U6

Also, it is advisable to not use table or field names with embedded spaces.

[rpeare]

Create a module

Put this code in the module and save it

Public Function GetGLPosition(sFieldContents, sSearchString)Dim ArrayItems


If InStr(sFieldContents, ":") Then
    ArrayItems = Split(sFieldContents, ":")
    For I = 0 To UBound(ArrayItems)
        If sSearchString = ArrayItems(I) Then
            GetGLPosition = I + 1
            Exit Function
        End If
    Next I
Else
    If sSearchString = sFieldContents Then GetGLPosition = 1
End If
End Function

in your query the syntax would be

GLPosition: getglposition([Fieldname], "SearchString")

so for instance if you were looking for the position of the string "U1" and your field name was called GLString you would use

GLPosition: getglposition([GLString], "U1")

Then you could add criteria for which glposition you were looking for (i.e. a criteria of 1 in the GLPosition field)

[Bulzie]

In a Query:
x: IIf([Modifiers] Like '*U1*',1,0)

[RobFlott]

Thank you

[kd2017]

Not sure if its applicable here but FYI in Access True=-1 and False=0.

Well... Technically anything <>0 is true but officially True=-1