Creating a username field

[artemetis]

Hi. I have a table with firstName & lastName. I need to run an sql to create a userName. I want the username to be the first initial of firstName and first seven letters of lastName.

I have this so far:

username: LCase(Left([firstName],1) & "" & (Left([lastname],7)))

but am running into a small problem.

Some of the last names are shorter than 7 characters which is ok, but these "shorter names" have trailing spaces and characters such as " / " and other words following. I need to be able to trim anything to the right of these characters and am stuck here.

[Mariostg]

functions like Trim() and Replace() may help.
But to be clear, you might want to show us real exemples of what you get vice what you want.

[artemetis]

firstName: jack
lastName: jones / group1
username: jjones /

desired username: jjones

I'm trying to have my qry return "jjones".
In the instance where the lastName is less than eight characters, it is including the first blank space and the backslash.

[Mariostg]

Function MakeUserName(firstName As String, lastname As String) As String
    Dim username As String
    lastname = Trim(Split(lastname, "/")(0))
    username = LCase(Left([firstName], 1) & (Left([lastname], 7)))
    MakeUserName = username
End Function

Output:

?MakeUserName("john", " jones / group1")
jjones

[artemetis]

Thank you Mariostg!
Is there a way to use this in my qry or must I use a form?

Thank youa again

[Mariostg]

It would be best to place this function inside a module.
Then you can call the function from your your query:

username: MakeUserName([lastname],[firstname])

It gives you much more control and makes things cleaner.