asked on

access 2003: Getting month of field

Have a text field in a linked table called Action. Have another date field which sets the Action field based upon what the CAEndDate shows. The below code works if the CAEndDate is < the current date but what i need is to figure out how to set the Action field when month(CAEndDate) and Year(CAEndDate) < LastMonth.

CurrentDb.Execute "UPDATE dbo_t_Detail SET dbo_t_Detail.[Action] = 'No' WHERE (((dbo_t_Detail.CAEndDate)<" & Date & "));"

thanks

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Angelp1ay

Have a try with:

WHERE Format(tbl.date,YYYY) < Format(Now(),YYYY)
OR (Format(tbl.date,YYYY) = Format(Now(),YYYY) AND Format(tbl.date,MM) < Format(Now(),MM))

Angelp1ay

Yea... Arthur_Wood's is better... :o)

Nestorio

Try this:

CurrentDb.Execute "UPDATE dbo_t_Detail SET dbo_t_Detail.[Action] = 'No' WHERE (((dbo_t_Detail.CAEndDate)<" & DateSerial(Year(Date),Month(Date)-1,1) & "));"

Gustav Brock

Why all this trouble when DateDiff() is right at hand:

CurrentDb.Execute "UPDATE dbo_t_Detail SET dbo_t_Detail.[Action] = 'No' WHERE DateDiff('m', dbo_t_Detail.CAEndDate, Date) > 1;"

/gustav

TechMonster

ASKER

Forgot to accept this question...sorry.

TechMonster

ASKER

Thanks everyone! This site wouldn't be as great as it is without folks like you guys!

Arthur_Wood

Glad to be of assistance.

AW