asked on

Create field that will return number based on characters before the dash

Hello,

I am trying to modify a query in access. I have a field that has a certain number of letters, a space and then a dash. For example:

a -
aa -
aaa -
aaaa -

What I want to do is create a field that will return a one, two, three or four based on how many characters are before the space and the dash. For 'a -' I would want a 1 return. For 'aa -', a two, etc.

Thanks,
John

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

If this is in code window, do:

Dim i as integer
Dim strTemp as Stirng

strTemp =Trim(Left(MyString,Instr(MyString, "-")-1))
i= len(strTemp)
If i>0 then
Msgbox Choose(i,"One", "Two", "Three", "Four")
else
Msgbox "none"
End if

If it is in a query, add a function call to handle it.

mike

Mike Eghtebas

Select Field1, Field2, fnNoOfChar(Field1) As NoOfCharacters From Table1

In a module, under module tab, paste:

Public Function fnNoOfChar(FieldVar As String) As String
'^-- contains values like a - 123, aa- zyz, etc

Dim i as integer
Dim strTemp as Stirng

strTemp =Trim(Left(MyString,Instr(MyString, "-")-1))
i= len(strTemp)
If i>0 then
strTemp=Choose(i,"One", "Two", "Three", "Four")
else
strTemp="none"
End if

fnNoOfChar=strTemp

End Function

mike

GRayL

Select myFld, instr(myFld," -")-1 as ChB4Dash from myTable;

by searching for " -", instr() will give the location of the space, and -1 will be the location of the last chararcter, provided there are no leading spaces. Note all previous solutions searched fro just "-". If there are ever leading spaces, change the query to:

Select myFld, instr(Trim(myFld)," -")-1 as ChB4Dash from myTable;