• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

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

0
TechMonster
Asked:
TechMonster
  • 2
  • 2
  • 2
  • +2
1 Solution
 
Arthur_WoodCommented:
CurrentDb.Execute "UPDATE dbo_t_Detail SET dbo_t_Detail.[Action] = 'No' WHERE FORMAT(dbo_t_Detail.CAEndDate, 'YYYYMM')<" & FORMAT(Date, 'YYYYMM')  & ");"

AW

0
 
Angelp1ayCommented:
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))
0
 
Angelp1ayCommented:
Yea... Arthur_Wood's is better... :o)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
NestorioCommented:
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)  & "));"
0
 
Gustav BrockCIOCommented:
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
0
 
TechMonsterAuthor Commented:
Forgot to accept this question...sorry.
0
 
TechMonsterAuthor Commented:
Thanks everyone!  This site wouldn't be as great as it is without folks like you guys!
0
 
Arthur_WoodCommented:
Glad to be of assistance.

AW
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now