deciphering errors on a double-joined query

[hammerman]

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?

[CJ_London]

You seem to have more brackets than I would expect and you are missing a ) - in the join part you have 9 ( and 8 )

is the sql provided the sql generated by the vba code? it may be your vba code is incorrect. And how does it compare with the sql from your query.

UPDATE (((C) INNER JOIN (I) ON (C.ID) = (I.ID))
INNER JOIN (Table_3) ON (I.ID) = (Table_3.ID)

Also your SET part has 8 ( and 7 ) so you missing one there as well

[hammerman]

@Ajax: Here is my VBA SQLstring and the definition for the dates to update in the table, which I wrote based on the query designer:
disregarding the first response, and unequal parentheses, is there something else I missed? As mentioned, the query designer works but is more labor-intensive.
<<
DayCount = InputBox("How many days ago was the cal done?")
'CalDate = DateAdd("d", DayCount, Date)
'DueCalDate = DateAdd("yyyy", 1, DateAdd("d", DayCount, Date )

qryCalUpdate = "UPDATE (((Calibration_Details) INNER JOIN (Instrument) ON (Calibration_Details.Inst_ID) = (Instrument.Inst_ID)) "
qryCalUpdate = qryCalUpdate + vbCrLf + " INNER JOIN (tblDailyCals) ON (Instrument.BC_Num) = (tblDailyCals.BC_Num) "
qryCalUpdate = qryCalUpdate + vbCrLf + " SET ((Calibration_Details.Cal_Date) = DateAdd('d', [DayCount], Date) , "
qryCalUpdate = qryCalUpdate + vbCrLf + " (Calibration_Details.Due_Date) = DateAdd('yyyy',1,DateAdd('d',[DayCount],Date())),"
qryCalUpdate = qryCalUpdate + vbCrLf + " (Calibration_Details.Cal_By) = 'company name here'));"
>>

[June7]

Concatenate variables.

qryCalUpdate = "UPDATE (((Calibration_Details) INNER JOIN (Instrument) ON (Calibration_Details.Inst_ID) = (Instrument.Inst_ID)) "
qryCalUpdate = qryCalUpdate + vbCrLf + " INNER JOIN (tblDailyCals) ON (Instrument.BC_Num) = (tblDailyCals.BC_Num) "
qryCalUpdate = qryCalUpdate + vbCrLf + " SET ((Calibration_Details.Cal_Date) = DateAdd('d', " & [DayCount] & ", Date) , "
qryCalUpdate = qryCalUpdate + vbCrLf + " (Calibration_Details.Due_Date) = DateAdd('yyyy',1,DateAdd('d', " & [DayCount] & ", Date())),"
qryCalUpdate = qryCalUpdate + vbCrLf + " (Calibration_Details.Cal_By) = 'company name here'));"

Alternatively, since saving calculated data is usually not advised, save the calibration date and calculate the other dates when needed.

[hammerman]

Finally figured it out. thanks for your help. I first, removed all parentheses; then, because of syntax error message, added some back in, on the phrasing that it pointed to. It seems the compiler needed them to prioritize the grouping of details for what was to happen. I also pulled the brackets from the variable, because it was defined before the SQL string was loaded. Anyway, thanks for your help.

[CJ_London]

tip for the future. When building sql strings in VBA, put

debug.print qryCalUpdate

after you have finished constructing the string. This will print the result in the immediate window. Often a quick inspection will tell you if it is correct sql syntax or not. You can also copy the string and paste into the sql window of the query builder and try to run it to see if you a) get any errors and b) the query has done what it is supposed to do - in this case update a record

[June7]

Ooops, sorry for my absent-minded typo leaving the brackets on the VBA variable. Glad you figured it out.

[hammerman]

Thanks, Ajax
I have been using the immediate window extensively. however, the sql string in that window appears to be syntactically correct. I still believe the lack of parentheses destroyed the compilers order of operations so to speak. Starting from square one, with no ()'s, and adding them in around the barest minimum caused different error messages, referring to different sections of the sql phrasing. adding ()'s there eliminated the issues, and it has been running great. Thanks again for all the help.

(I enjoy browsing these forum posts and learning from all you experts.)