avoiding 31/12/1899 with empty dates

[RB Smissaert]

In Excel VBA I am showing a variant array in an Access table.
All working nicely, except when there is a date column
with empty dates these empty dates will show in Access as
30/12/1899.

The format of this date field is set like this:

'doing dbText is better for the rows with a value as
'it will maintain the exact date format, but when there
'is no value you will get 30/12/1899, now it will be 00:00:00
'------------------------------------------------------------
SetPropertyDAO tdfNew.Fields(arrDateFormat(c)), _
"Format", _
dbDate, _
strDateFormat

strDateFormat is a string holding for example "dd/mmm/yyyy"

The table is setup like this (code snippet only):

'First, create the database.
Set dB1 = DBEngine.CreateDatabase(strAccessFile, dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = dB1.CreateTableDef(strSheetName)

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.

.Fields.Append .CreateField(strField, dbDate)


How do I avoid this and at the same time format the non-empty dates?

RBS

 

[Arvin Meyer [MVP]]

30Dec1899 is 0, not null or empty. If you look in your table, you will find
that the default for the column is 0. Remove that default and run an update
query:

UPDATE MyTable SET MyTable.[DateField] = Null
WHERE (((MyTable.[DateField])=0));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[RB Smissaert]

Thanks, how do I remove the 0 default in code?

RBS

30Dec1899 is 0, not null or empty. If you look in your table, you will
find
that the default for the column is 0. Remove that default and run an
update
query:

UPDATE MyTable SET MyTable.[DateField] = Null
WHERE (((MyTable.[DateField])=0));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In Excel VBA I am showing a variant array in an Access table.
All working nicely, except when there is a date column
with empty dates these empty dates will show in Access as
30/12/1899.

The format of this date field is set like this:

'doing dbText is better for the rows with a value as
'it will maintain the exact date format, but when there
'is no value you will get 30/12/1899, now it will be 00:00:00
'------------------------------------------------------------
SetPropertyDAO tdfNew.Fields(arrDateFormat(c)), _
"Format", _
dbDate, _
strDateFormat

strDateFormat is a string holding for example "dd/mmm/yyyy"

The table is setup like this (code snippet only):

'First, create the database.
Set dB1 = DBEngine.CreateDatabase(strAccessFile, dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = dB1.CreateTableDef(strSheetName)

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.

.Fields.Append .CreateField(strField, dbDate)


How do I avoid this and at the same time format the non-empty dates?

RBS

 

[Douglas J. Steele]

When you go into the table and see where the Default value is set to 0,
remove that default value. It should be pretty obvious when you look at the
field properties.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, how do I remove the 0 default in code?

RBS

30Dec1899 is 0, not null or empty. If you look in your table, you will
find
that the default for the column is 0. Remove that default and run an
update
query:

UPDATE MyTable SET MyTable.[DateField] = Null
WHERE (((MyTable.[DateField])=0));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In Excel VBA I am showing a variant array in an Access table.
All working nicely, except when there is a date column
with empty dates these empty dates will show in Access as
30/12/1899.

The format of this date field is set like this:

'doing dbText is better for the rows with a value as
'it will maintain the exact date format, but when there
'is no value you will get 30/12/1899, now it will be
00:00:00

'------------------------------------------------------------
SetPropertyDAO tdfNew.Fields(arrDateFormat(c)), _
"Format", _
dbDate, _
strDateFormat

strDateFormat is a string holding for example "dd/mmm/yyyy"

The table is setup like this (code snippet only):

'First, create the database.
Set dB1 = DBEngine.CreateDatabase(strAccessFile, dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = dB1.CreateTableDef(strSheetName)

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.

.Fields.Append .CreateField(strField, dbDate)


How do I avoid this and at the same time format the non-empty dates?

RBS

 

[RB Smissaert]

There was no default value of 0. There was no default value at all that I
could see.
I could loop through my array and do if arrvalue = 0 then
arrayvalue = vbNull.
But I understand that I then still need to set the default value for that
column to Null in code.
How do I do that, considering the posted code?

RBS

When you go into the table and see where the Default value is set to 0,
remove that default value. It should be pretty obvious when you look at
the field properties.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, how do I remove the 0 default in code?

RBS

30Dec1899 is 0, not null or empty. If you look in your table, you will
find
that the default for the column is 0. Remove that default and run an
update
query:

UPDATE MyTable SET MyTable.[DateField] = Null
WHERE (((MyTable.[DateField])=0));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In Excel VBA I am showing a variant array in an Access table.
All working nicely, except when there is a date column
with empty dates these empty dates will show in Access as
30/12/1899.

The format of this date field is set like this:

'doing dbText is better for the rows with a value as
'it will maintain the exact date format, but when there
'is no value you will get 30/12/1899, now it will be
00:00:00

'------------------------------------------------------------
SetPropertyDAO tdfNew.Fields(arrDateFormat(c)), _
"Format", _
dbDate, _
strDateFormat

strDateFormat is a string holding for example "dd/mmm/yyyy"

The table is setup like this (code snippet only):

'First, create the database.
Set dB1 = DBEngine.CreateDatabase(strAccessFile, dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = dB1.CreateTableDef(strSheetName)

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.

.Fields.Append .CreateField(strField, dbDate)


How do I avoid this and at the same time format the non-empty dates?

RBS

 

[RB Smissaert]

Have tried this:

.Fields.Append .CreateField(strField, dbDate)
.Fields(strField).DefaultValue = "Null"
arrDateFormat(c) = newFieldArray(c)
For i = 1 To URowCount
If vArray(i, c) = 0 Then
vArray(i, c) = vbNull
End If
Next

It does make the default value show as Null, but I still get the 30/12/1899
values.

Maybe I should run the Update query on the table, but I am not familiar with
Access and
looping through the array is easier and maybe faster.

RBS

When you go into the table and see where the Default value is set to 0,
remove that default value. It should be pretty obvious when you look at
the field properties.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, how do I remove the 0 default in code?

RBS

30Dec1899 is 0, not null or empty. If you look in your table, you will
find
that the default for the column is 0. Remove that default and run an
update
query:

UPDATE MyTable SET MyTable.[DateField] = Null
WHERE (((MyTable.[DateField])=0));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In Excel VBA I am showing a variant array in an Access table.
All working nicely, except when there is a date column
with empty dates these empty dates will show in Access as
30/12/1899.

The format of this date field is set like this:

'doing dbText is better for the rows with a value as
'it will maintain the exact date format, but when there
'is no value you will get 30/12/1899, now it will be
00:00:00

'------------------------------------------------------------
SetPropertyDAO tdfNew.Fields(arrDateFormat(c)), _
"Format", _
dbDate, _
strDateFormat

strDateFormat is a string holding for example "dd/mmm/yyyy"

The table is setup like this (code snippet only):

'First, create the database.
Set dB1 = DBEngine.CreateDatabase(strAccessFile, dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = dB1.CreateTableDef(strSheetName)

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.

.Fields.Append .CreateField(strField, dbDate)


How do I avoid this and at the same time format the non-empty dates?

RBS

 

[RB Smissaert]

Got it now.
If I do:
vArray(i, c) = "" instead of: vArray(i, c) = vbNull
It works.

RBS

Have tried this:

.Fields.Append .CreateField(strField, dbDate)
.Fields(strField).DefaultValue = "Null"
arrDateFormat(c) = newFieldArray(c)
For i = 1 To URowCount
If vArray(i, c) = 0 Then
vArray(i, c) = vbNull
End If
Next

It does make the default value show as Null, but I still get the
30/12/1899 values.

Maybe I should run the Update query on the table, but I am not familiar
with Access and
looping through the array is easier and maybe faster.

RBS

When you go into the table and see where the Default value is set to 0,
remove that default value. It should be pretty obvious when you look at
the field properties.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks, how do I remove the 0 default in code?

RBS

30Dec1899 is 0, not null or empty. If you look in your table, you will
find
that the default for the column is 0. Remove that default and run an
update
query:

UPDATE MyTable SET MyTable.[DateField] = Null
WHERE (((MyTable.[DateField])=0));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In Excel VBA I am showing a variant array in an Access table.
All working nicely, except when there is a date column
with empty dates these empty dates will show in Access as
30/12/1899.

The format of this date field is set like this:

'doing dbText is better for the rows with a value as
'it will maintain the exact date format, but when there
'is no value you will get 30/12/1899, now it will be
00:00:00

'------------------------------------------------------------
SetPropertyDAO tdfNew.Fields(arrDateFormat(c)), _
"Format", _
dbDate, _
strDateFormat

strDateFormat is a string holding for example "dd/mmm/yyyy"

The table is setup like this (code snippet only):

'First, create the database.
Set dB1 = DBEngine.CreateDatabase(strAccessFile, dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = dB1.CreateTableDef(strSheetName)

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.

.Fields.Append .CreateField(strField, dbDate)


How do I avoid this and at the same time format the non-empty dates?

RBS

 

[RB Smissaert]

Actually it looks I don't need to set the default value to Null.
The only thing needed is to alter the 0 values in the array
to "".
I can now also do:

SetPropertyDAO tdfNew.Fields(arrDateFormat(c)), _
"Format", _
dbText, _
strDateFormat

And that will give me now the exact date format and empty date values if
there is no date.
So, all solved now.

RBS

Got it now.
If I do:
vArray(i, c) = "" instead of: vArray(i, c) = vbNull
It works.

RBS

Have tried this:

.Fields.Append .CreateField(strField, dbDate)
.Fields(strField).DefaultValue = "Null"
arrDateFormat(c) = newFieldArray(c)
For i = 1 To URowCount
If vArray(i, c) = 0 Then
vArray(i, c) = vbNull
End If
Next

It does make the default value show as Null, but I still get the
30/12/1899 values.

Maybe I should run the Update query on the table, but I am not familiar
with Access and
looping through the array is easier and maybe faster.

RBS

When you go into the table and see where the Default value is set to 0,
remove that default value. It should be pretty obvious when you look at
the field properties.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks, how do I remove the 0 default in code?

RBS

30Dec1899 is 0, not null or empty. If you look in your table, you will
find
that the default for the column is 0. Remove that default and run an
update
query:

UPDATE MyTable SET MyTable.[DateField] = Null
WHERE (((MyTable.[DateField])=0));

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In Excel VBA I am showing a variant array in an Access table.
All working nicely, except when there is a date column
with empty dates these empty dates will show in Access as
30/12/1899.

The format of this date field is set like this:

'doing dbText is better for the rows with a value as
'it will maintain the exact date format, but when there
'is no value you will get 30/12/1899, now it will be
00:00:00

'------------------------------------------------------------
SetPropertyDAO tdfNew.Fields(arrDateFormat(c)), _
"Format", _
dbDate, _
strDateFormat

strDateFormat is a string holding for example "dd/mmm/yyyy"

The table is setup like this (code snippet only):

'First, create the database.
Set dB1 = DBEngine.CreateDatabase(strAccessFile, dbLangGeneral)

'Create a new TableDef object.
Set tdfNew = dB1.CreateTableDef(strSheetName)

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection database.

.Fields.Append .CreateField(strField, dbDate)


How do I avoid this and at the same time format the non-empty dates?

RBS

 

[Dirk Goldgar]

There was no default value of 0. There was no default value at all
that I could see.
I could loop through my array and do if arrvalue = 0 then
arrayvalue = vbNull.
But I understand that I then still need to set the default value for
that column to Null in code.

Please note: the defined constant vbNull is *not* the same as Null. In
fact, vbNull = 1. That constant is used to determine the data type of a
Variant, as returned by the VarType function; if a variant Foo has the
value Null, then VarType(Foo) = vbNull; that is, 1. However that does
*not* mean that Foo = 1.

 

[RB Smissaert]

Yes, I noticed vbNull is 1 when I saw it in the default value of that field.

RBS