Solved

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

Posted on 2013-06-04
5
397 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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 viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

785 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