Update Query, 2007 - Not Working??

J

Jim Dudley

SQL Code for update query.

Query is not posting values from Registration
Table([Workshop_Registrations]) to the Summary Table (Workshop_Credits]).

Any suggestions as to Why????

SQL
UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz("Workshop_Credits].[EL_Credits]",0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time();

Note Both files are showing Posting dates and times but the values are not
being input into the [Workshop_Credits] Table.
 
J

John Spencer

Let's see in the first Set you have quotes around and the table and
field names and you have misspelled a table name in the IIF statement.

(Val(nz("Workshop_Credits].[EL_Credits]",0)) << no quotes
IIf(([Worshop_Registrations].[Type]="EL") << Workshop_Registrations

You also have an abundance of parentheses. Not all of them are needed,
but they should not cause a problem. I'll get you started and allow you
to clean up the mess.

UPDATE Workshop_Registrations INNER JOIN Workshop_Credits
ON Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num

SET Workshop_Credits.EL_Credits =
IIf([Workshop_Registrations].[Type]="EL"
, Val(nz(Workshop_Credits].[EL_Credits],0)) +
Val(nz([Workshop_Registrations].[Credits],0)))

, Workshop_Credits.OD_Credits =
IIf([Workshop_Registrations].[Type]="OD"
, Val(nz([Workshop_Credits].[OD_Credits],0)) +
Val(nz(Workshop_Registrations].[Credits],0)))

, Workshop_Credits.PD_Credits =
IIf([Workshop_Registrations].[Type]="PD"
, Val(nz([Workshop_Credits].[PD_Credits],0)) +
Val(nz(Workshop_Registrations].[Credits],0)))

,
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"), << Misspelled table
(Val(nz([Workshop_Credits].[PL_Credits],0))+
(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =

IIf(([Worshop_Registrations].[Type]="GC"),
(Val(nz([Workshop_Credits].[GC_Credits],0))+
(Val(nz([Workshop_Registratrions].[Credits],0))))) << Misspelled table

, Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)
+Nz([Workshop_Credits].[OD_Credits],0)+
Nz([Workshop_Credits].[PD_Credits],0)+
Nz([Workshop_Credits].[PL_Credits],0)+ Nz([Workshop_Credits].[GC_Credits],0)

, Workshop_Credits.Posted = True
, Workshop_Credits.Post_Date = Date()
, Workshop_Credits.Post_Time = Time()
, Workshop_Registrations.Posted = True
, Workshop_Registrations.Post_Date = Date()
, Workshop_Registrations.Post_Time = Time();


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Jim said:
SQL Code for update query.

Query is not posting values from Registration
Table([Workshop_Registrations]) to the Summary Table (Workshop_Credits]).

Any suggestions as to Why????

SQL
UPDATE Workshop_Registrations INNER JOIN Workshop_Credits ON
Workshop_Registrations.Student_Num = Workshop_Credits.Student_Num SET
Workshop_Credits.EL_Credits =
IIf(([Worshop_Registrations].[Type]="EL"),(Val(nz("Workshop_Credits].[EL_Credits]",0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.OD_Credits =
IIf(([Worshop_Registrations].[Type]="OD"),(Val(nz([Workshop_Credits].[OD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PD_Credits =
IIf(([Worshop_Registrations].[Type]="PD"),(Val(nz([Workshop_Credits].[PD_Credits],0))+(Val(nz("Workshop_Registrations].[Credits]",0))))),
Workshop_Credits.PL_Credits =
IIf(([Worshop_Registrations].[Type]="PL"),(Val(nz([Workshop_Credits].[PL_Credits],0))+(Val(nz([Workshop_Registrations].[Credits],0))))),
Workshop_Credits.GC_Credits =
IIf(([Worshop_Registrations].[Type]="GC"),(Val(nz([Workshop_Credits].[GC_Credits],0))+(Val(nz([Workshop_Registratrions].[Credits],0))))),
Workshop_Credits.Total_Credits =
NZ([Workshop_Credits].[EL_Credits],0)+Nz([Workshop_Credits].[OD_Credits],0)+Nz([Workshop_Credits].[PD_Credits],0)+Nz([Workshop_Credits].[PL_Credits],0)+Nz([Workshop_Credits].[GC_Credits],0),
Workshop_Credits.Posted = Yes, Workshop_Credits.Post_Date = Date(),
Workshop_Credits.Post_Time = Time(), Workshop_Registrations.Posted = Yes,
Workshop_Registrations.Post_Date = Date(), Workshop_Registrations.Post_Time =
Time();

Note Both files are showing Posting dates and times but the values are not
being input into the [Workshop_Credits] Table.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update Query, 2007 - Not Working? 6

Top