Specifying Date Format for Column Heading in Crosstab Query

[mwolfod]

Under Access 2003, the simple expression, attached below, allowed me to include the day of the week in the date field (column heading) of a cross tab query (Sunday, 12/25/2010 versus simply 12/25/2010).



Under Access 2010, I get the "There is no message for this error" when trying to open the query.

Recreating the object and code does not solve the problem.

Is there some new syntax that is required?

Thank you.

[RuralGuy]

It should work. Try recreating the query from scratch and see what happens.

[mwolfod]

Thanks for the reply.

Yes, as mentioned, I had already tried recreating the query, without success.

I tried again anyway, and again received the error message.

See attachment for Error message and Query Design.

Thanks.

[RuralGuy]

Try just "mm/dd/yy" and see what happens.

[mwolfod]

Yes, that works fine, as does "mm/dd/yyyy", which is the format that it is in now, and that I am trying to change.

Thanks.

[RuralGuy]

How about "mm/dd/yyyy dddd"? How about "Long Date"

[mwolfod]

Well, now "ddd, mm/dd/yyyy" is finally working!

("dddd, mm/dd/yyyy" also works).

Is there a way to specify the width of the columns in a crosstab query? The current width does not allow the entire date to display.

Thanks.

[RuralGuy]

Can't you just drag it wider in datasheet view and save it?

[mwolfod]

The number of columns in this query varies widely, so I was really looking for a solution that would set the column width to a fixed measurement, regardless of the number of columns.

In any case, dragging the column width and saving seems to work when running the query alone, but not when I run it from the Form. Weird.

Thanks, for your continued assistance.

[mwolfod]

Oh, well, I just dragged the columns to the desired width on the query generated from the form, and that seems to work...at least for the number of columns generated by that particular query run.

Thanks.

[RuralGuy]

That's probably the best we will come up with for this. Thanks for marking the thread as Solved.

[mwolfod]

I am reopening this thread because I have discovered that the error that I was receiving (see Post #3 above), was due to the JOIN type (also visible in the attachment to Post #3 above), rather than the date format as originally thought.

This Join Type worked perfectly fine under Access 2003, giving us the query layout we desired. Because of this prior functionality, I am not sure where to start in resolving the problem, particularly because of the cryptic nature of the error message.

[RuralGuy]

Could you post the SQL view of the query so we can see it please?

[mwolfod]

SQL of the query is attached.

Thanks.

[RuralGuy]

So you believe a Left Join is causing the problem? Have you tried other Join types?