Solved

find columns that cannot be NULL

Posted on 2011-03-02
8
233 Views
Last Modified: 2012-05-11
is there a query that can check which all columns in tables that are designed to be not nullable.

thanks
0
Comment
Question by:anushahanna
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 15

Accepted Solution

by:
cquinn earned 200 total points
ID: 35015771
I don't know how to do it in a query, but the Database documentor tool (on the database tools tab) will show this information
0
 
LVL 4

Assisted Solution

by:MarioAlcaide
MarioAlcaide earned 100 total points
ID: 35015782
I don't think it can be done in Access with a query, it could be done if you use Oracle for example
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 200 total points
ID: 35015842
Can't be done in sql.

You can use a vba function...

Function testreq(tbl As String) As String
Dim reqlist As String
Dim db As Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb
Set tdf = db.TableDefs(tbl)
For Each fld In tdf.Fields
If fld.Required = True Then reqlist = reqlist & fld.Name & ","
Next fld

Set tdf = Nothing
Set db = Nothing
If Len(reqlist) > 1 Then
testreq = Left(reqlist, Len(reqlist) - 1)
Else
testreq = reqlist
End If
End Function

Sub testfn()
MsgBox testreq("products")
End Sub
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Author Comment

by:anushahanna
ID: 35015868
where can i find the Database documentor tool (in Design?)
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35015869
peter, how can you run the vba as a standalone (like a query) without a click event?
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 200 total points
ID: 35016059
Just change the table name in my testfn() and run that sub.
0
 
LVL 15

Assisted Solution

by:cquinn
cquinn earned 200 total points
ID: 35016190
The database documenter is in the Database Tools tab on the main menu bar
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35150218
thanks all!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can you open the FORM2 2 31
Top 1 of each supplier 55 56
Access report groups with sums 5 26
Switch 5 15
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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