Solved

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

Posted on 2013-06-04
5
372 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
After deleting the space, the query is working now!
0
 

Author Closing Comment

by:jjxia2001
Comment Utility
Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

772 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

10 Experts available now in Live!

Get 1:1 Help Now