none
convert null to date RRS feed

  • Question

  • I have following code to convert a text box valeu to Date.

    I got invalid use of null when the text box is null value.

    I already use IIf(IsNull(txtMytDate)  to check the text box is value or not, it seems that MS Access still try to excute  code CDate(txtMytDate.Value) when the txtMyDate is null.

    I tried to use a constant to replace  CDate(txtMytDate.Value)) then it works.

    Are there any workaround to let MS Access does not execute the  code CDate(txtMytDate.Value) when text box value is null?

    IIf(IsNull(txtMytDate), CDate(0), CDate(txtMytDate.Value))

    Your help and information is great appreciated,

    Regards,

    Sourises,

    Tuesday, October 17, 2017 6:34 PM

Answers

  • Sourises

    You might need to do this in your form:

    If IsNull(txtMyDate) then
    	‘ code to handle empty textbox
    Else
    	‘ code to deal with the date
    endif
    



    peter n roth - http://PNR1.com, Maybe some useful stuff

    • Proposed as answer by Terry Xu - MSFT Wednesday, October 18, 2017 8:25 AM
    • Marked as answer by sourises Wednesday, November 1, 2017 7:49 PM
    Tuesday, October 17, 2017 9:37 PM
  • Other than when called in a query the IIF function evaluates both the True and False expressions, so if one of these raises an error the expression as a whole will fail.  You can avoid this by calling the Nz function:

        IIF(IsNull(txtMytDate),Null,CDate(Nz(txtMytDate)))

    The expression CDate(Nz(txtMytDate)) will evaluate correctly, but its result in the event of txtMytDate being Null will never be returned, so the fact that it will actually evaluate to 1899-12-30 in that context (date-zero in Access's implementation of the date/time data type) is immaterial.

    Ken Sheridan, Stafford, England

    Wednesday, October 18, 2017 12:33 PM

All replies

  • Hi,

    Where are you using this expression? It looks like if the box is Null/Empty, you are converting a Zero (0) into a date. Do you really want to do that? Otherwise, if the box is Null, what do you really want to get? If Null, try the following:

    IIf(IsDate(txtMytDate), CDate(txtMytDate), Null)

    Hope it helps...

    Tuesday, October 17, 2017 7:07 PM
  • Sourises

    You might need to do this in your form:

    If IsNull(txtMyDate) then
    	‘ code to handle empty textbox
    Else
    	‘ code to deal with the date
    endif
    



    peter n roth - http://PNR1.com, Maybe some useful stuff

    • Proposed as answer by Terry Xu - MSFT Wednesday, October 18, 2017 8:25 AM
    • Marked as answer by sourises Wednesday, November 1, 2017 7:49 PM
    Tuesday, October 17, 2017 9:37 PM
  • Other than when called in a query the IIF function evaluates both the True and False expressions, so if one of these raises an error the expression as a whole will fail.  You can avoid this by calling the Nz function:

        IIF(IsNull(txtMytDate),Null,CDate(Nz(txtMytDate)))

    The expression CDate(Nz(txtMytDate)) will evaluate correctly, but its result in the event of txtMytDate being Null will never be returned, so the fact that it will actually evaluate to 1899-12-30 in that context (date-zero in Access's implementation of the date/time data type) is immaterial.

    Ken Sheridan, Stafford, England

    Wednesday, October 18, 2017 12:33 PM