Access Data Type Mismatch on my SQL Code

0
votes

I have a query that pulls the phone extension and the full name of a person from my phone system import, and splits the full name into a First and Last name. It is working great for spliting the name up but if I try to sort by either name I get a Data Tyep Mismatch Error.

Here is the SQL Code.

SELECT    ExportG3R.Extension,
          Left([ExportG3R.name],InStr([ExportG3R.name],",")-1) AS LastName,
          Trim(Mid([ExportG3R.name],InStr([ExportG3R.name],",")+1,Len([ExportG3R.name])-InStr([ExportG3R.name],","))) AS FirstName
FROM ExportG3R
ORDER BY ExportG3R.Extension;

Any ideas on how to get this working?

2
sqlms-accessms-access-2007
Are you sorting on LastName, FirstName or on Left([ExportG3R.name],InStr([ExportG3R.name],",")-1), Trim(Mid([ExportG3R.name],InStr([ExportG3R.name],",")+1,Len([ExportG3R.name])-InStr([ExportG3R.name],",")))? You can't use aliases in the ORDER BY, so you have to repeat the calculation. If you used the Access QBE to construct your query, it would take care of this for you. If you're doing it right, it could be that you have Nulls in there somewhere that are not a problem in the SELECT but cause problems in the ORDER BY. - David-W-Fenton

2 Answers

2
votes

I created a ExportG3R table with text fields for Extension and name. Your query worked with name values like "Litzner, Mike". However, a name value which doesn't include a comma, such as "Litzner Mike", gave me #Error for LastName and "Litzner Mike" for FirstName. And if name is Null, it gives me #Error for both. Neither of those was a deal-breaker for SELECT until I tried to sort on LastName and/or FirstName. When attempting those sorts, the Access 2003 db engine responded "Invalid procedure call", which is not the same error message you're getting.

So, although I'm not certain my situation exactly matches yours, I'll suggest you try your query with a WHERE clause to return only rows which have non-Null name values and also include a comma.

SELECT
    e.Extension,
    Left(e.[name],InStr(e.[name],",")-1) AS LastName,
    Trim(Mid(e.[name],InStr(e.[name],",")+1,Len(e.[name])-InStr(e.[name],","))) AS FirstName
FROM ExportG3R AS e
WHERE e.name Like "*,*"
ORDER BY ORDER BY 2,3;

Another concern is that name is a reserved word. When creating that table in design view with Access 2007, gave me a warning about that field name. Try creating a throw-away table in Design View and read the help it offers when it gives you a warning about name. If at all possible, change it to something which isn't a reserved word ... FullName perhaps.

Finally I think this would be simpler if the name field were split into two fields in the table itself. Storing names as "Last, First" combines 2 attributes in one field. So when you need either of them, you must split them out. It's easier to store them separately, then concatenate them whenever you need them as "Last, First":

SELECT LastName & ", " & FirstName AS FullName
0
votes

you could wrap it in a sub query;

SELECT extension,lastname,firstname
FROM (
    SELECT ExportG3R.Extension, 
    Left([ExportG3R.name],InStr([ExportG3R.name],",")-1) AS LastName,                       
    Trim(Mid([ExportG3R.name],InStr([ExportG3R.name],",")+1, 
        Len([ExportG3R.name])-InStr([ExportG3R.name],","))) AS FirstName
    FROM ExportG3R
) order by firstname