Update null field based on certain data in comment field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JCarter - 05 May 2005 18:59 GMT

Here's the situation. I have a table that has an ID field as well as a
Comment field. Now all of the ID fields are valued except 200, of these 200
null fields, the ID I want is sitting in the Comment Field, but it's not in
the same location in every comment.

How would I tell the criteria to update the null fields with the ID sitting
somewhere in the Comment field?

Here's an example. The ID I want may be T12345678. There's always an Alpha
character, followed by 8 numerics that make up the ID. So where ever the ID
is sitting in the comment statement, I'd want to update the empty field with
the T12345678.

Thank you in advance for any and all assistance! :o)

Signature

JCarter
Still Learning

Reply to this Message

John Vinson - 06 May 2005 06:33 GMT

>Here's the situation. I have a table that has an ID field as well as a
>Comment field. Now all of the ID fields are valued except 200, of these 200
[quoted text clipped - 10 lines]
>
>Thank you in advance for any and all assistance! :o)

Ow. That's not going to be too easy! The InStr function can find a
substring within your memo field, but not using wildcards.

You may need to do a Google search for "Regular Expressions" - dump
these memo fields out to a text file, and use an editor which can
search for regular expression substrings (such as A######## or
whatever the syntax would be). UltraEdit is one such editor.

Someone may have implemented Regular Expressions in Access but I don't
have a link. Anyone?

John W. Vinson[MVP]

Reply to this Message

'69 Camaro - 06 May 2005 17:29 GMT

Hi.

> Someone may have implemented Regular Expressions in Access but I don't
> have a link. Anyone?

Yes. The VBScript Regular Expression engine can be used. Create a public
user-defined function that finds the pattern using the RegExp object. Use
this function in the update query. For example syntax, please see the
"Microsoft Beefs Up VBScript with Regular Expressions" tutorial on the
following Web page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnclinic/html/s
cripting051099.asp

More example syntax:

http://groups.google.es/groups?hl=en&lr=&th=1a47084c4f79b64c&rnum=28

If this is a one-time event, set a reference to the Microsoft VBScript
Regular Expressions 5.5 library and remove it from the VBA Project when
finished. If this is something that may be used occasionally, then
instantiate a VBScript object in VBA and use that so that there will be less
of a chance of missing references in this database in the future.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are Ripley@CASInternet.Net and scott@ripleysoftware.com

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

> >Here's the situation. I have a table that has an ID field as well as a
> >Comment field. Now all of the ID fields are valued except 200, of these 200
[quoted text clipped - 23 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 07 May 2005 00:39 GMT

>Yes. The VBScript Regular Expression engine can be used.

Thanks Gunny! That's *very* useful information; saved and noted!

John W. Vinson[MVP]

Reply to this Message