format row source as long date (1 Viewer)

[tubar]

the source table needs to be formatted as short date...my form is pulling data from a query and I would like to change the row source of my combo box on the form to be formatted in long date. row source is as followed

SELECT [qrydateSUM].[day of event] FROM qrydateSUM ORDER BY [day of event];

I have tried this with no luck

SELECT [qrydateSUM].format([day of event],"long date") FROM qrydateSUM ORDER BY [day of event];

also changing the format option in combo box doesn't work...im thinking to add a day column to query...but there has to be a way to change row source

 

[pbaldy]

Try

SELECT format([day of event],"long date") FROM qrydateSUM ORDER BY [day of event]

 

[missinglinq]

Why not simply have a Calculated Field in your Query

[B]FormatAsLong: Format([day of event],"Long Date")[/B]

then use the Combobox Wizard and select the Field FormatAsLong to populate it?

Linq ;0)>

 

[Galaxiom]

I am more inclined to question why the Format property of the control doesn't work.

I suspect the problem is the field in the table is text instead of Date.

 

[missinglinq]

...I suspect the problem is the field in the table is text instead of Date...

Testing (in 2003/2007) shows that it doesn't matter, which really didn't surprise me. Access Functions are very tolerant, vis-à-vis accepting data that look like Dates as Dates.

I couldn't get it to Format a Date, in a SQL Statement, which is why I suggested doing the Format in the Query itself.

The trick, as I found out afterward, when I thought to check the SQL behind the Query, is it has to take the form of

SELECT Format([day of event],"Long Date") AS FormatAsLong FROM qrydateSUM;

which works just as well!

Linq ;0)>

 

[Galaxiom]

Testing (in 2003/2007) shows that it doesn't matter, which really didn't surprise me. Access Functions are very tolerant, vis-à-vis accepting data that look like Dates as Dates.

Ah yes, that's right. Access will treat things that look like dates as dates. It will even silently convert mm/dd/yy and ISO strings to dates on systems where the Regional Settings are dd/mm/yyyy. Ever so helpful .

However there must be some reason why the Format property is not working in that control. It certainly works for me even when the table is Short Date, as would be expected.

They could try spelling out the Long Date format in the control's Format property:
dddd", "d mmmm yyyy

Generally it is better to Format on the form than in the RecordSource query. (Especially if they want to be able to enter data on the form.)

I questioned because OP stated, "the source table needs to be formatted as short date" when nobody should care that much about the table format.

 

[missinglinq]

...However there must be some reason why the Format property is not working in that control...

The syntax of the two attempts

[qrydateSUM].format([day of event],"long date")

Format([day of event],"long date")

is incorrect, it has to be

Format([day of event],"long date") AS CalculatedFieldName

with the

AS CalculatedFieldName

being the needed but missing part of the previous attempts.

Linq ;0)>