This document provides information to assist in translating queries which fail to translate.
These issues are constantly under investigation, a number of which will be resolved as we aim to
push our 98% translation accuracy to 100% in all cases.


-----------------------------------------------------------------------------------------
Error : 	Invalid object name 'xxxxx'.

Explained :	If the query 'xxxxx' fails to convert, then dependent queries will give this error.

Fix :		Resolve issues in dependent query.

-----------------------------------------------------------------------------------------

Error : 	Each GROUP BY expression must contain at least one column that is not an outer reference.

Explained :	This error message is often missleading and indicates that in a group by you have either,
		a string, or numeric for example LTRIM('Company Name') or 1/20. These are not allowed
		in SQL Server. Note you can still leave them in your select statements.

Remove the string or calculation from the group by.

-----------------------------------------------------------------------------------------

Error : 	The column 'Purchase date' was specified multiple times for 'Nested1'.

Explained :	When MUST builds nested queries it sometimes adds in a field more than once if the
		original SQL had a select *; for example Customers.* and [Turnover]. 

Fix :		Remove the additional field.

-----------------------------------------------------------------------------------------

Error : 	Operand data type varchar is invalid for multiply operator.
		String variables can not be used in arithmetic without first converting the data.

Explained :	This is due to stricter data type operations in SQL Server.

Fix :		In this situation you need to add CONVERT functions such as CONVERT(INT,[CharFieldName]) before
		performing the calculation

------------------------------------------------------------------------------------------

Error : 	ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Explained :	A Typical example would be to have an Order By [Lastname] + ' ' + [FirstName], but this does
		not form part of the select. here we would add the field 
		[Lastname] + ' ' + [FirstName] As Orderedname
		to the select.

Fix :		Add all order by expressions to SELECT statement.

---------------------------------------------------------------------------------------------

Error :		Incorrect syntax near '<'.

Explained :	This error can occur with CASE statements in having clauses.

		MUST generates the following 

		=1)<>0
		=1) Is Null)

		When this should be

		)<>0
		) Is Null)

Explained :	This is due to an over correction within the tool

Fix :		Manual edit of SQL in tool to remove =1

---------------------------------------------------------------------------------------------


Error : 	Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.
		Operand data type sql_variant is invalid for sum operator.
		Or similar errors.

Explained : 	MUST will translate an existing custom VBA Function into a new empty stub function in SQL Server, if
		in Access you have not specified a data type returned by the function then this will be SQL_Variant. If
		the function is then used in for example arithmetic then it will fail to convert.

Fix :		Either change the return data type from your function to for example INT, or add a CONVERT(INT,dbo.Function()) before
		performing the arithmetic.

-----------------------------------------------------------------------------------------------------------------



