Update Query Error

  • Thread starter Thread starter Sysdupe123 via AccessMonster.com
  • Start date Start date
S

Sysdupe123 via AccessMonster.com

I've been agonizing over this all day! This is the SQL for the query that I
built:

UPDATE [Compl P129 A3C] INNER JOIN MasterFundSch ON [Compl P129 A3C].
LOANNUMBER = MasterFundSch.[Loan Number] SET MasterFundSch.P129OldInv =
[OLDINVNUMBER], MasterFundSch.P129OldCat = [OLDCATNUMBER], MasterFundSch.
P129NewInv = [Compl P129 A3C]!NEWINVNUMBER, MasterFundSch.P129NewCat = [Compl
P129 A3C]!NEWCATNUMBER, MasterFundSch.P129PrinBal = [PRINBAL], MasterFundSch.
P129PrinTran = [Compl P129 A3C]!PRINTRANS, MasterFundSch.P129Diff = [PRINBAL]-
[PRINTRANS], MasterFundSch.P129RptDt = [Compl P129 A3C]!RPTDATE,
MasterFundSch.P129ActionCd = [Compl P129 A3C]!ACTIONCODE, MasterFundSch.
P129DueDt = [Compl P129 A3C]!DUEDATE, MasterFundSch.P129EffDt = [Compl P129
A3C]!EFFDATE, MasterFundSch.[REPUR SUB] = IIf([groupID]="FH",IIf(Month([fund
dt])=Month(Now()),IIf(Day([fund Dt])<16,"N","C"),"C"),Null)
WHERE (((MasterFundSch.P129RptDt) Is Null) AND (([Compl P129 A3C].
NEWINVNUMBER)=[MasterFundSch]![New Inv]) AND (([Compl P129 A3C].NEWCATNUMBER)
=[MasterFundSch]![New Cat]) AND (([Compl P129 A3C].ACTIONCODE)="0000" Or (
[Compl P129 A3C].ACTIONCODE)="0820" Or ([Compl P129 A3C].ACTIONCODE)="0077"
Or ([Compl P129 A3C].ACTIONCODE)="0078"));


I'm getting records updating that are not one of the four Action Codes that
I've assigned.
Help before I go crazy!
 
Try this - you may need to retype the quotes --
WHERE [MasterFundSch].[P129RptDt] Is Null AND [Compl P129
A3C].[NEWINVNUMBER] =[MasterFundSch]![New Inv] AND [Compl P129
A3C].[NEWCATNUMBER]=[MasterFundSch]![New Cat] AND ([Compl P129
A3C].[ACTIONCODE]="0000" Or [Compl P129 A3C].[ACTIONCODE]="0820" Or [Compl
P129 A3C].[ACTIONCODE]="0077" Or [Compl P129 A3C].[ACTIONCODE]="0078");
 
Usually, this means the problem is in the logic of the where clause - often in
the placement of the parentheses. I've rewritten the query using "in" instead
of the multiple ors. I also moved some of the where clause up to the Join clause.

I can't say that I'm completely sure of the logic you were trying to apply, so
you may need to rewrite this again.

UPDATE [Compl P129 A3C] INNER JOIN MasterFundSch
ON [Compl P129 A3C].LOANNUMBER = MasterFundSch.[Loan Number]
AND [Compl P129 A3C].NEWINVNUMBER=[MasterFundSch].[New Inv]
AND [Compl P129 A3C].NEWCATNUMBER=[MasterFundSch].[New Cat]

SET MasterFundSch.P129OldInv =[OLDINVNUMBER]
, MasterFundSch.P129OldCat = [OLDCATNUMBER]
, MasterFundSch.P129NewInv = [Compl P129 A3C].NEWINVNUMBER
, MasterFundSch.P129NewCat = [Compl P129 A3C].NEWCATNUMBER
, MasterFundSch.P129PrinBal = [PRINBAL]
, MasterFundSch.P129PrinTran = [Compl P129 A3C].PRINTRANS
, MasterFundSch.P129Diff = [PRINBAL]-[PRINTRANS]
, MasterFundSch.P129RptDt = [Compl P129 A3C].RPTDATE
, MasterFundSch.P129ActionCd = [Compl P129 A3C].ACTIONCODE
, MasterFundSch.P129DueDt = [Compl P129 A3C].DUEDATE
, MasterFundSch.P129EffDt = [Compl P129 A3C].EFFDATE
, MasterFundSch.[REPUR SUB] = IIf([groupID]="FH",
IIf(Month([fund dt])=Month(Now()),IIf(Day([fund Dt])<16,"N","C"),"C"),Null)

WHERE MasterFundSch.P129RptDt Is Null
AND [Compl P129 A3C].ACTIONCODE IN ("0000","0820","0077","0078")

Sysdupe123 via AccessMonster.com said:
I've been agonizing over this all day! This is the SQL for the query that I
built:

UPDATE [Compl P129 A3C] INNER JOIN MasterFundSch ON [Compl P129 A3C].
LOANNUMBER = MasterFundSch.[Loan Number] SET MasterFundSch.P129OldInv =
[OLDINVNUMBER], MasterFundSch.P129OldCat = [OLDCATNUMBER], MasterFundSch.
P129NewInv = [Compl P129 A3C]!NEWINVNUMBER, MasterFundSch.P129NewCat = [Compl
P129 A3C]!NEWCATNUMBER, MasterFundSch.P129PrinBal = [PRINBAL], MasterFundSch.
P129PrinTran = [Compl P129 A3C]!PRINTRANS, MasterFundSch.P129Diff = [PRINBAL]-
[PRINTRANS], MasterFundSch.P129RptDt = [Compl P129 A3C]!RPTDATE,
MasterFundSch.P129ActionCd = [Compl P129 A3C]!ACTIONCODE, MasterFundSch.
P129DueDt = [Compl P129 A3C]!DUEDATE, MasterFundSch.P129EffDt = [Compl P129
A3C]!EFFDATE, MasterFundSch.[REPUR SUB] = IIf([groupID]="FH",IIf(Month([fund
dt])=Month(Now()),IIf(Day([fund Dt])<16,"N","C"),"C"),Null)
WHERE (((MasterFundSch.P129RptDt) Is Null) AND (([Compl P129 A3C].
NEWINVNUMBER)=[MasterFundSch]![New Inv]) AND (([Compl P129 A3C].NEWCATNUMBER)
=[MasterFundSch]![New Cat]) AND (([Compl P129 A3C].ACTIONCODE)="0000" Or (
[Compl P129 A3C].ACTIONCODE)="0820" Or ([Compl P129 A3C].ACTIONCODE)="0077"
Or ([Compl P129 A3C].ACTIONCODE)="0078"));

I'm getting records updating that are not one of the four Action Codes that
I've assigned.
Help before I go crazy!
Click to expand...
 
Yeah, I tried that. When I went into SQL view, from Design view, Access put
the Quotes back in! Either way, it updated with codes that I didn't want.

KARL said:
Try this - you may need to retype the quotes --
WHERE [MasterFundSch].[P129RptDt] Is Null AND [Compl P129
A3C].[NEWINVNUMBER] =[MasterFundSch]![New Inv] AND [Compl P129
A3C].[NEWCATNUMBER]=[MasterFundSch]![New Cat] AND ([Compl P129
A3C].[ACTIONCODE]="0000" Or [Compl P129 A3C].[ACTIONCODE]="0820" Or [Compl
P129 A3C].[ACTIONCODE]="0077" Or [Compl P129 A3C].[ACTIONCODE]="0078");
I've been agonizing over this all day! This is the SQL for the query that I
built:
Click to expand...
[quoted text clipped - 19 lines]
I've assigned.
Help before I go crazy!
Click to expand...
Click to expand...
 
The "In" function did it! Thanks!

John said:
Usually, this means the problem is in the logic of the where clause - often in
the placement of the parentheses. I've rewritten the query using "in" instead
of the multiple ors. I also moved some of the where clause up to the Join clause.

I can't say that I'm completely sure of the logic you were trying to apply, so
you may need to rewrite this again.

UPDATE [Compl P129 A3C] INNER JOIN MasterFundSch
ON [Compl P129 A3C].LOANNUMBER = MasterFundSch.[Loan Number]
AND [Compl P129 A3C].NEWINVNUMBER=[MasterFundSch].[New Inv]
AND [Compl P129 A3C].NEWCATNUMBER=[MasterFundSch].[New Cat]

SET MasterFundSch.P129OldInv =[OLDINVNUMBER]
, MasterFundSch.P129OldCat = [OLDCATNUMBER]
, MasterFundSch.P129NewInv = [Compl P129 A3C].NEWINVNUMBER
, MasterFundSch.P129NewCat = [Compl P129 A3C].NEWCATNUMBER
, MasterFundSch.P129PrinBal = [PRINBAL]
, MasterFundSch.P129PrinTran = [Compl P129 A3C].PRINTRANS
, MasterFundSch.P129Diff = [PRINBAL]-[PRINTRANS]
, MasterFundSch.P129RptDt = [Compl P129 A3C].RPTDATE
, MasterFundSch.P129ActionCd = [Compl P129 A3C].ACTIONCODE
, MasterFundSch.P129DueDt = [Compl P129 A3C].DUEDATE
, MasterFundSch.P129EffDt = [Compl P129 A3C].EFFDATE
, MasterFundSch.[REPUR SUB] = IIf([groupID]="FH",
IIf(Month([fund dt])=Month(Now()),IIf(Day([fund Dt])<16,"N","C"),"C"),Null)

WHERE MasterFundSch.P129RptDt Is Null
AND [Compl P129 A3C].ACTIONCODE IN ("0000","0820","0077","0078")
I've been agonizing over this all day! This is the SQL for the query that I
built:
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
Back
Top