Probable an easy select problem[RESOLVED]

Morning All,
I have the following sql statement which although is selecting a few fields it is still relativley simple

Code:

---

    Me.SqlSelectCommand1.CommandText = "SELECT Quotations.[Enquiry Number], Quotations.Price, Quotations.NeworRecon AS Co" & _
        "ndition, Quotations.QuoteValidity, Quotations.InstallationPeriod, Quotations.Ext" & _
        "ension, Quotations.StairliftType, Quotations.FirstName, Quotations.Surname, Quot" & _
        "ations.QuoteRequested, Quotations.QuoteSent, Appointments.TITLE, Appointments.IN" & _
        "ITIAL, Appointments.NAME, Appointments.ROAD, Appointments.VILLAGE, Appointments." & _
        "TOWN, Appointments.COUNTY, Appointments.POSTCODE, Appointments.[ENQUIRY  NUMBER]" & _
        ", Quotations.ID, Quotations.Quantity, Quotations.Make AS Make, Quotations.Model," & _
        " Quotations.Platform AS Platform, Quotations.Automatic AS Automatic, Quotations." & _
        "Hinge AS Hinge, Quotations.Joint AS Joint, Appointments.TITLE & ' ' & Appointments.NAME  FROM Quotations INNER JOIN Appointments ON Quotations.[E" & _
        "nquiry Number] = Appointments.[ENQUIRY  NUMBER] WHERE (Quotations.QuoteRequested" & _
        " = 1) AND (Quotations.QuoteSent = 0)"

---

It is because i inserted the firstname & ' ' & surname line that i am getting the error "Operator equals boolean AND, type equals varchar at execute reader" ???
Does anyone know what is going wrong in this pls?

Quote:

---

Originally Posted by FishGuy

Code:

---

SELECT
    Quotations.[Enquiry Number],
    Quotations.[Price],
    Quotations.[NeworRecon] AS [Condition],
    Quotations.[QuoteValidity],
    Quotations.[InstallationPeriod],
    Quotations.[Extension],
    Quotations.[StairliftType],
    Quotations.[FirstName],
    Quotations.[Surname],
    Quotations.[QuoteRequested],
    Quotations.[QuoteSent],
    Appointments.[TITLE],
    Appointments.[INITIAL],
    Appointments.[NAME],
    Appointments.[ROAD],
    Appointments.[VILLAGE],
    Appointments.[TOWN],
    Appointments.[COUNTY],
    Appointments.[POSTCODE],
    Appointments.[ENQUIRY  NUMBER],
    Quotations.[ID],
    Quotations.[Quantity],
    Quotations.[Make],
    Quotations.[Model],
    Quotations.[Platform],
    Quotations.[Automatic],
    Quotations.[Hinge],
    Quotations.[Joint],
    Appointments.TITLE & ' ' & Appointments.NAME As FullName

FROM
    Quotations INNER JOIN Appointments ON Quotations.[Enquiry Number] = Appointments.[ENQUIRY  NUMBER]
WHERE
  (Quotations.QuoteRequested = 1) AND
  (Quotations.QuoteSent = 0)

---

---

I'd break up your sql statement over several lines... Also Appointmantes.[Enquiry Number] << should that have an extra space - why are you putting spaces in table names??!!!

Code:

---

strSql = "SELECT Quotations.[Enquiry Number], Quotations.[Price], Quotations.[NeworRecon] AS [Condition], "
strSql=strSql & "Quotations.[QuoteValidity], Quotations.[InstallationPeriod], Quotations.[Extension], "
strSql=strSql & "Quotations.[StairliftType], Quotations.[FirstName], Quotations.[Surname], "
strSql=strSql & "Quotations.[QuoteRequested], Quotations.[QuoteSent], Appointments.[TITLE], Appointments.[INITIAL], Appointments.[NAME], "
strSql=strSql & "Appointments.[ROAD], Appointments.[VILLAGE], Appointments.[TOWN], Appointments.[COUNTY], Appointments.[POSTCODE], "
strSql=strSql & "Appointments.[ENQUIRY  NUMBER], Quotations.[ID], Quotations.[Quantity], Quotations.[Make], Quotations.[Model], Quotations.[Platform], "
strSql=strSql & "Quotations.[Automatic], Quotations.[Hinge], Quotations.[Joint], Appointments.TITLE & ' ' & Appointments.NAME As [FullName]"
strSql=strSql & "FROM Quotations INNER JOIN Appointments ON Quotations.[Enquiry Number] = Appointments.[ENQUIRY  NUMBER] "
strSql=strSql & "WHERE (Quotations.QuoteRequested = 1) AND (Quotations.QuoteSent = 0)"

Me.SqlSelectCommand1.CommandText = strSql

---

You also have a field named name <<< Name is a function in most dbs, up to you if you want to change it, just be aware of that

I am working with the tables I am given they have been in the company for years so its not really viable to change the table names. The same goes for the name field but touch wood we havent had any problems as yet. I am still getting the same error message even when I try to concatenate two different fields.

This seems to work using + instead of &

Code:

---

Quotations.FirstName  + ' ' + Quotations.Surname as AgentName

---

no both should be work (+/&)