Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

find columns that cannot be NULL

is there a query that can check which all columns in tables that are designed to be not nullable.

thanks
0
anushahanna
Asked:
anushahanna
  • 3
  • 2
  • 2
  • +1
5 Solutions
 
cquinnCommented:
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
 
MarioAlcaideCommented:
I don't think it can be done in Access with a query, it could be done if you use Oracle for example
0
 
peter57rCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
anushahannaAuthor Commented:
where can i find the Database documentor tool (in Design?)
0
 
anushahannaAuthor Commented:
peter, how can you run the vba as a standalone (like a query) without a click event?
0
 
peter57rCommented:
Just change the table name in my testfn() and run that sub.
0
 
cquinnCommented:
The database documenter is in the Database Tools tab on the main menu bar
0
 
anushahannaAuthor Commented:
thanks all!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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