Solved

Need help to figure out why an update query in Access does not work

Posted on 2013-06-04
5
421 Views
Last Modified: 2013-06-04
I don't know why my following update query doesn't work.  I attach my access database with the data table and the query.  Thank!


UPDATE MAFs_20_archive AS L INNER JOIN MAFs_20_archive AS H ON (L.State=H.State) AND (L.Segment=H.Segment) AND (L.Favorability=H.Favorability) AND (L.Tier=H.Tier) SET L.Exp_Date = H.Eff_Date-1, L.Row_Expir_Date = Null
WHERE (((L.Row_Expir_Date) Is Not Null) AND ((L.Eff_Date)<H.Eff_Date) AND ((H.Eff_Date)=DMin("Eff_Date","MAFs_20_archive","State=' " & L.State & " ' And Segment=' " & L.Segment & " ' AND Favorability=' " & L.Favorability & " ' AND Tier=' " & L.Tier & " ' AND Eff_Date>#" & L.Eff_Date & "#")));
test.mdb
0
Comment
Question by:jjxia2001
[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
  • 3
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39219540
You will have to explain what is not working - what are you trying to do that your query is not doing, and what are you expecting the result to be .
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39219640
could it be that you have a space in between single quote and double quote in your sql statement


AND ((H.Eff_Date)=DMin("Eff_Date","MAFs_20_archive","State=' " & L.State & " ' And Segment=' " & L.Segment & " ' AND Favorability=' " & L.Favorability & " ' AND Tier=' " & L.Tier & " ' AND
0
 

Author Comment

by:jjxia2001
ID: 39219683
My purpose is that for the same record (same combined state, segment, favorability and tier), the expired date for the older effective date will be one day earlier then the newer effective date.  This query worked before.

Here is an example:
State      Segment      Favorability      Tier
NJ      Garage      Favorable                      2

current:
Eff_Date                      Exp_Date
6/15/2013      12/31/2080
6/16/2013      12/31/2080

After run query:      
Eff_Date                       Exp_Date
6/15/2013      6/15/2013
6/16/2013      12/31/2080
0
 

Author Comment

by:jjxia2001
ID: 39219700
After deleting the space, the query is working now!
0
 

Author Closing Comment

by:jjxia2001
ID: 39219701
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

724 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