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
LVL 6
anushahannaAsked:
Who is Participating?
 
cquinnConnect With a Mentor Commented:
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
 
MarioAlcaideConnect With a Mentor Commented:
I don't think it can be done in Access with a query, it could be done if you use Oracle for example
0
 
peter57rConnect With a Mentor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
peter57rConnect With a Mentor Commented:
Just change the table name in my testfn() and run that sub.
0
 
cquinnConnect With a Mentor Commented:
The database documenter is in the Database Tools tab on the main menu bar
0
 
anushahannaAuthor Commented:
thanks all!
0
All Courses

From novice to tech pro — start learning today.