Convert date to string?

[Guest]

I have month, day, and year variables for some records but not for others
(appropriately). I need to create an expression that will return a date
value or a string version of that value if those are populated and a null or
a blank string if they aren't. I can convert to a date or a string with that
date, but I always get #Error when there is not a valid date.

Here's one version of what I've tried so far. Any ideas? Thanks.

apptdate:
IIf(CDate(DateSerial([tblNV]![yr],[tblNV]![mo],[tblNV]![dy])),CStr(CDate(DateSerial([tblNV]![yr],[tblNV]![mo],[tblNV]![dy]))),"")

 

[UpRider]

Take a look in help for the IsDate function.
UpRider

 

[John W. Vinson]

I have month, day, and year variables for some records but not for others
(appropriately). I need to create an expression that will return a date
value or a string version of that value if those are populated and a null or
a blank string if they aren't. I can convert to a date or a string with that
date, but I always get #Error when there is not a valid date.

Here's one version of what I've tried so far. Any ideas? Thanks.

apptdate:
IIf(CDate(DateSerial([tblNV]![yr],[tblNV]![mo],[tblNV]![dy])),CStr(CDate(DateSerial([tblNV]![yr],[tblNV]![mo],[tblNV]![dy]))),"")

I'd try:

IIF(IsNull([yr]) OR IsNull([mo]) OR IsNull([dy]), Null, DateSerial([yr], [mo],
[dy]))


John W. Vinson [MVP]

 

[Van T. Dinh]

.... and another option:

IIf(IsNumeric([yr]) AND IsNumeric([mo] AND IsNumeric([dy]),
DateSerial([yr], [mo], [dy]), Null)

(type as one line in your code.)

 

[Pieter Wijnen]

Simplified: IIf (IsNull(yr+mo+dy),Null, DateSerial([yr], [mo], [dy]))

no reason not to utilize the "problem" <g>

Pieter

I have month, day, and year variables for some records but not for others
(appropriately). I need to create an expression that will return a date
value or a string version of that value if those are populated and a null
or
a blank string if they aren't. I can convert to a date or a string with
that
date, but I always get #Error when there is not a valid date.

Here's one version of what I've tried so far. Any ideas? Thanks.

apptdate:
IIf(CDate(DateSerial([tblNV]![yr],[tblNV]![mo],[tblNV]![dy])),CStr(CDate(DateSerial([tblNV]![yr],[tblNV]![mo],[tblNV]![dy]))),"")

I'd try:

IIF(IsNull([yr]) OR IsNull([mo]) OR IsNull([dy]), Null, DateSerial([yr],
[mo],
[dy]))


John W. Vinson [MVP]

 

[John W. Vinson]

Simplified: IIf (IsNull(yr+mo+dy),Null, DateSerial([yr], [mo], [dy]))

no reason not to utilize the "problem" <g>

<g> Excellent. Simple is good.

John W. Vinson [MVP]

 

[Guest]

Thanks to all for your help.

.... and another option:

IIf(IsNumeric([yr]) AND IsNumeric([mo] AND IsNumeric([dy]),
DateSerial([yr], [mo], [dy]), Null)

(type as one line in your code.)

--
HTH
Van T. Dinh
MVP (Access)

I have month, day, and year variables for some records but not for others
(appropriately). I need to create an expression that will return a date
value or a string version of that value if those are populated and a null
or
a blank string if they aren't. I can convert to a date or a string with
that
date, but I always get #Error when there is not a valid date.

Here's one version of what I've tried so far. Any ideas? Thanks.

apptdate:
IIf(CDate(DateSerial([tblNV]![yr],[tblNV]![mo],[tblNV]![dy])),CStr(CDate(DateSerial([tblNV]![yr],[tblNV]![mo],[tblNV]![dy]))),"")