July 15, 2017

How to configure Column default value in SQL Server in design View.

Recently I migrated my MS ACCESS database to SQL Database. The migration is a success until a user tried to print a report from her MS Access frontend. All the computation in a report is blank. It confusing because the code for the computation is correct and the field to calculate has a value and it returns no error only its show blank.

When I compare the field from my old previous MS Access database to new SQL database I now see what causes of that blank computation results. It seems that in my old database, any field with numeric data types has a default value of zero if the field hasn't specify any value, but in my new database it will be null. And so I need to change it to zero if value is not specify. In my old database the datatypes is numeric but when I converted it to SQL it become FLOAT data types.

 What I did to fix this is to enter a default value in all of the fields that have a FLOAT data types. I enter the value zero(0) in all float data types if the field does not contain any number, so in case the field has no value it should have zero in that field instead of null. When I do that MS access can now compute and all the computation in reports or form are fix.

This is how to add a default value in a column in SQL Server using design View.
So every time a new record is inserted it automatically add the default to the field you specify.

  1. Login to your SQL Server using SQL Management Studio.
  2. In your Object Explorer expand to your database and to your desired table.
  3. right click on your table then select Design
  4. click on choose the column you want to add default value
  5. then on column properties, navigate to default Value or Binding then enter the value you want.

No comments:

Post a Comment