Date

[easternfish]

HI,

I need my data to open with yesterday's date.

XDATE format is yyyymmdd

how can i make xdate = today -1

DATE(XDATE) = DATE(today(),'yyyymmdd') -1

thanks

[oscar_ortiz]

Try this

DATE(today()-1,'yyyymmdd')

[easternfish]

thanks that fixed 1 problem that i had but i'm still getting errors.

here's what i got

SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'DATE#(XDATE) = DATE(today()-1,'yyyyMMdd')'.
SQL SELECT *
FROM `XFILE`
WHERE DATE#(XDATE) = DATE(today()-1,'yyyyMMdd')

this works :

where XDATE = 20101215;

[oscar_ortiz]

Ok, try this:

Create a variable first, then use the variable in your where clause.

Let vYesterday = Date(Today()-1, 'YYYYMMDD');

Now in your where clause use

WHERE DATE#(XDATE) = $(vYesterday)

[easternfish]

Great Thanks

this works!

Let vYesterday = Date(Today()-1, 'YYYYMMDD');

WHERE XDATE = $(vYesterday);

[oscar_ortiz]

No problem...

As a side note, if you needed to have the variable equal to a string value as in:

where XDATE = '20101215'

You could use the variable wrapped in single quotes...

where XDATE = '$(vYesterday)'