convert null to date

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,

Answer 1

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

Answer 2

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

Answer 3

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