Problem with DCount in Query

[Kobs]

I have an Access Table, entitled AVL. One of the Field Names/Columns is Certify Exp Date. In this column, my records either contain a date or are left empty/null. In my query, I want to count the number of records that have a date in that field and another count of the number of records that are empty/null. I have looked into the DCount function but I am still completely lost. Please advice.

Thanks,

-Kobs

 

[GoDawgs]

What else is in this query? Dcount is better used for a report or as criteria in a query and not really to get a count total in a query. Give me a little more info and maybe I can help.

Kevin

 

[Kobs]

Kevin,

The table itself has about 50 fields overall with text fields (such as company name, address, etc), numerical fields (statistics such as accuracy rating, errors, receipts), and a few date/time fields (date survey was sent, and the afformentioned Certify Exp Date).... I have run numerous queries under different criteria for the sole purpose of generating reports.

The query that is casuing the headache is one that features both the name and number of a company (both text fields), and the date the company's certification expires (the Certify Exp Date date/time field). I want the query to run a count of how many companies have a date in the Certify Exp Date field and the number of companies that do not have a date in that field. From there, I would like to generate a report of the results of the query (I'm assuming easily via the Report Wizard)

I'm hoping this gives you a better vision of my problem

Thanks,

Ken

 

[GoDawgs]

I'm thinking this would require two separate queries...although I may still be a little confused. Honestly your best option would probably be to just create the report with all the other data you want, and then use the dcount function in a text box on the report. Those dcounts would look something like this I think:

=dcount("[Certify Exp Date]","AVL","[Certify Exp Date]=''&quot

and

=dcount(&quot;[Certify Exp Date]&quot;,&quot;AVL&quot;,&quot;[Certify Exp Date]<>''&quot

you might have to use &quot;is null&quot; and &quot;is not null&quot; instead of the ='' part, but I'm not sure. Hope this helps.

Kevin

 

[Kobs]

I tried putting the function in the report like you suggested. However when I tried

=DCount(&quot;[Certify Exp Date]&quot;,&quot;AVL&quot;,&quot;[Certify Exp Date]=&quot;&quot;&quot

or

=DCount(&quot;[Certify Exp Date]&quot;,&quot;AVL&quot;,&quot;[Certify Exp Date]=<>&quot

I received #Error

Any other insights?

- Ken

 

[GoDawgs]

Make sure you're using apostrophes (single quote) inside the double quotes...kinda confusing...but it needs to be:

=DCount(&quot;[Certify Exp Date]&quot;,&quot;AVL&quot;,{quotation marks}[Certify Exp Date]={apostrophe}{apostrophe}{quotation marks})


Hope that makes sense. Try the Is Null instead if you still can't get this to work (&quot;[certify exp date] is null&quot