Hello,
In our DB an employee's supervisor's name is entered into one field ex. "Bob Smith" (it was requested to be set up this way). Now I need to format the names in the query to "Last, First". in my query I have:
Space: InStr([supervisorname]," ") to find the space between the FN and LN
Length: Trim(Len([SupervisorName])) to find out the length of the field
First Name: Trim(Left([SupervisorName],InStr([SupervisorName]," "))) finds the first name
the issue is when I try to get the last name, I have it coded as:
Last Name: Trim(Right([SupervisorName],([Length]-[Space])))
When I run the query it prompts me for the value of the Length and Space. If I take the [Last Name] part out of the query it runs fine and displays the correct values for Length and Space. I didn't know if there is something I am missing or what is going on. I even tried calculating Length - Space in a separate field but I still get the same thing.
Thanks,
Will