UPPER and to_char functions not defined

I'm trying to make this query in MS Access:

   SELECT *  FROM Clients
   WHERE UPPER(name) = 'Theil' 
   AND   To_Char(date_test,'MM')= 08 
   AND  To_Char(date_test, 'yyyy')=2007;

but it says UPPER is not defined, after removing upper and leaving just name = 'Theil' it now says thar To_Char is not defined. My question then is, can I use to_char and UPPER into a where clause?

If it is not possible, how ca开发者_如何学编程n I select all from clients where certain date is equals to something? thanks.

Those are Oracle functions. The Access equivalent of to_char is datepart:

select datepart(yyyy, datetest)

And upper is matched by ucase:

select ucase(name)

In access sql you can use ucase(str), month(date_test), and year(date_test)

http://www.techonthenet.com/access/functions/

Suggested translation:

SELECT *
  FROM Clients
 WHERE UCASE(name) = 'THEIL' 
       AND FORMAT(date_test,'MM') = '08' 
       AND FORMAT(date_test, 'yyyy') = '2007';

Note the quotes around the text literal values. Also, I've changed the literal value 'THEIL' to upper case because it makes more sense :) (however, you may find you are using a case insensitive collation anyhow).

However, in general I think it is best to use temporal functionality with temporal data:

SELECT *
  FROM Clients
 WHERE UCASE(name) = 'THEIL' 
       AND DATEPART('M', date_test) = 8 
       AND DATEPART('YYYY', date_test) = 2007;

Note the same values are now literals of type SMALLINT.

Since you want rows with date_test values from August 2007, you can use literal date values in your WHERE clause to indicate the appropriate range of dates. This method will allow the db engine to take advantage of an index on date_test, so could be much faster than the suggestions using functions such as Format() or DatePart().

SELECT *
FROM Clients
WHERE
    UCase([name]) = 'THEIL' 
    AND date_test >= #2007/08/01#
    AND date_test < #2007/09/01#;

I enclosed name with square brackets because it is a reserved word.