Date Serial - Division by Zero 1

[TommyP]

Hi All and Happy Holidaze,

I'm stumped. I am using a date serial formula to get current year, prior month GP%. Some of my sales and cogs have 0.00 and Access gives me the old division by zero error. I cannot figure out how/where to insert an iif statement so the query runs. Here is what my query looks like.

CY PM GP%: Sum(IIf(DateSerial([Year],[Month],1)=DateSerial(Year(Date()),Month(Date())-1,1),([Sales $]-[COGS])/[Sales $],0))

This is run as an Expression.

Any help would be greatly appreciated.

TommyP

 

[dhookom]

Try something like:

CY PM GP%: Sum(IIf(DateSerial([Year],[Month],1)=DateSerial(Year(Date()),Month(Date())-1,1) AND [Sales $]<> 0,([Sales $]-[COGS])/[Sales $],0))

Duane
Hook'D on Access
MS Access MVP

 

[TommyP]

Hi Duane. That works perfectly. You learn something new every day.

Thank you and have a Happy New Year!


TommyP