Share via

How to save blank date in access database using Visual Studio 2012

  • Article

Question

Saturday, November 30, 2013 6:16 AM

Hi

I made a web research for two days and I cannot found a solution for this:

I have a Access 2013 database named 'Clients". In this database I have one table named 'Purchase'

This table has three fields:

1. cli_id (the code)

2. cli_name (text,40 positions)

3. cli_dtpu (date, dd/mm/yyyy)

I created a form in visual basic. In this form I added my database and drag and drop my table (details mode) and this generated a bindingnavigator that I use to add,delete, and save my records.

The problem is, when I add a record and I put a date value in the 'cli_dtpu' (date field) and save it , when I back to this record and I try clear this field, I cannot save it with blank value. I already tried to  create validation rules in Access, change the validation mode of the field 'cli_dtpu' in Visual Basic but without success.

My goal is to allow the user to fill in a date and later, when the user wants, erase this date.
How do I save a blank date in a Access database ? I would not want to change the field type to string, because I need that this field be a date field.

Thanks for your attention

All replies (2)

Saturday, November 30, 2013 6:59 AM âś…Answered | 1 vote

Saving a null value to a column in a database table is the same as  setting the column to no value (nothing -- blank). The definition of the column during table creation must be set to accept a null value as well as real value. So it's a null value that is going to be saved to the column that is a date column,  or a real date is going to be saved to the date column.

You have to know when you are dealing with a null value coming from a column or real data when reading the data by doing a null value check. If the value is null, then you are going to set the date field on the screen to a blank. And if it is is not null data,  then you are going to use the read date data. If that date field on the screen is blank, then you are going to write a null value to the date column in the table. Otherwise, you are going to write a date to the date column if the date field on the screen is not blank and a real date.

All I can tell is learn how to work with null data columns in regards to reading a null value  from a table column or saving a null value to a table column.

http://office.microsoft.com/en-us/access-help/about-working-with-blank-fields-in-queries-mdb-HP005188534.aspx

Saturday, November 30, 2013 1:32 PM

if your field is set to Nulls allowed, then you have to use the DBNull.Value

A datefield in Access can only be in your situation Nulls allowed.

Success
Cor