Re: Attn: Kevin How to save a null date to access database?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Re: Attn: Kevin How to save a null date to access database?

  1. #1
    Bill Guest

    Re: Attn: Kevin How to save a null date to access database?



    Kevin

    Here is my code with your line in it, I get a type mismatch when I run it

    If IsNull(EngSealsCopyDate.Value) Then
    temp_EngSealsCopyDate$ = ""
    Else
    temp_EngSealsCopyDate$ = Left(EngSealsCopyDate.Value, 10) ' get
    the date like this 02/29/2000
    temp_EngSealsCopyDate$ = Trim$(temp_EngSealsCopyDate$)
    End If


    If Len(temp_EngSealsCopyDate$) > 0 Then
    rs![Eng Seals Copy Date] = CDate(temp_EngSealsCopyDate$)

    Else

    ' rs![Eng Seals Copy Date] = CDate("")
    ' rs![Eng Seals Copy Date] = CDate(myKnullDate)
    'type mismatch here ----> 'rs![Eng Seals Copy Date] = "#" & Format$(EngSealsCopyDate.Value,
    "mm/dd/yyyy") & "#"
    'type mismatch or here ----> rs![Eng Seals Copy Date] = CDate(Format$(EngSealsCopyDate.Value,
    "mm/dd/yyyy"))
    End If


    How do you set the Date field to allow null values in the access design view?
    Do you set a default value to the date field?

    "Kevin MacCallum" <kdmaccal_nospamtome@gapac.com> wrote:
    >
    >First ensure the datepicker control Checkbox property is checked (true).

    This
    >allows the user to uncheck the datepicker which will return True when you
    >test the value in the function IsNull(DTP_DT.Value).value. DTP_DT is the
    >name of the control.
    >Also ensure your database date field allows null values.
    >
    >Here is your test
    >if IsNull(DTP_DT.Value) then
    > SQL="DatabaseDateFieldNameHere = Null"
    >else
    > SQL="DatabaseDateFieldNameHere = #" & Format$(DTP_DT.Value, "mm/dd/yyyy")
    >& "#"
    >
    >end if
    >
    >Kevin M
    >
    >"Bill" <atlastruss@gbso.net> wrote:
    >>
    >>
    >>I have a access 2000 database with a vb6 front end, when user clears the
    >>date from a date / time picker control i want to save the blank date back
    >>to the database.

    >



  2. #2
    Kevin MacCallum Guest

    Re: Attn: Kevin How to save a null date to access database?


    Bill, here is how I would write the code

    If IsNull(EngSealsCopyDate.Value) Then
    rs![Eng Seals Copy Date] = Null
    Else
    rs![Eng Seals Copy Date] = CDate(Format$(EngSealsCopyDate.Value, "mmm/d/yyyy"))
    End If

    There's no need to convert the value from the datepicker control to a string
    first then text for the length >0 etc.

    My original reply assumed you were creating a SQl string and then running
    the Execute command against the connection object.
    To update a field in an open recordset you don't use the # symbol.
    I'm only using the format$ command to strip off the
    time portion of the datepicker value (if there was any).
    By using the MMM in the format$ I'm returning a 3 character month name so
    the Cdate function cannot possibly confuse the day / month order.

    To set the allows null on a date field in Access just ensure the
    "Reqired" property (in table design view) is set to No.
    If you want the date field in a new record to remain blank do not set a default
    value. A new record will pick up the default if you do not specify a value
    for that field.


    "Bill" <atlastruss@gbso.net> wrote:
    >
    >
    >Kevin
    >
    >Here is my code with your line in it, I get a type mismatch when I run it
    >
    >If IsNull(EngSealsCopyDate.Value) Then
    > temp_EngSealsCopyDate$ = ""
    > Else
    > temp_EngSealsCopyDate$ = Left(EngSealsCopyDate.Value, 10) ' get
    >the date like this 02/29/2000
    > temp_EngSealsCopyDate$ = Trim$(temp_EngSealsCopyDate$)
    > End If
    >
    >
    > If Len(temp_EngSealsCopyDate$) > 0 Then
    > rs![Eng Seals Copy Date] = CDate(temp_EngSealsCopyDate$)
    >
    > Else
    >
    > ' rs![Eng Seals Copy Date] = CDate("")
    > ' rs![Eng Seals Copy Date] = CDate(myKnullDate)
    >'type mismatch here ----> 'rs![Eng Seals Copy Date] = "#" & Format$(EngSealsCopyDate.Value,
    >"mm/dd/yyyy") & "#"
    >'type mismatch or here ----> rs![Eng Seals Copy Date] = CDate(Format$(EngSealsCopyDate.Value,
    >"mm/dd/yyyy"))
    > End If
    >
    >
    >How do you set the Date field to allow null values in the access design

    view?
    >Do you set a default value to the date field?
    >
    >"Kevin MacCallum" <kdmaccal_nospamtome@gapac.com> wrote:
    >>
    >>First ensure the datepicker control Checkbox property is checked (true).

    >This
    >>allows the user to uncheck the datepicker which will return True when you
    >>test the value in the function IsNull(DTP_DT.Value).value. DTP_DT is the
    >>name of the control.
    >>Also ensure your database date field allows null values.
    >>
    >>Here is your test
    >>if IsNull(DTP_DT.Value) then
    >> SQL="DatabaseDateFieldNameHere = Null"
    >>else
    >> SQL="DatabaseDateFieldNameHere = #" & Format$(DTP_DT.Value, "mm/dd/yyyy")
    >>& "#"
    >>
    >>end if
    >>
    >>Kevin M
    >>
    >>"Bill" <atlastruss@gbso.net> wrote:
    >>>
    >>>
    >>>I have a access 2000 database with a vb6 front end, when user clears the
    >>>date from a date / time picker control i want to save the blank date back
    >>>to the database.

    >>

    >



  3. #3
    Bill Guest

    Re: Attn: Kevin How to save a null date to access database?


    Hats of to you Kevin

    Thank you so very very much, your code change works just how
    I wanted. I also learned from your example that I don't need to change the
    value from the date picker control to a string, this will cut down on alot
    of coding.

    You the man

    Thank you again so much
    Bill Atkins

    "Kevin MacCallum" <kdmaccal_nospantome@gapac.com> wrote:
    >
    >Bill, here is how I would write the code
    >
    >If IsNull(EngSealsCopyDate.Value) Then
    > rs![Eng Seals Copy Date] = Null
    >Else
    > rs![Eng Seals Copy Date] = CDate(Format$(EngSealsCopyDate.Value, "mmm/d/yyyy"))
    >End If
    >
    >There's no need to convert the value from the datepicker control to a string
    >first then text for the length >0 etc.
    >
    >My original reply assumed you were creating a SQl string and then running
    >the Execute command against the connection object.
    >To update a field in an open recordset you don't use the # symbol.
    >I'm only using the format$ command to strip off the
    >time portion of the datepicker value (if there was any).
    >By using the MMM in the format$ I'm returning a 3 character month name so
    >the Cdate function cannot possibly confuse the day / month order.
    >
    >To set the allows null on a date field in Access just ensure the
    >"Reqired" property (in table design view) is set to No.
    >If you want the date field in a new record to remain blank do not set a

    default
    >value. A new record will pick up the default if you do not specify a value
    >for that field.
    >
    >
    >"Bill" <atlastruss@gbso.net> wrote:
    >>
    >>
    >>Kevin
    >>
    >>Here is my code with your line in it, I get a type mismatch when I run

    it
    >>
    >>If IsNull(EngSealsCopyDate.Value) Then
    >> temp_EngSealsCopyDate$ = ""
    >> Else
    >> temp_EngSealsCopyDate$ = Left(EngSealsCopyDate.Value, 10) '

    get
    >>the date like this 02/29/2000
    >> temp_EngSealsCopyDate$ = Trim$(temp_EngSealsCopyDate$)
    >> End If
    >>
    >>
    >> If Len(temp_EngSealsCopyDate$) > 0 Then
    >> rs![Eng Seals Copy Date] = CDate(temp_EngSealsCopyDate$)
    >>
    >> Else
    >>
    >> ' rs![Eng Seals Copy Date] = CDate("")
    >> ' rs![Eng Seals Copy Date] = CDate(myKnullDate)
    >>'type mismatch here ----> 'rs![Eng Seals Copy Date] = "#" & Format$(EngSealsCopyDate.Value,
    >>"mm/dd/yyyy") & "#"
    >>'type mismatch or here ----> rs![Eng Seals Copy Date] = CDate(Format$(EngSealsCopyDate.Value,
    >>"mm/dd/yyyy"))
    >> End If
    >>
    >>
    >>How do you set the Date field to allow null values in the access design

    >view?
    >>Do you set a default value to the date field?
    >>
    >>"Kevin MacCallum" <kdmaccal_nospamtome@gapac.com> wrote:
    >>>
    >>>First ensure the datepicker control Checkbox property is checked (true).

    >>This
    >>>allows the user to uncheck the datepicker which will return True when

    you
    >>>test the value in the function IsNull(DTP_DT.Value).value. DTP_DT is the
    >>>name of the control.
    >>>Also ensure your database date field allows null values.
    >>>
    >>>Here is your test
    >>>if IsNull(DTP_DT.Value) then
    >>> SQL="DatabaseDateFieldNameHere = Null"
    >>>else
    >>> SQL="DatabaseDateFieldNameHere = #" & Format$(DTP_DT.Value, "mm/dd/yyyy")
    >>>& "#"
    >>>
    >>>end if
    >>>
    >>>Kevin M
    >>>
    >>>"Bill" <atlastruss@gbso.net> wrote:
    >>>>
    >>>>
    >>>>I have a access 2000 database with a vb6 front end, when user clears

    the
    >>>>date from a date / time picker control i want to save the blank date

    back
    >>>>to the database.
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center