Undefined function in expression

[Iain Boyd]

I have been programming for several years in Access and have recently begun to program in VB.

In Access I can include a global function within an SQL statement to manipulate the selected input data to output a new (usually reformatted) field. For example, depending on the value of a [Member Type] field I can format the [Member Name] as [Title Description + First Names + Surname] or [Rank Description + First Names + Surname] where [First Names] and [Surname] are fields in the [Member] table and the Title and Rank Descriptions are held in separate tables.

When I try to do the same thing in VB I get the error - Undefined function 'AAA' in expression!

Is there any way in VB I can code an SQL statement to manipulate the selected input to produce a new formatted field?

[Clunietp]

Here is an example that uses the NWIND MDB file and concatenates 3 fields into one with SQL:

    Dim cn As Connection
    Dim rs As Recordset
    

    Set cn = New Connection

    cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=Nwind.mdb"
    
    Set rs = cn.Execute("Select CustomerID + ' ' + CompanyName + ' ' + ContactName as Name from Customers")
    
    MsgBox rs.Fields("Name").Value

[JHausmann]

As a matter of style, I use "&" when dealing with string concatinations, in place of "+". Both work _but_ the usage of "+" can, sometimes, have undesired effects (and this should not detract from what Tom has posted, which is correct)

[This message has been edited by JHausmann (edited 01-12-2000).]

[Clunietp]

Good point.

Yes, use '&' instead of '+' so you don't accidentally add your numeric fields together when you wanted to concatenate them.