VBA for report

[Spiderdan1016]

I have a report that i run every morning. The report breaksdown the sales for the day by username and type of sale. each sale can also be either an order,type O, credit, type C, or return, type R. I need to add some VBA to the report that when a line is a type C or type R the qty shipped and price is multiplied by -1 to make it negative. any ideas. i have experience in programming just havent used VBA in so long forgot the syntax

 

[Spiderdan1016]

or if its easier the report is made off of a table. if there is an easier way of writing something to go through the table and set the values to negatives in the table before the report is even run that would work as well.

 

[SkipVought]

Hi,

If the report is form a table, why not use MS Query to gen the report. The calculation can either be done in the query or in a column adjacent to the resultset. There is a switch in Edit/Data Range Parameters, to copy formula in adjacent column(s).

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[Spiderdan1016]

i'm not sure i understand. I highly customized the report will creating it with ms query keep the same customizations? and then what will i put in the edit/date range paramaters if type c make negative?

 

[SkipVought]

Then just add a column for a NEW Price
[tt]
=If(Or(Type="C",Type="R"),-1,1)*Price
[/tt]
I'm using Names for cell references for clarity.

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[Spiderdan1016]

everytime i type it in it says its missing a comma...

=If((Or([SATYPE]="C",[SATYPE]="R"),-1,1)*[# Shipped])

am i missing something. satype is the field name for the types. and i need to multiply it for Qty SHipped and Price.

 

[SkipVought]

parenthesis in wrong place...
[tt]
=If(Or([SATYPE]="C",[SATYPE]="R"),-1,1)*[# Shipped]
[/tt]
If this is in a query...
[tt]
=IIf(Or([SATYPE]="C",[SATYPE]="R"),-1,1)*[# Shipped]
[/tt]



Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]