Hi.
I have a dB, (accdb), that has two main tables, ('I' & 'C'). Table 'I' & 'C' have the unique ID for each item/device; table 'C' has 2 date fields that annually are updated. records in the tables are linked together by a key/foreign_key, (ID) between these. I create a 3rd table with a list of which records to update the dates recorded in the 'C' table. Table_3 is incorporated in a query that Inner joins (tables 'I' and 'C') and inner join to my 3rd table ON the item unique ID in both tabel_3 and 'I', to update the two date fields in the C table; updating only those records whose Unique IDs match the second join.
If I build a simple qry and run that from the Nav panel, no problem. If I incorporate that into a VBA module, correcting punctuation, and trigger it by a button on a switchboard, I get a "syntax error in join operation' error. Below is the 'generic' format and syntax, (C, I, Table_3, are aliases for real tables; ID, newest_date, future_date are aliases for record field names; and 'DayCount' is a numeric variable defined by an inputbox in the code). ps,, this variable will be a negative number! as you might see, the newest date is updated at some time after the fact, while the future date is coded to be one year from then.
UPDATE (((C) INNER JOIN (I) ON (C.ID) = (I.ID))
INNER JOIN (Table_3) ON (I.ID) = (Table_3.ID)
SET ((C.newest_date) = DateAdd('d', DayCount, Date()) ,
(C.future_Date) = DateAdd('yyyy',1,DateAdd('d',[DayCount],Date()));
If you need, I can provide actual VBA code for the module, but I think from the error I'm getting 'syntax in join statement', this may be sufficient.
Any suggestions?