• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Access 2007 Query to get List of IsNull Fields

Hello all!

New project in Access that I am trying to get a list of all records that a particular field IsNull, blank or Empty.

The query is based upon table tblCOA.  That table (tblCOA) has a field that is linked to another table tblAcctntCOA.  I am trying to get a list of just the records in tblCOA that have not had a value selected.

tblCOA
Fields
ClientGLAcct
ClientGLDescription
AcctntGLAcct#
(Lookup Field to tblAcctntCOA)

tblAcctntCOA
AcctntGLAcct#
AcctntGLDescription

In a query of tblCOA I selected AcctntGLAcct# an used the Criteria:
=ISNull({AcctntGLAcct#)

However I don't get any results.  I know that currently all of the records in tblCOA except 2 have a value.


0
wlwebb
Asked:
wlwebb
  • 5
  • 3
1 Solution
 
Gustav BrockCIOCommented:
Try this:

Select Count(*) As CountOfNullRows
From tblCOA
Where [AcctntGLAcct#] Is Null;

/gustav
0
 
wlwebbAuthor Commented:
Get "Enter Parameter Value"
0
 
wlwebbAuthor Commented:
Ignore the last Comment.  I had a space in AcctntGLAcct#  Field is Acctnt GL Acct#
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
wlwebbAuthor Commented:
That gives me the "Count" of the records with NullRows.  I am wanting to get a List of accounts with Null
0
 
Gustav BrockCIOCommented:
Oh, then try:

Select ClientGLAcct
From tblCOA
Where [AcctntGLAcct#] Is Null;

/gustav
0
 
wlwebbAuthor Commented:
Tried this and still get a blank list instead of a list with the 2 blank records

SELECT tblCOA.ClientGLAcct, tblCOA.ClientGLDescription, tblCOA.[Acctnt GL Acct#]
FROM tblAcctntCOA INNER JOIN tblCOA ON tblAcctntCOA.[Acctnt GL Nbr#] = tblCOA.[Acctnt GL Acct#]
WHERE (((tblCOA.[Acctnt GL Acct#]) Is Null))
GROUP BY tblCOA.ClientGLAcct, tblCOA.ClientGLDescription, tblCOA.[Acctnt GL Acct#];

Still don't get a list
0
 
wlwebbAuthor Commented:
That got it.  Thanks!
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now