Null Value prevents showing any results

[nick.h]

Hey,

I have a query:

SELECT "Prospecting Calls" AS VisitType, Abs(Count(VisitDate)) AS Monday
FROM VisitInfo
WHERE (((VisitInfo.VisitDate)=GetTraxDate()) AND ((VisitInfo.AddedBy)=GetUser()) AND ((VisitInfo.FormOfContact)=3))
GROUP BY "Prospecting Calls";

Where if there is a Count of 0 then it returns as Null. I would like to put this into a report where there are several similar queries, but if any of the Counts are 0 then I get a blank form. How do I get it to display 0???



Please Help!
Nick

[hertfordkc]

Can you tell if the problem is because the count is 0 or is it because some of the underlying VisitDate are null?
When there is a chance of a field containing a null value, I use NZ(var,0) to insure there is a zero value in the field. Same thing applies to empty fields, although I haven't suffered as much from empty fields as I have from nulls.

[nick.h]

The count is 0. The problem is that there were no records added on that day, and therefore there is nothing for it to count.

[hertfordkc]

How you solve this problem depends on where the query is in the report. As long as the query listed isn't the basis for the report, there should be a point at which you can intercept the null and take other action.
Can you put some dummy data in your database, compact and post it? Zip it if necessary. Since I have Access 2007, I'm not sure that I can help.

[nick.h]

The report is 100% based on the query :/

[hertfordkc]

Sounds like you will have to run the query from a form or macro, trap the null and doctor the data/query so the report will run.

[hertfordkc]

report is based. Use Dcount within the report to get the number you need.
Google 'access Dcount'.

[nick.h]

Yep! DCount is the way to go! Thank you!

I'm having issues with doing multiple Criteria. When I use:

=DCount("VisitDate","VisitInfo","VisitDate = GetTraxDate()+2" And "FormOfContact = '2'")

It simply counts everything, but when I remove the Part after "And" it filters properly. ???

Nick

[orange]

=DCount("VisitDate","VisitInfo","VisitDate = GetTraxDate()+2" And "FormOfContact = '2'")

Try the following

=DCount("VisitDate","VisitInfo","VisitDate = GetTraxDate()+2 And FormOfContact = '2'")

[nick.h]

It gave me #Error, but I'm going to kepe looking at your version and see if it's just a missing quote

[orange]

I was looking at a syntax error, now I see this part of your description

but when I remove the Part after "And" it filters properly. ???

which indicates that the And FormOfContact = '2' isn't required.

So try this
=DCount("VisitDate","VisitInfo","VisitDate = GetTraxDate()+2")

Does GetTraxDate() return a Date datatype?