Solved

Update "yyyy" to "2013"

Posted on 2013-01-10
6
247 Views
Last Modified: 2013-02-08
I have a Microsoft Access query where I am trying to update the "yyyy" "2012" to "2013".  Here is the Select query:
SELECT Format([MyDate],"mmyyyy") AS Expr1
FROM Company_Data
WHERE (((Format([MyDate],"mmyyyy"))="012012"));
0
Comment
Question by:donnie91910
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 100 total points
ID: 38765952
I'm not sure I understand the question.  Wouldn't  you just change it to:
SELECT Format([MyDate],"mmyyyy") AS Expr1
FROM Company_Data
WHERE (((Format([MyDate],"mmyyyy"))="012013"));
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38765961
Or did you want a query to update the dates in your table that are in 2012 to 2013?
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 100 total points
ID: 38765968
Try this

Update  Emp_Detail

set Emp_Detail.[JoiningDate] = CDate( Format(Emp_Detail.[JoiningDate], "MM/DD/") & "2013" )

where year(Emp_Detail.[JoiningDate]) = 2005



In your case for year
Update  Company_Data

set [MyDate] = CDate( Format([MyDate], "MM/DD/") & "2013" )

where year([MyDate]) = 2012
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 38766292
if the field is date, you just want to add 1 year:

dateadd("year", 1, your_date_field)

and eventually format from there.
if it's not datetime, it would be about time to think about getting that changed ..
0
 
LVL 61

Accepted Solution

by:
mbizup earned 100 total points
ID: 38766448
If your field is stored as TEXT (not optimal), you can use the following:

Select Query:
SELECT Replace([MyDate], "2012", "2013")
FROM Company_Data
WHERE Right([MyDate],4) = "2012"

Open in new window


Update query:
UPDATE Company_Data
SET [MyDate] = Replace([MyDate], "2012", "2013")
WHERE Right([MyDate],4) = "2012"

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 38767331
angelIII's solution should do what you want, although I think the syntax for year interval is "yyyy" -
dateadd("yyyy", 1, your_date_field)
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question