Update query with date not working 1

[vba317]

I am using access 2003 and trying to write an update query to update a date field. I am trying to combine three fields from different tables to create this date.
All fields are integers. If I run the query for the dbo_dic_Period table cal_num = 1 and year = 2015. From the z_CloseSched table I would get 9 for a date of 1/9/2015.
Any Help is appreciated.

Tom

UPDATE z_CloseSched, dbo_dic_Period SET z_CloseSched.closedt = "#" & [dbo_dic_Period].[cal_num] & "/" & [defdom] & "/" & [dbo_dic_Period].[yr] & "#"
WHERE (((dbo_dic_Period.calpddiff)=0) AND ((z_CloseSched.uci)="AAC"));

 

[SkipVought]

where are your database(s)

UPDATE z_CloseSched, dbo_dic_Period SET z_CloseSched.closedt = "#" & [dbo_dic_Period].[cal_num] & "/" & [defdom] & "/" & [dbo_dic_Period].[yr] & "#"
WHERE (((dbo_dic_Period.calpddiff)=0) AND ((z_CloseSched.uci)="AAC"));

 

[vba317]

The databases are : dbo_dic_Period and z_CloseSched

 

[dhookom]

I can't imagine an Acces query with more than one table and no joins would be updateable. I typically use the DateSerial() function to create dates from year, month, and day of month.

Is this VBA? Is there more code you can share?

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'm with Duane, eg:

UPDATE z_CloseSched 
SET closedt = DateSerial(DlookUp("yr","dbo_dic_Period","calpddiff=0"),DlookUp("cal_num","dbo_dic_Period","calpddiff=0"),defdom)
WHERE uci="AAC"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886