By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,501 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,501 IT Pros & Developers. It's quick & easy.

Read part of the data in text field

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> 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
Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.