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]))),"")

Take a look in help for the IsDate function.
UpRider

"Emjaysea" <(E-Mail Removed)> wrote in message
newsEEC5737-78E2-42C0-9312-(E-Mail Removed)...
>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]))),"")

On Tue, 9 Oct 2007 14:04:01 -0700, Emjaysea
<(E-Mail Removed)> wrote:

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

.... 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" <(E-Mail Removed)> wrote in message
newsEEC5737-78E2-42C0-9312-(E-Mail Removed)...
>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]))),"")

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

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

Pieter


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Tue, 9 Oct 2007 14:04:01 -0700, Emjaysea
> <(E-Mail Removed)> wrote:
>
>>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]

On Wed, 10 Oct 2007 09:00:21 +0200, "Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.no (E-Mail Removed)>
wrote:

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

Thanks to all for your help.

"Van T. Dinh" wrote:

> .... 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" <(E-Mail Removed)> wrote in message
> newsEEC5737-78E2-42C0-9312-(E-Mail Removed)...
> >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]))),"")
>
>
>