Access SQL DAYOFWEEK() problem

G

Guest

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

-----------------------------------------------------------------
 
D

Duane Hookom

Please don't post the same question to multiple groups. Have you tried using
the Weekday() function?
 
G

Guest

I tried Weekday(). It did not work either. Plus, Weekday() is a VBA function
not a SQL function

Duane Hookom said:
Please don't post the same question to multiple groups. Have you tried using
the Weekday() function?

--
Duane Hookom
MS Access MVP


Derek Chen said:
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
Click to expand...
Click to expand...
 
D

Duane Hookom

Apparently your application is not written in Access?

--
Duane Hookom
MS Access MVP


Derek Chen said:
I tried Weekday(). It did not work either. Plus, Weekday() is a VBA
function
not a SQL function

Duane Hookom said:
Please don't post the same question to multiple groups. Have you tried
using
the Weekday() function?

--
Duane Hookom
MS Access MVP


Derek Chen said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access SQL Scalar Function DAYOFWEEK Problem 1
Access vs SQL Date Weirdness = Incompatibility? 11

Top