Trouble Concatenating Dates

[jazminecat]

Hi all --
I have a report based on a query that returns records which contain dates. The dates are not always populated. The date fields are

NoticeDate1
NoticeDate2
NoticeDate3

What I would like is for the field to show the dates, with commas in between for each record, and not show any info if the dates are empty. For example:

Record 1 has NoticeDate1 = 1/1/2006
NoticeDate2 = 5/5/2006

I would like to see:

1/1/2006, 5/5/2006



When I try to concatenate them with the following in the control source of a text box, I get #Error.

=[NoticeDate1] & [NoticeDate2] & [NoticeDate3]

I have also tried
=Nz([NoticeDate1]) & Nz([NoticeDate2]) & Nz([NoticeDate3])

with the same results. Any thoughts?

Thanks!

 

[jazminecat]

anyone? anyone? Bueller?

 

[Knicks]

=Trim([noticedate1] & Trim([noticedate2].....

But for the commas and stuff you'll probably need a big old If then else statement. That is where normalization comes in, you probably would want a linked table of notice dates and then a corresponding subform, this way you can have 1 notice date or 1000 notice dates associated with the record.

Barring changing your database, try an If then Else statement for the text if you want the commas to look right.

 

[Remou]

Have you seen the FAQs on concatenation? Here are two:
Create a horizontal delimited list of records
faq703-3587
How to concatenate multiple child records into a single value
faq701-4233

 

[sdk]

Hi

(without first checking what is in the FAQs)

Is the textbox name included in the statement? ie is the name of the control "NoticeDate1", "NoticeDate2", or "NoticeDate3"?

Also, the statement should read:
=[NoticeDate1] & ", " & [NoticeDate2] & ", " & [NoticeDate3]

Cheers

S