Help: Find & Mid Syntax

[claudz_08]

How do i use the FIND and MID command in acces using queries.

What i'm doing is i'm trying to separate the company name from the email address.
ex. claudine@intel.com = I'm going to extract the "intel " from the email address.

i have already solved this using excel. What i did is that one coumn for email, another is for the "@", another is for the find function and another is for the MID function. The find column finds the "@" from the email address and output the number where "@" is located while the MID separates the company name from the "@".
Attached here is an example of what i did.

Now i'm trying to do this using queries in acces. Can anyone please tell me the syntax of FIND and MID command or you can also tell me the exact sql command for this one. Thanks!

 

[Mile-O]

Where strEmail is your email field or control:


=Mid(strEmail, InStr(1, strEmail, "@") + 1, (InStr(InStr(1, strEmail, "@"), strEmail, ".") - InStr(1, strEmail, "@")) - 1)

 

[claudz_08]

hey thanks! it did worked!

 

[claudz_08]

another question

What's the syntax for if then else loops.

example:

if company="R", the result would be richard
else

if company="A", the output would be alex

....how do i write this in sql command? thanks!

 

[Mile-O]

Better to make a function and then use a select case within it and call that from the query.

ie. put this code in a module

Public Function GetName(ByVal strCompany As String) As String

    Select Case strCompany
        Case Is = "A"
            GetName = "Alex"
        Case Is = "R"
            GetName = "Richard"
        Case Else
            GetName = "Unknown"
    End Select

End Function

Then in your query, make a calculated field

i.e.

UserName: GetName([Company])