Select Count

[Myysterio]

My programming instructor told me to use this for counting records in a report with multiple conditions;

=Select count (select *
from

where [req]=Yes and [comp]=0 and ((Now()-[arrivaldate])/30.4)>=0 and ((Now()-[arrivaldate])/30.4)<=4 and [status]="UNQAL")


But when I enter it, I get this error.

"the syntax of the subquery in this expression is incorrect" "Check the subquery's syntax and enclose the subquery in parentheses"

Anyone help?

I am actually doing this database for work, my instructor is teaching me C# which has nothing to do with this database.

 

[Galaxiom]

Your parentheses don't match. Too many opens or not enough closes.
What I do is count plus one for every open and minus one for every close.
You should always get back to zero at the end.

Should either be a close at the end (or better drop the opens at the beginnings) of the second last and third last lines. Access is obsessed with them and puts them everywhere for no reason so you always end up with too many.

Indeed I think you could leave them all out except those in the Now function and the subquery delimiters.

 

[Myysterio]

There is exactly 7 of each parenthesis. And it doesnt work without them. Especially since I want to subtract before I divide.

 

[Galaxiom]

Sorry, blurred vision and I got sucked in by the error message referring to parentheses.

 

[Myysterio]

I'll take what I can get.... specially since this error is so obnoxious.

 

[Galaxiom]

Maybe the message is misleading.
My understanding is that sql doesn't work as a control source because it is returning a value for every record in the field rather than a single number.

Should work if you use DCount instead.

=DCount("*","[table]","[req]=Yes and [comp]=0 and ((Now()-[arrivaldate])/30.4)>=0 and ((Now()-[arrivaldate])/30.4)<=4 and [status]='UNQAL'")

 

[Galaxiom]

Actually a better is way is to derive a yes/no field in the report's record source query using:

 IIF([req]=Yes and [comp]=0 and ((Now()-[arrivaldate])/30.4)>=0 and ((Now()-[arrivaldate])/30.4)<=4 and [status]="UNQAL"),True, False) AS somefieldname

Then in the report header:

Abs(Sum(somefieldname))

Since True = -1 this give a count of the true records.

 

[Myysterio]

Thanks for the help, I'll let you know how it works. I go back into work tonight.

 

[Myysterio]

=dcount is getting me a bunch of "0"s

=IIf([afsc]="2a5x1","N/A",
DCount("*","1-Primary Table","[phaseonereq]=Yes
and [phaseonecomp]=0
and ((Now()-[arrivaldate])/30.4)>=0
and ((Now()-[arrivaldate])/30.4)<=2
and [phaseonestatus]='UNQAL'"))

-->Criteria Changed Slightly

 

[Myysterio]

Does my issue stem from trying to find a range from 0 to 2 out of a date function?

 

[Galaxiom]

I am not clear on your goal and I think this is leading to confusion.

I assumed you were trying to get a count of the records meeting the conditions to display in a textbox in the header of a report. I thought this because you started your original code with an equals sign and this is where one uses an equals sign.

If you were deriving a field in the table then there would be no equals sign and the sql would include "AS somefieldname" for the derived field.

Re-reading your first post makes me still think you want the number of records meeting the conditions. The DCount would be the expression in the textbox control source. It counts the number of records meeting the condition in the DCount's third argument.

But I believe the second technique is a better way becuase it uses less memory and should run faster. It will also work on data accessed on an SQL server with PassThrough queries but I guess that is not important right now.

This technique derives an extra field (somefieldname) in the record source query using the IIF expression. For each record this field would have True (-1) if the conditions are met and false (0) if they are not met.

The =Abs(Sum(somefieldname)) goes in the control source of the textbox to be displayed in the header of the report and shows the number of records meeting the condition. The Sum part adds all the records and the Abs changes this negative total to a positive value hence coming up with the number of True values.

Hope this makes sense.

 

[Myysterio]

I am attempting to make a text box on a report count the number of records that match the current Grouping and then display it.

Currently, Speed is not my concern, I am dealing with a database that does so little it doesnt even really matter. I just want it to be easy, robust and repeatable as I am going to need to set this range up for 4-5 courses each of which has 5 date ranges.

0-2 months
2-4 months
4-6 months
6-8 months
8+ months

I wanted decimals in my date difference equations because hitting 4 months would make it appear in two different reports unless i used >= and = in one report and repeated it a bunch of times.

 

[Myysterio]

=IIf([afsc]="2a5x1", "N/A", 'I want the box to report "N/A" when the grouping is "2a5x1"
DCount("*","1-Primary Table", 'table I am counting records on is "1-Primary Table"
"[phaseonereq]=Yes 'I have lots of conditions to each record counting
and [phaseonecomp]=0
and ((Now()-[arrivaldate])/30.4)>=0
and ((Now()-[arrivaldate])/30.4)<=2
and [phaseonestatus]='UNQAL'"))

I hope this may clear it up a little.

Report is grouped on [AFSC]

 

[Myysterio]

how do you make datediff() that =1 be >0 and <2? Apparently there is some conversion issues with this formula.