Convert date to string?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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]))),"")
 
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]))),"")
Click to expand...

I'd try:

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


John W. Vinson [MVP]
 
.... and another option:

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

(type as one line in your code.)
 
Simplified: IIf (IsNull(yr+mo+dy),Null, DateSerial([yr], [mo], [dy]))

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

Pieter


John W. Vinson said:
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]))),"")
Click to expand...

I'd try:

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


John W. Vinson [MVP]
Click to expand...
 
Simplified: IIf (IsNull(yr+mo+dy),Null, DateSerial([yr], [mo], [dy]))

no reason not to utilize the "problem" <g>
Click to expand...

<g> Excellent. Simple is good.

John W. Vinson [MVP]
 
Thanks to all for your help.

Van T. Dinh said:
.... 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)



Emjaysea said:
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]))),"")
Click to expand...
Click to expand...
 
Back
Top