ambiguous name in query expression

[longhair]

afternoon all,
receiving the following:
runtime error '-2147217900 (80040e14)'
ambiguous name in query expression
'yearcalc([tblshiptmephdr]![confirmed-date-time])'.
the query is in a module that has worked for years and none of the syntax or tables / fields have changed.

cmd.CommandText = "INSERT INTO tblSHIPTempShippingData ( AvesSKU, [FC$], PCS, [Year], CustCode, [TTLShippingK$], ConfirmedDate, [pick-ticket-num] ) SELECT UCase([product-code]) AS Expr3, tblSHIPTempPicTrl.[sales-cost], tblSHIPTempPicTrl.[SumOfpicked-qty], YearCalc([tblSHIPTemphdr]![confirmed-date-time]) AS Expr1, tblSHIPTempOEtrl.[cust-code], ([tblSHIPTempOEtrl]![extended-price]/[tblSHIPTempOEtrl]![qty-ordered])*[tblSHIPTempPicTrl]![SumOfpicked-qty] AS Expr2, tblSHIPTemphdr.[confirmed-date-time], tblSHIPTemphdr.[pick-ticket-num] FROM (tblSHIPTempOEtrl INNER JOIN tblSHIPTemphdr ON tblSHIPTempOEtrl.[order-num] = tblSHIPTemphdr.[order-num]) INNER JOIN tblSHIPTempPicTrl ON (tblSHIPTempOEtrl.[line-num] = tblSHIPTempPicTrl.[order-line-num]) AND (tblSHIPTemphdr.[pick-ticket-num] = tblSHIPTempPicTrl.[pick-ticket-num]) WHERE (((tblSHIPTemphdr.[shipment-num])<>''));"
    cmd.Execute

i could see there being an issue with confirmed-date-time being used twice if the tables were not explicitly defined - but they are.
any ideas on how to fix or what may have happend? only thing new that i can think of were patches that ms sent down on 06/22.
thanks.
regards,
longhair

 

[PHV]

I'd use this instead:
YearCalc(tblSHIPTemphdr[!].[/!][confirmed-date-time])'

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

 

[longhair]

thanks phv. but no dice. i did, however, discover the issue. yearcalc is a public function in the vba module. i'm working on another module and did not remember to change the public function name in the new module. hence, access did not know which public function to use, even though they are the same.
sometimes i am a foolish mortal.
regards,
longhair