Reserved error (-3087)

[vasque01]

Hi,
I just updated some columns in my tables and now one of my queries is not working.
However, the columns which are used in this query were not updated or changed in any way.
When I want to go to Datasheet view I get an error message: "Reserved error (-3087);there is no message for this error".

I have an expample field here below, could anyome please let me know whether there is something wrong with the expression?


Screening conducted Week 25: Count(IIf(([New Maf Query].[Quarter Screening Conducted] Like "*2018*") And ([New Maf Query].[Week Screening Conducted]="25"),1))

 

[Minty]

Count is an aggregate function but you're not aggregating any data. Try removing the count.

 

[isladogs]

Also IIf statements have 3 parts: IIf(expression, true part, false part)
So as well as removing the Count you need the 3rd part

 

[vasque01]

HI,

So the intetnion of this part is to agregate data. Perhaps better if I show the whole expression:

SELECT Count(IIf(([New Maf Query].[Quarter Screening Conducted] Like "*2018*") And ([New Maf Query].[Week Screening Conducted]="25"),1)) AS [Screening conducted Week 25], Count(IIf([New Maf Query].[Quarter Request Received] Like "*2018*",1))+18 AS [Requests received YTD], Count(IIf([New Maf Query].[Quarter Screening Conducted] Like "*2018*",1)) AS [Screenings Conducted YTD], Count(IIf(([New Maf Query].[Quarter Screening Conducted] Like "*2018*") And ([New Maf Query].[Quarter Final Recommendation]<>""),1)) AS [Screening Completed (final recommendation issued)]
FROM [New Maf Query]
WHERE ((([New Maf Query].[Screening results]) Is Null Or ([New Maf Query].[Screening results])="“Passed”" Or ([New Maf Query].[Screening results])="Stopped due to other" Or ([New Maf Query].[Screening results])="Stopped due to finding"));

The weird part is that this used to work fine before I edited some columns. But the columns I edited have nothing to do with the data used in the Expression.
Any other suggestions?

 

[isladogs]

We obviously have no idea what the effect of you altering some columns was.

However each of your Count(IIF.... statements make little sense and the syntax is incorrect as already stated. What should the false part in each case be? Zero?

I think you could replace all of these by doing an aggregate query using either Count or Sum.

Perhaps you should explain what each expression is meant to do.

 

[vasque01]

Hi,

So I have checked several fields and it appears that there is only one which is not working.
I have a query named "New Maf Query" which contains data about the screenings we have conducted in the past three years.

What I want is to create a field where it says how many screenings we conducted in a specific week.
In this case week 25 (the New Maf Query contains a column for selecting the week). Therefore the expresion starts with "count" because I want to count the amount of screenings.
Then I only want to count the screenings of 2018 so I included: Like "*2018*".

I tried to remove count but it still does not work, and I do not know how to work without the "iff's".


the formula is still:
Screening conducted Week 25: Count(IIf(([New Maf Query].[Quarter Screening Conducted] Like "*2018*") And ([New Maf Query].[Week Screening Conducted]="25"),1))

is there another way for calculating the screening in one week?

 

[isladogs]

Yes. Use an aggregate query as already stated

 

[Minty]

As Colin has suggested, I would have thought a fairly simple

Count(Anyfield) as Qty , Year(DateOfScreening) as ScreenYear,   Week(DateOfScreening) as ScreenWeek
FROM [YourMainTable]
WHERE Year(DateOfScreening)  = 2018 AND Week(DateOfScreening) = 25
Group By Year(DateOfScreening) ,   Week(DateOfScreening)

From your base table data would do this without any of the complications you appear to introducing?