Doyce-
Try this:
strSQL = "UPDATE VinMaster SET VinMaster.CustPONo = " & """" & [Forms]![frmAddInvoiceDetail]![txtCustPO] & """, " & _
"VinMaster.Mat_Labor = " & [Forms]![frmAddInvoiceDetail]![txtMatAndLabor] & ", " & _
"VinMaster.FET = IIf([VinMaster].[Canadian], 0, " & [Forms]![frmAddInvoiceDetail]![txtFET] & "), " & _
"VinMaster.Freight = " & [Forms]![frmAddInvoiceDetail]![txtDelChg] & ", " & _
"VinMaster.BOM = " & """" & [Forms]![frmAddInvoiceDetail]![cboBOM] & """" & _
" WHERE (((VinMaster.[Cust ID])= " & """" & [Forms]![frmAddInvoiceDetail]![cboCustomer] & """) " & _
"And (Vinmaster.[Invoiced]) = " & False & _
" AND ((VinMaster.[Unit #]) Between """ & [Forms]![frmAddInvoiceDetail]![cboStartNo] & """" & _
" And " & """" & [Forms]![frmAddInvoiceDetail]![cboEndNo] & """));"
Not sure it'll do the test correctly in the middle of the update expression, but worth a try. The other alternative would be to run two updates, one with WHERE [Canadian] = True setting FET to 0, and the second with [Canadian] = False setting FET to the form value.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of winberry.doyce@roadsysinc.com
Sent: Thursday, November 07, 2013 2:17 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] SQL help!
Good morning friends.
I need help modifying this procedure which updates records in my Vinmaster table with values from controls on a form.:
Dim db As Database, strSQL As String
Set db = CurrentDb
strSQL = "UPDATE VinMaster SET VinMaster.CustPONo = " & """" & [Forms]![frmAddInvoiceDetail]![txtCustPO] & """, " & _
"VinMaster.Mat_Labor = " & [Forms]![frmAddInvoiceDetail]![txtMatAndLabor] & ", " & _
"VinMaster.FET = " & [Forms]![frmAddInvoiceDetail]![txtFET] & ", " & _
"VinMaster.Freight = " & [Forms]![frmAddInvoiceDetail]![txtDelChg] & ", " & _
"VinMaster.BOM = " & """" & [Forms]![frmAddInvoiceDetail]![cboBOM] & """" & _
" WHERE (((VinMaster.[Cust ID])= " & """" & [Forms]![frmAddInvoiceDetail]![cboCustomer] & """) " & _
"And (Vinmaster.[Invoiced]) = " & False & _
" AND ((VinMaster.[Unit #]) Between """ & [Forms]![frmAddInvoiceDetail]![cboStartNo] & """" & _
" And " & """" & [Forms]![frmAddInvoiceDetail]![cboEndNo] & """));"
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
MsgBox CStr(db.RecordsAffected) & " records updated.", vbInformation, "Records Updated"
Set db = Nothing
The Vinmaster table has a yes/no field named Canadian. If Canadian is true, I would like the FET field to be zero else I want it to do what it does in the procedure above. I just don't know how to do that in SQL. I appreciate your help!
Doyce
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar