Update query to a VBA invoked query

T

TJT

I created the following "query" via access, but it won't allow me to add any
more arguments...can someone please help me get started coding this in VBA?
I haven't a clue where to begin...here is the current SQL in access

UPDATE TrackInvoice SET TrackInvoice.FreightCharge =
IIf([ShipViaRate]="FREIGHT
ALLOWED",[FreightCharge]=0,IIf(InStr([ShipViaRate],"C"),[freightCharge]=0,IIf([customerNumber]="MKB001",[RefChg],IIf([customerNumber]="WIS002",[RefChg],IIf([customerNumber]="BAT358",[RefChg],IIf([customerNumber]="BAT002",[RefChg],IIf([customerNumber]="MOO002",[RefChg],IIf([customerNumber]="FIR006",[RefChg],IIf([customerNumber]="SIG003",[RefChg],IIf([customerNumber]="PEG001",[RefChg],IIf([customerNumber]="BAT008",[RefChg],IIf([customerNumber]="MAR066",[RefChg],IIf([CustomerNumber]="BAT149",[RefChg],IIf([CustomerNumber]="STA011",[RefChg],[FreightCharge]))))))))))))));
 
B

Barry Gilbert

I would recommend getting away from hard-coding your customer numbers in the
query. This should be table-based. I assume you have a customers table. In
that table, create a column that indicates whether the customer should have
the freight charge be equal to the RefChg column. Then you can relate the
tables in your query and greatly simplify your query. This would also allow
you to change which customer this applies to without having to change your
query. You'd just change a value in your table.

Barry
 
Top