jz1english
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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(
i= len(strTemp)
If i>0 then
strTemp=Choose(i,"One", "Two", "Three", "Four")
else
strTemp="none"
End if
fnNoOfChar=strTemp
End Function
mike
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;
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;
Dim i as integer
Dim strTemp as Stirng
strTemp =Trim(Left(MyString,Instr(
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