Solved

If Null and Dcount

Posted on 2013-06-29
6
389 Views
Last Modified: 2013-07-05
In the below, if Dcount is >0  then the return will either be "Yes" or "-".  I want to add a criteria if Dcount is Null then return " " (a blank) to show me that there are no records.  I think I need to add another criteria?  Possibly Switch?  

ExpctDepart: IIf(DCount("*","qryDepartures_3","ProjectID= " & [Projects.ID])>0,"Yes","-")

qryDepartures_3:
SELECT tblDepartures.ProjectID, tblDepartures.DraftedDocYN, tblDepartures.ConfirmedDepartureYN
FROM tblDepartures
WHERE (((tblDepartures.ConfirmedDepartureYN) Is Null Or (tblDepartures.ConfirmedDepartureYN)=True))
GROUP BY tblDepartures.ProjectID, tblDepartures.DraftedDocYN, tblDepartures.ConfirmedDepartureYN
HAVING (((tblDepartures.DraftedDocYN)="Yes" Or (tblDepartures.DraftedDocYN) Is Null));


thank you
0
Comment
Question by:pdvsa
  • 3
  • 2
6 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 39286724
DCOUNT will never be NULL.  It will always return a numeric value.
0
 

Author Comment

by:pdvsa
ID: 39286731
Hi fyed, maybe i could use =0 somehow?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39286741
You probably think of something like this:

ExpctDepart: Format(DSum("Abs([DraftedDocYN])","qryDepartures_3","ProjectID= " & [Projects.ID]), "\Y\e\s;;\-;")

/gustav
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:pdvsa
ID: 39286779
Cactus.... Very funky looking.... Interesting.  I will test when back at computer.  Thank you
0
 

Author Comment

by:pdvsa
ID: 39286901
Cactus,

I get a "data type mismatch in criteria or expression" error when I run from qry design builder.  When I paste it, it changes it a bit to the below.  

ExpctDepart: Format(DSum("Abs([DraftedDocYN])","qryDepartures_3","ProjectID= " & [Projects.ID]),"""Yes"";;-")
                      ^^^^^^^^

Not sure if DraftedDocYN would be the problem.  It is not a true YN field...I have since then modified it to a combo value list with values of "Yes";"No";"previously issued";"Don’t Need it"

let me know what is next...
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 39287161
If DraftedDocYN is not a Boolean field (WHO could know), you will, of course, have to adjust.

Try starting with a Boolean field and:

ExpctDepart: DSum("Abs([DraftedDocYN])","qryDepartures_3","ProjectID= " & [Projects.ID] & "")

The apply the format.

/gustav
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now