Access to SQL problems.

[Aaron Bertrand - MVP]

You're using & ... concatenation is handled with + in SQL Server.



"Matt Smith" <_matt@breathemail.net> wrote in message
news:bf0vr4$a77$1@sparta.btinternet.com...

> In putting my app on line I'm trying to upgrade my database to MS SQL
> Server.
> My host has converted my data to SQL (after much effort God bless 'em) but
> I'm having a spot of bother with one particular query (which some of you

may

> have spotted Aaron Bertrand helping me with last week).
>
> My statement is as follows:
> "SELECT ProductData.DacNumber, "& _ "ProductData.ProdName & ' ' &
> ProductData.SuppliersCatalogueNumber AS ProdName, ProductData.ProdPrice,
> tblSubCatList.strSubCategory
> FROM ProductData
> LEFT JOIN tblSubCatList ON ProductData.SubCategory=tblSubCatList.iSubCatID
> WHERE (((ProductData.SupplierIndex)=" & i_SupplierIndex & "))
> ORDER BY ProductData.SubCategory, ProductData.ProdName;"
>
> In my Access version the fields definitions are:
> ProductData.
> DacNumber: Long Integer, Primary Key
> ProdName: Text (100)
> SuppliersCatalogueNumber: Text (26)
> ProdPrice: Currency
> SubCategory: Long Integer
> SupplierIndex: Long Integer
>
> tblSubCatList.
> iSubCatID: Long Integer, Primary Key
> strSubCategory: Text(35)
> SupplierIndex: Long Integer
>
> The field SupplierIndex in both instances refers to table
> SupplierList
> SupplierIndex: Long Integer Primary Key
>
> As to what these fields became in SQL, I do not know, but can reasonably

(i

> hope) assume that Text would become nvarchar and Long Integer would become
> int (at the very least).
>
> The error is this:
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid operator for data
> type. Operator equals boolean AND, type equals nvarchar.
>
> /newlook/browse/products.asp, line 42
>
> Line 42 being the ADODB.Connection.Execute call with the earlier shown SQL
> statement.
>
> ASP FAQ 3.0 suggests:
>
> #2195 What are the limitations of Access?
>
> #2080 What are reserved Access, ODBC and T-SQL keywords?
>
> #2086 Why do I get 'Syntax Error in INSERT INTO Statement' with Access?
>
> #2400 Why do I get 80040E14 errors?
>
> #2061 Why do I get 'Argument data type text is invalid for argument

[...]'?

>
> #2394 Can I use the NZ() function without getting 80040E14 errors?
>
>
> but none of these appear to relate to my error.
>
> Anyone got any suggestions?
>
> Cheers
>
> Matt
>
>
>

[Matt Smith]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:OgiRZhtSDHA.2152@TK2MSFTNGP12.phx.gbl...

> You're using & ... concatenation is handled with + in SQL Server.
>

Changing to use '+' has led to blank field returns where there is no
ProductData.SuppliersCatalogueNumber.
What am I missing?

I used to use '+' for concatenation but found that I couldn't combine text
with numeric fields 'cos Access tried to add them up. Not good. Is this
likely to happen with SQL Server too?

Matt