Solved

Update "yyyy" to "2013"

Posted on 2013-01-10
6
241 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 31

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

16 Experts available now in Live!

Get 1:1 Help Now