Defining "Yesterday" (1 Viewer)

[brokkel]

Ok I'm a total N00b in this and I know half of the board has studied Informatics, so I'm hoping someone can help me

I'm trying to create an Microsoft Access (2003) query that will provide me everytime results from Yesterday. But I'm having some difficulties defining "yesterday".

I have tried everything I could find on the web, but keep getting error messages when running the query. It might be interresting to know that I run this query via ODBC on DB2 tables.

The query in SQL looks something like this.

SELECT T_DI_CCE_CAS_01C.CNI_RCV_ID, T_DI_CCE_CAS_01C.CAS_STU_ID INTO tempmailphone
FROM T_DI_CCE_CAS_01C
WHERE (((T_DI_CCE_CAS_01C.CNI_RCV_ID)="PHONE" Or (T_DI_CCE_CAS_01C.CNI_RCV_ID)="EMAIL") AND ((T_DI_CCE_CAS_01C.CAS_CRT_DT)=Date()-1) AND ((T_DI_CCE_CAS_01C.CAS_SRV_DS)="COMMUNICATIONS"));

In the access query view I just wrote Date()-1 (and tried other things as well) in the criteria, but to no avail

Please .. help ... me !!

When running the query, it I get:

ODBC--call failed.

[IBM][CLI Driver][DB2]SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007 (#-181)

 

[PeterF]

The error message

The string representation of a datetime value is out of range

should give you some clue.
I'm no expert on DB2 but to me it seems that you can only use string values to filter, and the date() - 1 function returns a double.
So changing it to something like:

format(date() - 1, "mm/dd/yyyy" )

should solve this problem.
Note: the error says dateTime value so maybe you need to include the time part.