"Sum(iif" query

[gmazza76]

Good morning / afternoon

I am trying to re create a formula in access that I know works in Excel, but I seem to be having a few issues and could do with some help if possible.

I have 2 tables as follows :
Tbl_PWeek - holds the previous weeks start (Monday) and end (Sunday) date
DataAll - Data stored summing the columns below based on the "where" for Survey Date

I can't see what I am definitely missing as it seems a simple formula. Is there a way to combine the "Working Access Query" once the sum has been corrected to be completed in 1 query something like the bottom one. I am getting a syntax error on the "Hopeful" query "Syntax error (comma) in query expression"

Excel Formula
= SUM(BPN-BDN )/Surveys *100 = "-25"

Working Access Query (Kind Of)
SurveyDate "Where" Between [Tbl_PWeek].[FromDate] And [Tbl_PWeek].[ToDate]
Brand : Sum([DATAALL]![BN]-[DATAALL]![BDN]/[DATAALL]![Surveys]*100) = -4775

Hopeful Access Query
Brand Score : Sum(IIf([DATAALL].[Survey]>=[Tbl_PWeek].[FromDate] And ([DATAALL].[SurveyDate]<=[Tbl_PWeek].[ToDate],[DATAALL]![BPN]-[DATAALL]![BDN]/[DATAALL]![Surveys]*100),0)) 
[code]

thanks in advance and I hope you have a good Christmas
Gavin

 

[welshgasman]

I would surround the math with () as you had in Excel.?
In the first query the field name is not the same BN and not BPN?
I would also use BETWEEN and AND for the dates, as it makes it a little clearer.

 

[gmazza76]

apologies @welshgasman

Can you explain please as I tried to use the Between and AND previously and I had an error coming up. It seems like the calculation isnt right no matter what I do.
Do you mean something like this, apologies I usually do the between in as part of the query but not in the overall calculation

Brand Score : IIf(BETWEEN [DATAALL].[Survey]>=[Tbl_PWeek].[FromDate] And ([DATAALL].[SurveyDate]<=[Tbl_PWeek].[ToDate],SUM([DATAALL]![BPN]-[DATAALL]![BDN]/[DATAALL]![Surveys]*100),0)))

Thanks

 

[welshgasman]

No. something along the lines of

Brand Score : IIf([DATAALL].[Survey] BETWEEN [Tbl_PWeek].[FromDate] And [Tbl_PWeek].[ToDate],SUM([DATAALL]![BPN]-[DATAALL]![BDN])/[DATAALL]![Surveys]*100,0)