Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

avoiding 31/12/1899 with empty dates

Discussion in 'Information Technology' started by RB Smissaert, Jul 28, 2006.

  1. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Avoiding Negative Vibrations Spiritual Articles Oct 19, 2005

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:OpbrHl5dGHA.4576@TK2MSFTNGP05.phx.gbl...
    > 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
     
  4. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

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

    RBS

    "Arvin Meyer [MVP]" <a@m.com> wrote in message
    news:%23B8Zvp5dGHA.1208@TK2MSFTNGP02.phx.gbl...
    > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:OpbrHl5dGHA.4576@TK2MSFTNGP05.phx.gbl...
    >> 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

    >
    >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    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" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:OhgPpD6dGHA.3556@TK2MSFTNGP02.phx.gbl...
    > Thanks, how do I remove the 0 default in code?
    >
    > RBS
    >
    > "Arvin Meyer [MVP]" <a@m.com> wrote in message
    > news:%23B8Zvp5dGHA.1208@TK2MSFTNGP02.phx.gbl...
    >> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:OpbrHl5dGHA.4576@TK2MSFTNGP05.phx.gbl...
    >>> 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

    >>
    >>

    >
     
  6. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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$oI6dGHA.1792@TK2MSFTNGP03.phx.gbl...
    > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:OhgPpD6dGHA.3556@TK2MSFTNGP02.phx.gbl...
    >> Thanks, how do I remove the 0 default in code?
    >>
    >> RBS
    >>
    >> "Arvin Meyer [MVP]" <a@m.com> wrote in message
    >> news:%23B8Zvp5dGHA.1208@TK2MSFTNGP02.phx.gbl...
    >>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:OpbrHl5dGHA.4576@TK2MSFTNGP05.phx.gbl...
    >>>> 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
    >>>
    >>>

    >>

    >
    >
     
  7. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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$oI6dGHA.1792@TK2MSFTNGP03.phx.gbl...
    > 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:OhgPpD6dGHA.3556@TK2MSFTNGP02.phx.gbl...
    >> Thanks, how do I remove the 0 default in code?
    >>
    >> RBS
    >>
    >> "Arvin Meyer [MVP]" <a@m.com> wrote in message
    >> news:%23B8Zvp5dGHA.1208@TK2MSFTNGP02.phx.gbl...
    >>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:OpbrHl5dGHA.4576@TK2MSFTNGP05.phx.gbl...
    >>>> 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
    >>>
    >>>

    >>

    >
    >
     
  8. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

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

    RBS

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:OrOpqZ6dGHA.4276@TK2MSFTNGP03.phx.gbl...
    > 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$oI6dGHA.1792@TK2MSFTNGP03.phx.gbl...
    >> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >> news:OhgPpD6dGHA.3556@TK2MSFTNGP02.phx.gbl...
    >>> Thanks, how do I remove the 0 default in code?
    >>>
    >>> RBS
    >>>
    >>> "Arvin Meyer [MVP]" <a@m.com> wrote in message
    >>> news:%23B8Zvp5dGHA.1208@TK2MSFTNGP02.phx.gbl...
    >>>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>> news:OpbrHl5dGHA.4576@TK2MSFTNGP05.phx.gbl...
    >>>>> 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
    >>>>
    >>>>
    >>>

    >>
    >>

    >
     
  9. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

    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" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:eEHDcb6dGHA.3484@TK2MSFTNGP04.phx.gbl...
    > Got it now.
    > If I do:
    > vArray(i, c) = "" instead of: vArray(i, c) = vbNull
    > It works.
    >
    > RBS
    >
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:OrOpqZ6dGHA.4276@TK2MSFTNGP03.phx.gbl...
    >> 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$oI6dGHA.1792@TK2MSFTNGP03.phx.gbl...
    >>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>> news:OhgPpD6dGHA.3556@TK2MSFTNGP02.phx.gbl...
    >>>> Thanks, how do I remove the 0 default in code?
    >>>>
    >>>> RBS
    >>>>
    >>>> "Arvin Meyer [MVP]" <a@m.com> wrote in message
    >>>> news:%23B8Zvp5dGHA.1208@TK2MSFTNGP02.phx.gbl...
    >>>>> 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" <bartsmissaert@blueyonder.co.uk> wrote in message
    >>>>> news:OpbrHl5dGHA.4576@TK2MSFTNGP05.phx.gbl...
    >>>>>> 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
    >>>>>
    >>>>>
    >>>>
    >>>
    >>>

    >>

    >
     
  10. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    news:%23ykrPO6dGHA.1456@TK2MSFTNGP04.phx.gbl
    > 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)
     
  11. RB Smissaert

    RB Smissaert
    Expand Collapse
    Guest

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

    RBS

    "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in message
    news:ur5kOD7dGHA.3932@TK2MSFTNGP05.phx.gbl...
    > "RB Smissaert" <bartsmissaert@blueyonder.co.uk> wrote in message
    > news:%23ykrPO6dGHA.1456@TK2MSFTNGP04.phx.gbl
    >> 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)
    >
    >
     

Share This Page