Solved

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

Posted on 2013-06-04
5
387 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
  • 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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

910 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

24 Experts available now in Live!

Get 1:1 Help Now