[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

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
0
jz1english
Asked:
jz1english
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
This function will return the number of characters before the dash:

instr(1, "Your Field Name Goes Here", "-") -1

You can change the -1 to a -2 to account for the space before the -.

Hope this helps.
-Jim
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
 
GRayLCommented:
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;
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now