Instr query search with a fieldname and any six character string (1 Viewer)

[K2M]

I am looking for a instr query that searches on two fields ([Field1] and [Field2]) where in between there is a 6 character text that could be anything.

So basically it searches in a string for [Field1] + any six character string + [Field2]

Test: IIf(InStr([Field1] & ?????? & [Field2]),1,0))

Hopefully you can help me out with this.

 

[nanscombe]

I would imagine it being ...

Instr(stringToSearch, "*" & [Field1] & "??????" & [Field2]& "*")

Nope. Apparently Instr() doesn't support wildcards.

You could try this function. It takes three Parameters:

1) TheHaystack - The string you are searching
2) TheNeedle1 - The first string you are searching for
3) TheNeedle2 - The second string you are searching for

It should only return TRUE if the two search strings have 6 characters between them.

Public Function findTwoFieldsPlusSixChars(ByVal theHaystack As String, ByVal theNeedle1 As String, ByVal theNeedle2 As String)
  Dim lngN1 As Long, lngN2 As Long
  On Error Resume Next
  findTwoFieldsPlusSixChars = True

  lngN1 = InStr(theHaystack, theNeedle1)
  findTwoFieldsPlusSixChars = findTwoFieldsPlusSixChars And (lngN1 > 0)

  lngN2 = InStr(lngN1 + 1, theHaystack, theNeedle2)
  findTwoFieldsPlusSixChars = findTwoFieldsPlusSixChars And (lngN2 > 0)

  findTwoFieldsPlusSixChars = findTwoFieldsPlusSixChars And (lngN2 = lngN1 + Len(theNeedle1) + 6)
End Function