Solved

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

Posted on 2005-05-16
317 Views
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
Question by:jz1english

LVL 65

Accepted Solution

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

LVL 33

Expert Comment

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

LVL 33

Expert Comment

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

LVL 44

Expert Comment

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…