Code to perform trim, len function and then get results from query

[jw5107]

I am trying to build a query to do a trim function from the right. The table I am working with has over 44,000 records and the field I want to do the trim on has several different character varitons ending in the value.
Any example as to how I can put in a module:
- code that will trim from the right
- an if statement somehow that will look for "YES" at the end, "NO" at the end, "Y" at the end, "N", etc....
EXAMPLES: SCHEDULEDREMOVALYN-Y ---- to result "SCHD"
SCHEDULEDREMOVALYN-N ---- to result "UNS"
SCHEDULEDREMOVALYN- ---- to result "SCHD"
SCHEDULEDREMOVALYN-NO --- to result "UNS"
SCHEDULEDREMOVALYN-YES -- to result "SCHD"
Then somehow call the module in the query to show result per record or for each aircraft part removed.
What I am trying to do is clean up this field and get a result all at once instead of building 5 more action querys to get the data to work for me.

Any killer moves out there??
Thanks a million in advance!!
jw

 

[PHV]

Something like this (ac2k or above) ?
Public Function getSchedule(strSchedule As Variant) As Variant
Dim tmpArr
If Trim(strSchedule & "") = "" Then Exit Function
tmpArr = Split(strSchedule, "-")
If UBound(tmpArr) < 1 Then
getSchedule = "?"
ElseIf UCase(Left(tmpArr(UBound(tmpArr)), 1)) = "N" Then
getSchedule = "UNS"
Else
getSchedule = "SCHD"
End If
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jw5107]

PHV,

How do I call this from a select query??
This is a very old post - but I'm revisting this...

Thanks in advance!!
jw

 

[KMKelly]

Based on how this is set up, it looks like you just put it in a field in the query designer top row as

NameField: getSchedule([name of field with text string])

where the NameField is whatever you want to call this new calculated column and the name of field with text string is the table column with the "SCHEDULEDREMOVALYN-N" information in it.

Kris

 

[jw5107]

KMKelly,

Thank you very much!!

I figured out myself!!
Thanks,
jw