Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

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

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
jjxia2001
Asked:
jjxia2001
  • 3
1 Solution
 
peter57rCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
jjxia2001Author Commented:
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
 
jjxia2001Author Commented:
After deleting the space, the query is working now!
0
 
jjxia2001Author Commented:
Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now