Solved

Update "yyyy" to "2013"

Posted on 2013-01-10

Query Syntax

5 solutions

248 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"));

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

Join The Community

6 Comments

LVL 29

Assisted Solution

by:IrogSinta

IrogSinta earned 100 total points

ID: 387659522013-01-10

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"));

LVL 29

Expert Comment

by:IrogSinta

ID: 387659612013-01-10

Or did you want a query to update the dates in your table that are in 2012 to 2013?

LVL 39

Assisted Solution

by:Pratima Pharande

Pratima Pharande earned 100 total points

ID: 387659682013-01-10

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

LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]

Guy Hengel [angelIII / a3] earned 100 total points

ID: 387662922013-01-11

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 ..

LVL 61

Accepted Solution

by:mbizup

mbizup earned 100 total points

ID: 387664482013-01-11

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

LVL 32

Assisted Solution

by:awking00

awking00 earned 100 total points

ID: 387673312013-01-11

angelIII's solution should do what you want, although I think the syntax for year interval is "yyyy" -
dateadd("yyyy", 1, your_date_field)

Featured Post

Question has a verified solution.

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

Composite Queries in Oracle

Article by: Swadhin

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.

Installing SQL Server vNext on Linux: Part 2

Article by: Yashwant

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.

How to view most utilized nodes in your network?

Video by: AdRem

Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

display Triangles! and Circles! in a Microsoft Access Query -- Get Previous Record too

Video by: crystal

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…