Update query help needed

[air0jmb]

I have a table that captures details about events, tblEvents. A field 'txtDesc' has a description of the event. Within the description is an aircraft tail number. The tail is always 4 digits - the first is "N", and the 3 subsequent characters are numbers between 100 and 999.

I've created a field called Tail and would like to run an update query to update it to the tail number which resides in the field EventDesc.

Is that possible? If so, what would be the expression to use? And what if more than one tail number is in that field? For example, "...volume moved from N301 to N334" If that's a problem, I could except the first occurance.

Thanks in advance for any assistance!!

Mike

 

[AlexCuse]

How is the tail number stored in your field EventDesc?

A wise man once said
"The only thing normal about database guys is their tables".

 

[air0jmb]

The tail is always 4 digits - the first is "N", and the 3 subsequent characters are numbers between 100 and 999.

Example 1: 0597/01 SDF-ABQ N301 (RT T/R inop).

Example 2: 8211/02 MHR-BOS N921 (hyd qty erratic); volume swapped to N807.

Mike

 

[Remou]

How about:

Dim strTest, astrTest, strTail
strTest = "8211/02 MHR-BOS N921 (hyd qty erratic); volume swapped to N807."
'strTest = "0597/01 SDF-ABQ N301 (RT T/R inop)."

astrTest = Split(Replace(strTest, ".", ""), " ")
For i = 0 To UBound(astrTest)
    If astrTest(i) Like "N###" Then
        strTail = astrTest(i)
    End If
Next
Debug.Print strTail

 

[air0jmb]

Remou,

Thanks for the response, but I was hoping for an expression that I could use in the 'Update To' field in an update query.

Mike

 

[lespaul]

you can use this code in an update query. in a module create a function called GetTailNumber and then you can use it in a query.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi