Welcome to SPN

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

Sign Up Now!

Another "Invalid use of Null"

Discussion in 'Information Technology' started by KellyS, Nov 18, 2005.

  1. KellyS

    KellyS
    Expand Collapse
    Guest

    I read somewhere that you cannot use any data type other than Variant to have
    a null value, not String, Date, or anything. So, how do you have a "Null" in
    a date field? My particular date is not necessary for all records and I do
    not want anything to display in the textbox. Do you define the variable as a
    "Date" or "Variant"?

    I'm stumped. I have looked through the other posts and did not find anything
    similar to my situation.

    Thanks, in advance, for any help.
     
  2. Loading...

    Similar Threads Forum Date
    Sikhism More Than Just Another Religion New to Sikhism Jul 25, 2016
    Yet Another Show Of Support For Sikhs From Canada! Breaking News Apr 28, 2016
    Another funny story, and Rory is castrated Blogs Oct 17, 2015
    Another week, another wolf Blogs Oct 17, 2015
    easy, develop another addiction! Blogs Oct 16, 2015

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    You can store a Null value in a Date/Time field in Access.
    You cannot store a Null value in a Date variable in VBA code.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "KellyS" <KellyS@discussions.microsoft.com> wrote in message
    news:F0F138BD-67EC-4680-90D1-FF3C3B612069@microsoft.com...
    >I read somewhere that you cannot use any data type other than Variant to
    >have
    > a null value, not String, Date, or anything. So, how do you have a "Null"
    > in
    > a date field? My particular date is not necessary for all records and I do
    > not want anything to display in the textbox. Do you define the variable as
    > a
    > "Date" or "Variant"?
    >
    > I'm stumped. I have looked through the other posts and did not find
    > anything
    > similar to my situation.
    >
    > Thanks, in advance, for any help.
     
  4. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Allen Browne wrote:
    > You cannot store a Null value in a Date variable in VBA code.


    It depends what you mean by 'Null'. A VBA variable of type Date can be
    'empty':

    Sub test()
    Dim dDate As Date
    MsgBox CStr(CBool(dDate = vbEmpty))

    dDate = Now
    MsgBox CStr(CBool(dDate = vbEmpty))

    dDate = vbEmpty
    MsgBox CStr(CBool(dDate = vbEmpty))
    End Sub
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    <peregenem@jetemail.net> wrote in message
    news:1132240585.720679.256610@g49g2000cwa.googlegroups.com
    > Allen Browne wrote:
    >> You cannot store a Null value in a Date variable in VBA code.

    >
    > It depends what you mean by 'Null'. A VBA variable of type Date can be
    > 'empty':
    >
    > Sub test()
    > Dim dDate As Date
    > MsgBox CStr(CBool(dDate = vbEmpty))
    >
    > dDate = Now
    > MsgBox CStr(CBool(dDate = vbEmpty))
    >
    > dDate = vbEmpty
    > MsgBox CStr(CBool(dDate = vbEmpty))
    > End Sub


    Not really; that's just a confusing type conversion:

    dDate = vbEmpty
    MsgBox Format(dDate, "long date")

    dDate = CDate("30-Dec-1899 12:00:00 AM")
    MsgBox Format(dDate, "long date") & " " & _
    CLng(dDate) & " " & _
    vbEmpty

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

    (please reply to the newsgroup)
     
  6. KellyS

    KellyS
    Expand Collapse
    Guest

    Yes, I just want the field to be empty.

    Thanks for your responses.

    "peregenem@jetemail.net" wrote:

    >
    > Allen Browne wrote:
    > > You cannot store a Null value in a Date variable in VBA code.

    >
    > It depends what you mean by 'Null'. A VBA variable of type Date can be
    > 'empty':
    >
    > Sub test()
    > Dim dDate As Date
    > MsgBox CStr(CBool(dDate = vbEmpty))
    >
    > dDate = Now
    > MsgBox CStr(CBool(dDate = vbEmpty))
    >
    > dDate = vbEmpty
    > MsgBox CStr(CBool(dDate = vbEmpty))
    > End Sub
    >
    >
     
  7. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Dirk Goldgar wrote:
    > Not really; that's just a confusing type conversion:
    >
    > dDate = vbEmpty
    > MsgBox Format(dDate, "long date")
    >
    > dDate = CDate("30-Dec-1899 12:00:00 AM")
    > MsgBox Format(dDate, "long date") & " " & _
    > CLng(dDate) & " " & _
    > vbEmpty


    I think I mean Empty - no matter.

    An alternative is to use an ADO object:

    Sub NullableDates()

    ' Single value
    Dim oDate As ADODB.Parameter

    Set oDate = New ADODB.Parameter
    oDate.Type = adDate

    MsgBox CStr(CBool(oDate.Value = Empty))

    oDate.Value = Now
    MsgBox CStr(CBool(oDate.Value = Empty))

    oDate.Value = Empty
    MsgBox CStr(CBool(oDate.Value = Empty))

    ' Array values
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    With rs
    .Fields.Append "my_date", adDate, , adFldIsNullable
    .Open
    .AddNew "my_date", Empty
    .AddNew "my_date", CDate("2005-10-01")

    .MoveFirst
    MsgBox CStr(CBool(.Fields("my_date").Value = Empty))

    .MoveNext
    MsgBox CStr(CBool(.Fields("my_date").Value = Empty))

    .Fields("my_date").Value = Empty
    MsgBox CStr(CBool(.Fields("my_date").Value = Empty))
    End With

    End Sub

    Probably the best way is to use a date with an actual value but one
    that is implausible in the data model, preferably an unambiguous one
    (i.e. for format dd/mm/ccyy, where the dd element is greater than 12
    and the yy element is greater than 31).
     
  8. RoyVidar

    RoyVidar
    Expand Collapse
    Guest

    peregenem@jetemail.net wrote in message
    <1132244699.262450.135120@z14g2000cwz.googlegroups.com> :
    > Dirk Goldgar wrote:
    >> Not really; that's just a confusing type conversion:
    >>
    >> dDate = vbEmpty
    >> MsgBox Format(dDate, "long date")
    >>
    >> dDate = CDate("30-Dec-1899 12:00:00 AM")
    >> MsgBox Format(dDate, "long date") & " " & _
    >> CLng(dDate) & " " & _
    >> vbEmpty

    >
    > I think I mean Empty - no matter.
    >
    > An alternative is to use an ADO object:
    >
    > Sub NullableDates()
    >
    > ' Single value
    > Dim oDate As ADODB.Parameter
    >
    > Set oDate = New ADODB.Parameter
    > oDate.Type = adDate
    >
    > MsgBox CStr(CBool(oDate.Value = Empty))
    >
    > oDate.Value = Now
    > MsgBox CStr(CBool(oDate.Value = Empty))
    >
    > oDate.Value = Empty
    > MsgBox CStr(CBool(oDate.Value = Empty))
    >
    > ' Array values
    > Dim rs As ADODB.Recordset
    > Set rs = New ADODB.Recordset
    > With rs
    > .Fields.Append "my_date", adDate, , adFldIsNullable
    > .Open
    > .AddNew "my_date", Empty
    > .AddNew "my_date", CDate("2005-10-01")
    >
    > .MoveFirst
    > MsgBox CStr(CBool(.Fields("my_date").Value = Empty))
    >
    > .MoveNext
    > MsgBox CStr(CBool(.Fields("my_date").Value = Empty))
    >
    > .Fields("my_date").Value = Empty
    > MsgBox CStr(CBool(.Fields("my_date").Value = Empty))
    > End With
    >
    > End Sub
    >
    > Probably the best way is to use a date with an actual value but one
    > that is implausible in the data model, preferably an unambiguous one
    > (i.e. for format dd/mm/ccyy, where the dd element is greater than 12
    > and the yy element is greater than 31).


    The help file states the following on empty:
    "Indicates that no beginning value has been assigned to a Variant
    variable. An Empty variable is represented as 0 in a numeric context
    or a zero length string ("") in a string context"

    Why should one then be surprised when comparing 0 (a numeric)
    with empty and get True?

    Using a magic date is not something I'd do. I'd prefer Null for
    unknown or input actual dates.

    --
    Roy-Vidar
     
  9. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    While you can not assign a Null value to a Date/Time *variable*, you can
    store a Null value in a Date/Time *field* provided it is not a required
    field. You should define your field as a Date/Time field. When you need to
    assign the value of that field to a variable, you can ...

    1) Use a query to select only records that do not contain Null values in the
    field.

    2) Use the NZ or IIF functions to return a default value when the field is
    Null.

    3) Use the IsNull function to check for Null values before attempting to
    assign values to a Date/Time variable.

    4) Use a Variant variable.

    All four techniques have appropriate uses.

    --
    Brendan Reynolds


    "KellyS" <KellyS@discussions.microsoft.com> wrote in message
    news:F0F138BD-67EC-4680-90D1-FF3C3B612069@microsoft.com...
    >I read somewhere that you cannot use any data type other than Variant to
    >have
    > a null value, not String, Date, or anything. So, how do you have a "Null"
    > in
    > a date field? My particular date is not necessary for all records and I do
    > not want anything to display in the textbox. Do you define the variable as
    > a
    > "Date" or "Variant"?
    >
    > I'm stumped. I have looked through the other posts and did not find
    > anything
    > similar to my situation.
    >
    > Thanks, in advance, for any help.
     

Share This Page