Formula Help - Access 2003

[mrichelle]

Created a query. One of the fields calculates Sales year to date for 2006. It is a simple formula, see below.

2006ytd: nz([Jan-06])+nz([Feb-06])+nz([Mar-06])+nz([Apr-06])+nz([May-06])+nz([Jun-06])

The formula works as it should and captures the correct data, but if there were negative sales it will also capture it. What can I put into this formula that will not look at negative ($500.00) sales?

Thanks!

 

[VRoscioli]

If you want it to just ignore negative values, you could try:

2006ytd: max(nz([Jan-06]),0)+max(nz([Feb-06]),0)+max(nz([Mar-06]),0)+max(nz([Apr-06]),0)+max(nz([May-06]),0)+max(nz([Jun-06]),0)

-V

 

[dhookom]

I don't think "V" understands the Max() aggregate in SQL. There is only one argument used in Max() and Min() and Sum()...

IMHO, you should normalize your table structure so your query would be

SELECT Sum(IIf([Sales]<0,0,[Sales])) As TotalSales
FROM tblNormalized
WHERE Year(SaleDate0 = 2006;

I also never use Nz() without explicitly providing 2 arguments.

2006ytd: IIf(nz([Jan-06],0)<0,0,nz([Jan-06],0))+ ...

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mrichelle]

Duane,

Can you explain your code to me? Maybe I am using it wrong...where do I put this code? I guess I am a little confused.

 

[dhookom]

My 2006YTD.... replaces your expression in your query. However, you need to add the other fields just I as created with [Jan-06]. I wasn't going to create the entire expression but just wanted to get you started.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mrichelle]

Duane,
Ok well I tried it and it does not work. I wonder if it has something to do with the less than sign and maybe is will work with greater than? Don't know? But will try to figure it out. Also, I did not expect you to create the whole formula for me and actually I was talking about the code before that one with the select statement. Somehow, I failed to mention it in my reply, my fault.

V,
Thanks so much for attempting to help. Whether right or wrong, your suggestions are more than welcome.

 

[dhookom]

Can you tell us what you entered as the expression and what you mean by "does not work"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]