Read part of the data in text field

[Shelley]

Hi everyone,

I am searching a way to read part of the data in the text field in a
form. As the length of the field is dynamic, I can't use the left or
right function. I need to read the first 2 words from the specific
control like:

Get "January 2005" from "January 2005 final", or "January 2005 test
samples", what I need is the first two words. What function I can use?

Any help will be greatly appreciated!

Thanks in advance!

Shelley

[deko]

> I am searching a way to read part of the data in the text field in a

form. As the length of the field is dynamic, I can't use the left or
right function. I need to read the first 2 words from the specific
control like:

Get "January 2005" from "January 2005 final", or "January 2005 test
samples", what I need is the first two words. What function I can use?

This is a job for Regex. Though not really an Access function, this should
work on 2000/XP machines (the joker in the pack is the version of the
VBScript engine). This code will get you started but you'll need to brush
up on Regex syntax to match the text you're looking for.

Public Function MatchString(strStringToMatch As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strNeedle As String
Dim strHaystack As String
Dim objRgx As Object
Set db = CurrentDb
Set objRgx = CreateObject("VBScript.RegExp")
Set rst = db.OpenRecordset("qrySelectFieldsToSearch")
strNeedle = LCase(strStringToMatch)
Do While Not rst.EOF
strHaystack = LCase(GetSomeString(rst!Haystack))
objRgx.Pattern = "\b" & strNeedle
If objRgx.Test(strHaystack) Then
[do whatever]
Exit Do
End If
End If
rst.MoveNext
Loop
Set rst = Nothing
Set db = Nothing
Set objRgx = Nothing
End Function

[fredg]

On 21 Feb 2005 09:43:58 -0800, Shelley wrote:

Hi everyone,

I am searching a way to read part of the data in the text field in a
form. As the length of the field is dynamic, I can't use the left or
right function. I need to read the first 2 words from the specific
control like:

Get "January 2005" from "January 2005 final", or "January 2005 test
samples", what I need is the first two words. What function I can use?

Any help will be greatly appreciated!

Thanks in advance!

Shelley

Is the second word always 4 characters (2004, 2005, etc.)?

Left([CombinedNames],(InStr([CombinedNames]," "))) &
Mid([CombinedNames],InStr([CombinedNames]," ")+1,4)

If the second word length varies:
Copy and paste the below code to a Module:

Public Function FindString(OldString) As String
' find the first 2 words within a string

Dim intX As Integer
Dim intY As Integer

intX = InStr(1, OldString, " ")
intY = InStr(intX + 1, OldString, " ")
FindString = Left(OldString, intY - 1)

End Function
==============
Call it from a query:
Exp:FindString([CombinedNames])
Or...
In an unbound control in a form or report:
= FindString([CombinedNames])

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.