Re: Access SQL Scalar Function DAYOFWEEK Problem

I don't think you can use the ODBC Scalar Function in Access since (AFAIK)
the expression {fn DAYOFWEEK(Tran_Dt)} needs to be translated / interpreted
by the JET ODBC driver for the JET engine to understand and process the ODBC
Scalar Function DAYOFWEEK. Internally, JET may have a different name for
this function altogether (which is not exposed if JET is used in Access) or
JET may pass this as function call Weekday to VBA for processing.

If you do this in Access, you should use VBA Weekday function in stead.

The {fn DAYOFWEEK(Tran_Dt)} works fine if you pass it through the ODBC
driver. I create a DSN "TestAccessViaODBC" using JET ODBC driver with
"db1.mdb" as the database. I then use the following code in another
database "db2.mdb" to test the Scalar function and the Recordset returns
correct value. Here the quick & dirty code I used to test:

********
Option Compare Database
Option Explicit

Public Sub TestAccessViaODBC()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConn As String
Dim strSQL As String

strConn = "Provider=MSDASQL.1;Persist Security Info=False;" & _
"Data Source=TestAccessViaODBC"
Set cnn = New ADODB.Connection
cnn.ConnectionString = strConn
cnn.Open
MsgBox "Cnn state: " & GetState(cnn.State)

strSQL = "SELECT {fn DAYOFWEEK(Date1)} As DOW FROM Table1"
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
Debug.Print rst.Fields("DOW").Value

On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

End Sub

Public Function GetState(intState As Integer) As String

Select Case intState
Case adStateClosed
GetState = "adStateClosed"
Case adStateOpen
GetState = "adStateOpen"
End Select

End Function
********

--
HTH
Van T. Dinh
MVP (Access)



"Derek Chen" <DerekChen@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:06030C44-514C-4552-9E76-F597309570CE@xxxxxxxxxxxxxxxx
>I am programming with Access 2003. I have a table named
> 'testTestDailyWDVolume' with a field 'TermId' as TEXT and another field
> 'Tran_Dt' as Date/Time, and I tried to use the following SQL statement to
> return the 'TermId' field, 'Tran_Dt' field, and a calculated field using
> the
> scalar function DAYOFWEEK()
>
> SELECT [TermId], [Tran_Dt], {fn DAYOFWEEK(Tran_Dt)} AS DayOfWeek
> FROM testTestDailyWDVolume;
>
> When I tried to excute it I received a "Malformed Guid. in query
> expression
> ' {fn DAYOFWEEK(Tran_Dt)} '." error. I then tried the following,
>
> SELECT [TermId], [Tran_Dt], [fn DAYOFWEEK(Tran_Dt)] AS DayOfWeek
> FROM testTestDailyWDVolume;
>
> which then prompted for a parameter (it is treating [fn
> DAYOFWEEK(Tran_Dt)]
> as a parameter and pop up a window for it).
>
> Could anyone tell me what is the correct syntax for the scalar functions?
> Here is what I found in the Access help document. I tried to follow the
> example but it didn't work.
>
>
> -----------------------------------------------
> ODBC Scalar Functions
> 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.
>
> For a description of the arguments and a complete explanation of the
> escape
> syntax for including functions in a SQL statement, see the ODBC
> documentation.
>
> String Functions
> ASCII LENGTH RTRIM
> CHAR LOCATE SPACE
> CONCAT LTRIM SUBSTRING
> LCASE RIGHT UCASE
> LEFT
>
>
> Numeric Functions
> ABS FLOOR SIN
> ATAN LOG SQRT
> CEILING POWER TAN
> COS RAND MOD
> EXP SIGN
>
>
> Time & Date Functions
> CURDATE DAYOFYEAR MONTH
> CURTIME YEAR WEEK
> NOW HOUR QUARTER
> DAYOFMONTH MINUTE MONTHNAME
> DAYOFWEEK SECOND DAYNAME
>
>
> Data Type Conversion
> CONVERT String literals can be converted to the following data types:
> SQL_FLOAT, SQL_DOUBLE, SQL_NUMERIC, SQL_INTEGER, SQL_REAL, SQL_SMALLINT,
> SQL_VARCHAR and SQL_DATETIME.
>
>
> See Also
> Configuring the Microsoft Jet Database Engine for ODBC Access
>
> -----------------------------------------------------------------
>
>


.

Relevant Pages

  • Re: [Access2003, VBA] Use DAO or ADO?
    ... One of the reasons my clients preferred Access / Jet / ODBC was that they ... Autonumber or its server equivalent and could have been shown to the users ... real world of development, Dot Net", ADO has been superceded by ADO.NET ...
    (comp.databases.ms-access)
  • Re: [Access2003, VBA] Use DAO or ADO?
    ... If you're using ODBC, you gain exactly nothing. ... You gain sql neutral code. ... Every ODBC driver usable by Jet will translate ... Because if you want a query to be pass-though then you DO NOT have to ...
    (comp.databases.ms-access)
  • Re: JDBC =?UTF-8?B?ZsO8ciBNUy1BY2Nlc3MgZ2VzdWNodA==?=
    ... finde ich immer nur den Weg über JDBC und dann ODBC. ... suche ich eine Alternative. ... "From a data access technology standpoint, Jet is considered a ... Microsoft has no plans to natively support Jet under ...
    (de.comp.lang.java)
  • Re: [Access2003, VBA] Use DAO or ADO?
    ... If you're using ODBC, you gain exactly nothing. ... You gain sql neutral code. ... Every ODBC driver usable by Jet will ... Because if you want a query to be pass-though then you DO NOT have ...
    (comp.databases.ms-access)
  • Re: Access VBA too slow and the WEEKDAY() function
    ... The ODBC Scalar function will be slower since it has to go through the JET ... > Set DlyDmdRD = New ADODB.Recordset ...
    (microsoft.public.access.modulesdaovba)
Loading