Solved

Update "yyyy" to "2013"

Posted on 2013-01-10
6
242 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
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 142

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now