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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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

RBS

"Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>
>

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
http://I.Am/DougSteele
(no private e-mails, please)


"RB Smissaert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks, how do I remove the 0 default in code?
>
> RBS
>
> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>
>>
>

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23zL$(E-Mail Removed)...
> 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
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks, how do I remove the 0 default in code?
>>
>> RBS
>>
>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>
>>>
>>
>
>

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:%23zL$(E-Mail Removed)...
> 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
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks, how do I remove the 0 default in code?
>>
>> RBS
>>
>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>
>>>
>>
>
>

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

RBS

"RB Smissaert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:%23zL$(E-Mail Removed)...
>> 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
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "RB Smissaert" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Thanks, how do I remove the 0 default in code?
>>>
>>> RBS
>>>
>>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>>> news:%(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>
>>>>
>>>
>>
>>
>

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

"RB Smissaert" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Got it now.
> If I do:
> vArray(i, c) = "" instead of: vArray(i, c) = vbNull
> It works.
>
> RBS
>
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>> news:%23zL$(E-Mail Removed)...
>>> 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
>>> http://I.Am/DougSteele
>>> (no private e-mails, please)
>>>
>>>
>>> "RB Smissaert" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Thanks, how do I remove the 0 default in code?
>>>>
>>>> RBS
>>>>
>>>> "Arvin Meyer [MVP]" <(E-Mail Removed)> wrote in message
>>>> news:%(E-Mail Removed)...
>>>>> 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" <(E-Mail Removed)> wrote in message
>>>>> news:(E-Mail Removed)...
>>>>>> 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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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

RBS

"Dirk Goldgar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "RB Smissaert" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)
>> 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.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>