-
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?
-
Here is an example that uses the NWIND MDB file and concatenates 3 fields into one with SQL:
Code:
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
-
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).]
-
Good point.
Yes, use '&' instead of '+' so you don't accidentally add your numeric fields together when you wanted to concatenate them.
