[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Making a field required

Posted on 2011-05-04
10
Medium Priority
?
220 Views
Last Modified: 2012-06-27
I have a date field in both sql server and MIcrosoft access databases that  needed now to be converted to a required fiield. It was'nt before.
What would happen to the table if I convert this to be required  and for rows that were left null previousl to convert it? would the table accept the changes?
0
Comment
Question by:zachvaldez
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35690220
It will not allow you to do so if you have null values in the column.
What you can do is update null to some default value and then you will be able to make them required/not null
0
 
LVL 14

Expert Comment

by:athomsfere
ID: 35690277
Exactly as IJZ said.

You can make it default value.
0
 

Author Comment

by:zachvaldez
ID: 35690337
What appropriate default date value to enter  since many rows were ignored and left blank for years?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 16

Expert Comment

by:Imran Javed Zia
ID: 35690360
Please provide some discription
but in general you can use empty string or space for text data
0 for numeric
0/false/no for bool
and for datetime you may use some old native date or timestamp filed if there is in the table
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35690373
If all the data is being entered via Access forms, another way to do this would be to modify the form and place a check in the BeforeUpdate event to identify if the field is a valid date, and if not, force the user to enter a date before saving the current record.
0
 
LVL 12

Expert Comment

by:Mohamed Abowarda
ID: 35690843
You don't have to set a value for the field you want to make required, leave the database as it is (don't change the field), and simply check when you execute INSERT sql statement if the field is not null, if null you can show error message and don't insert.
0
 

Author Comment

by:zachvaldez
ID: 35690903
it has to be isdate and i null for checking. IN access form should this be in the form_beforeupdate event?
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 35690966
I would look to make the requirements on the application side instead of disallowing nulls.  
Your application will still need to ensure the data follows any business rules.  It's easier to consume nulls on outputs also because it is not specific to a data type like if you put in blank space for a text field and a really old date for a datetime field.
0
 
LVL 12

Accepted Solution

by:
Mohamed Abowarda earned 1000 total points
ID: 35691007
If you used database required attribute, you MUST enter a value, the value first in the null fields, the value will depend on the field usage, that's why I recommend you to keep the database as it's without changing the field to required, and make the field required from your application when the user try to insert a new row not your database.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35696982
It will not save the converted changes. It will ask to drop table and create again.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

867 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