IIF statement to pull string from memo field (1 Viewer)

[heather001]

I have a 3rd party database where I have no control over how the data is entered. I've been given the task of creating a Crystal report that would need to gather data from two databases. The link between the two databases in my report would be on a quote number. In one database, the quote number is in it's own field. In the 3rd party database, the quote number is stored in a memo field along with other data.
Here's an example:

4000 C7875
9003267 T7761
90000167/4010/T6895
4010 T7152A
TPCA #1756/2914

The data I'm after is
C7875
T7761
T6895
T7152A
*No data from the 5th row since the data after the "T" isn't numerical

So far I'm thinking of using an IIF statement to check for the existance of a C or T, then if true, use a nested IIF statement within the first to check for numeric, if true, use the Mid function to pull out the quote number.

My first concern is this could become a complicated IIF statement and was wondering if there was another direction I should be looking in acheiving this.

My second concern is if I go with this method, some of the quote numbers have a space after them, some of them have no space after them, some may even have a "/" after them. How would I go about accomplishing this?

 

[grnzbra]

You could run an immediat if statement such as

which uses InStr() to check for a "T" or "C". If you get a hit, you would use the Right, Len, Instr and IsNumeric functions to determine if the characters to the right of the T or C are numeric.

It might not be so ugly if it were written as a function in VBA.

Something kinda like this:

Public Function KyFld(Inp As String) As String
Dim str As String
Dim pos As Integer
str = Inp
'KyFld = ""
If InStr(str, "T") <> 0 Then
pos = InStr(str, "T")
Else
If InStr(str, "C") <> 0 Then
pos = InStr(str, "C")
Else
pos = 0
End If
End If
If pos <> 0 Then
If IsNumeric(Right(str, Len(str) + 1 - pos)) Then
KyFld = Right(str, Len(str) - pos)
End If
End If

End Function

I think this will generate a null if you don't have a proper value. If that causes problems, take the apostrophy out of the fifth line. That will give an outpu of ""

 

[heather001]

Thanks for the response. I would prefer VBA since the If statement structure is much cleaner than in MSaccess code of IIF.

I'm not familiar with how I would call the function from the query. Can you point me in the right direction?

 

[RV]

Add a column to your query.
For field, enter KyFld(yourcolumname)
Replace yourcolumname by the actual name of the memofield column in your table.

RV

 

[heather001]

This helps a lot! Thanks!!

 

[heather001]

It works like a charm.
I had to make some minor changes to get it to work for my needs. I've attached the code in case someone else needs to use it for ideas

Public Function KyFld(Inp As String) As String
Dim str As String
Dim pos As Integer
Dim xpos As Integer
Dim TmpKyFld As String
str = Inp
If InStr(str, "T") <> 0 Then
pos = InStr(str, "T")
Else
If InStr(str, "C") <> 0 Then
pos = InStr(str, "C")
Else
pos = 0
End If
End If
If pos <> 0 Then
If IsNumeric(Mid(str, (pos + 1), 1)) = True Then
TmpKyFld = Mid(str, pos, Len(str) + 1 - pos)
End If
If InStr(TmpKyFld, " ") <> 0 Then
xpos = InStr(TmpKyFld, " ")
KyFld = Left(TmpKyFld, xpos)
Else
KyFld = TmpKyFld
End If
End If
End Function