ODBC Scalar Functions

[dave h]

Hi,

Is it possible to use the CONCAT function in the following fashion?

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

Dim myDB As Database
Dim myRSin As Recordset
Dim strTest As String

Set myDB = CurrentDb
Set myRSin = myDB.OpenRecordset("SELECT fld1, {fn concat(fld1, fld2)} as
testFld FROM table1")
With myRSin
Do While Not .EOF
strTest = !testFld
Debug.Print strTest
MoveToNextRow:
.MoveNext
endOfLoop:
Loop
End With
myDB.Close
-----------------------------------------
Access reponds with a "mal formed GUID" error

My goal is to set myCbo.RowSource to an SQL string that concatenates 4
numeric fields into a string like "1.2.3.4". Is this possible to do
directly without an approach like creating an temporary table? What is the
best way to handle this problem?

Thanks, Dave H.

 

[Andreas]

Set myRSin = myDB.OpenRecordset("SELECT fld1, fld1 & fld2 as testFld
FROM table1")

 

[dave h]

Thanks, I should have known it was that easy!

BTW, the Access Help for ODBC Scalar Functions states:

"Microsoft® Jet SQL supports the use of the ODBC defined syntax for scalar
functions. For example, the query: SELECT DAILYCLOSE, DAILYCHANGE FROM
DAILYQUOTE WHERE {fn ABS(DAILYCHANGE)} > 5. Would return all rows where the
absolute value of the change in the price of a stock was greater than five.A
subset of the ODBC defined scalar functions is supported. The following
table lists the functions that are supported." And then goes on to show a
list interesting functions. Although I clearly don't need CONCAT ;-), is
there some special syntax for using these functions? Some of them look
pretty useful.

Thanks again, Dave H

 

[MGFoster]

Thanks, I should have known it was that easy!

BTW, the Access Help for ODBC Scalar Functions states:

"Microsoft® Jet SQL supports the use of the ODBC defined syntax for scalar
functions. For example, the query: SELECT DAILYCLOSE, DAILYCHANGE FROM
DAILYQUOTE WHERE {fn ABS(DAILYCHANGE)} > 5. Would return all rows where the
absolute value of the change in the price of a stock was greater than five.A
subset of the ODBC defined scalar functions is supported. The following
table lists the functions that are supported." And then goes on to show a
list interesting functions. Although I clearly don't need CONCAT ;-), is
there some special syntax for using these functions? Some of them look
pretty useful.

The ODBC scalar functions are used in SQL Pass-Through queries to ODBC
data tables.