Count Records that are not " "

[yleeSF]

Hello!

Query:
Fields
ID # 12345
03/06/04: IIF ([3/6/04]>39.5," ",[3/6/04])
03/08/04: Same as above

Report: Tied to Query Above.

Trying to count all 3/6/04, 3/8/04 records that do not contain " ". Is this possible? I've tried the following formula in my report: Count([3/6/04]). The result counts the " " as a record.

Any help would be appreciated!!!

 

[BobJacksonNCI]

If you will create your query in design view, make sure it runs, bring up SQL View and then cut/paste the SQL here, someone can get you fixed up.

Also, if the records contained Null instead of " "
Count would automatically exclude them.

Before you change your table design and run an update query, think about how Nulls could affect other objects. See the following for more info:

http://members.iinet.net.au/~allenbrowne/casu-12.html

HTH,
Bob

 

[yleeSF]

Hello!

Thanks for your prompt response! Here's the SQL code for my Query. Again, I am trying to create a calculated control in MS Access Report to Count the records without "". Let me know your thoughts.....

SELECT DISTINCT dbo_Store.region AS [Zone], dbo_Store.district, pay2.STORE_NUM, pay2.EMP_NUM, pay2.FNAME, pay2.LNAME, pay2.END_ON, IIf([3/6/2004]>39.5,"",[3/6/2004]) AS [3/06/04], IIf([3/13/2004]>39.5,0,[3/13/2004]) AS [3/13/04], IIf([3/20/2004]>39.5,"",[3/20/2004]) AS [3/20/04], IIf([3/27/2004]>39.5,"",[3/27/2004]) AS [3/27/04], IIf([4/3/2004]>39.5,0,[4/3/2004]) AS [4/3/04], IIf([4/10/2004]>39.5,0,[4/10/2004]) AS [4/10/04], IIf([4/17/2004]>39.5,0,[4/17/2004]) AS [4/17/04], IIf([4/24/2004]>39.5,0,[4/24/2004]) AS [4/24/04]
FROM (pay2 RIGHT JOIN [TOTAL ALL] ON pay2.EMP_NUM = [TOTAL ALL].EMP_NUM) LEFT JOIN dbo_Store ON pay2.STORE_NUM = dbo_Store.store
WHERE (((pay2.END_ON)=#4/24/2004#))
ORDER BY dbo_Store.region, dbo_Store.district, pay2.STORE_NUM;

 

[dhookom]

To count the number of times a condition is true in a group or report footer section, use an expression like:
=Sum(Abs( [Red]{YourExpressionHere}[/red] ))

If this doesn't help then come on back. I noticed you stated [Red]do not contain " "[/red]. Your expression however contains [Red]""[/red]. These are not the same value. You would need to be more accurate with your question. There is a huge difference between a space, as zero-length-string, and Null.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[yleeSF]

HEllO!!!

Thanks again for your reply. I tried the expression, but it does not work. I receive the error Data Type Mismatch in expression.

To clarify, I am using "". In my Query, my immediate If Function returns "", if the hours are > than 39.5. Otherwise, it returns the hours.

I simply would like my Access Report to Count the records without the "". Is this possible?

Thank you so much for your help!!!

 

[yleeSF]

Hello Again!

In thinking more about your comments regarding NUll values, I was able to resolve my problem. Basically, I changed my query where if hours is > 39.5, I return NULL. Since the Count function in the report only counts NON-NULL values, it counts all hours less than 39.5 correctly.

Thanks Again!!! =)