How To Convert Sql Server Join Query To Access 1

[vish6]

Hi
I want to convert this sql server query in MS Access.It will work in sql server.
So plz Tell me How to write it in Access.

Update StockTable Set Outward=Outward-SM.Netweight,OutwardQty=OutwardQty-SM.Qty From Stocktable ST Join SalesVchMaterialDescCR SM on ST.Materialid=SM.Material_Name where ST.Materialid=SM.Material_Name and Transactionid=" & txtTransactionID.text & " and ST.VoucherDate=SM.VoucherDate

 

[mishbaker]

Try bracketing the field names and it looks to me like your quotes are backwards for Access, it will read "& txttransactionsID.text &" as a string literal. Leaving those quotes out should help with that portion. Also, quotes should be used in pairs. Hope that helps get you on track...



All lessons learned from the School of Hard Knocks........at least tuition is cheap.

 

[JerryKlmns]

Update StockTable Set Outward = nz([Outward])-nz([SM].[Netweight]), OutwardQty = nz([OutwardQty])-nz([SM].[Qty] From Stocktable As ST Inner Join SalesVchMaterialDescCR As SM on (ST.Materialid=SM.Material_Name) And (ST.VoucherDate = SM.VoucherDate) where Transactionid ='" & txtTransactionID & "'"

nz([Field]) = converts Nulls to 0
nz([Field], whatever) = converts Nulls to whatever

 

[PHV]

The syntax in access is:
UPDATE Table1 AS A INNER JOIN Table2 AS B ON join clause
SET ....
WHERE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886