Date Format for Report 1

[mcalvanelli]

I have a report that is exported from another program into Excel and then imported into an Access Database. I have a field called [Entry Date] that is in the format 06/15/04. I need for it to appear in long date format in the report ie: Tuesday, June 15, 2004. I searched the boards and archive and couldn't find anything to address this.

Does anyone have any ideas?

Melissa

 

[Noel2]

In the field where you need the date, put the expression as follows: Format([yourfield],"dddd, mmmm d, yyyy"). This is where yourfield is what ever field that you are pulling the date from. Please post reply if you run into a problem using that.

 

[mcalvanelli]

Thanks for the quick reply!

I put that in the control source, but I'm getting a syntax error.

 

[Noel2]

Please show me what exactly you are putting into the control source, and is it coming from a table, query?

 

[mcalvanelli]

I put this in Control Source:
Format([Entry Date],"dddd, mmmm d, yyyy")

This is coming from a query based on a table.

 

[Noel2]

Sorry for the confusion. Make sure you input just this:

=Format([Entry Date],"dddd"", ""mmmm d"", ""yyyy")

If you are still getting an error, please let me know what the error is and if you have any controls on your report named [Entry Date].

Thank you,
Noel

 

[mcalvanelli]

It doesn't give a specific error message now. But the text field has #Error in it.

I also checked and I don't have any controls on the report called Entry Date.

 

[Noel2]

What version of access are you using? Can you e-mail the file?

noelbaggett@cox.net

 

[mcalvanelli]

I am using Access 2002. Unfortunately, I can't email the DB to you. Our email system is locked down pretty tight.

Thanks for your help. I'll just keep trying to figure it out.

Melissa

 

[CosmoKramer]

Melissa,

Did you try simply setting the format of the text box on the report to Long Date??

Hoc nomen meum verum non est.

 

[mcalvanelli]

Yes, I did try that and it didn't do anything. It just leaves it in the 06/15/04 format. That's actually the first thing I tried.

Thanks anyway.

Melissa

 

[CosmoKramer]

OK, try this. Add a new text box with this as the control source:

=Format([Entry Date],"Long Date")

Hoc nomen meum verum non est.

 

[Noel2]

The only other thing that I could think of is to check the format of the table beneath the query. Double check and make sure that it is date/time. It really shouldn't matter, but it may be a good thing to check. Just to troubleshoot, I would input this into the control source:

=Format(Date(),"dddd"", ""mmmm d"", ""yyyy")

This will put today's date into it. I am spent after that, I do not believe I can help you any further on this one. Good luck to you!

 

[mcalvanelli]

Ok, I feel really dumb. Apparently the field is coming over as text from the other DB. When I changed it to Date/Time and put Long Date under format, it worked.

Unfortunatly, this export is done at least once a week, so all the field info needs to be updated everytime it exports. I think I'll just write a macro to convert everything.

Thanks to all for your help. Sorry I was so dense.

Melissa

 

[CosmoKramer]

If you want, you could just re-format it in the report:

=Format(CDate([Entry Date]),"Long Date")

Hoc nomen meum verum non est.