Formating dates and ordering in queries

[John Dumay]

Hi,

I have a data base which gets date from an SQL server and the dates are in
General format. What I want to do is convert them to Short Date format
"dd/mm/yyyy" (Australian Dates) and then run a Total query to summarise all
the dates. All appears to work well until I populate a drop down list of
dates. Depending on how I do the conversion to the "dd/mm/yyyy". If i
Round([date],0) it appears a s a number, or if I Format([Date], "Short Date")
they dates appear in the proper format but are sorted on the lead digit and
not in date order.

Please help!

Regards,

John Dumay

 

[Rick Brandt]

Hi,

I have a data base which gets date from an SQL server and the dates are
in General format. What I want to do is convert them to Short Date
format "dd/mm/yyyy" (Australian Dates) and then run a Total query to
summarise all the dates. All appears to work well until I populate a
drop down list of dates. Depending on how I do the conversion to the
"dd/mm/yyyy". If i Round([date],0) it appears a s a number, or if I
Format([Date], "Short Date") they dates appear in the proper format but
are sorted on the lead digit and not in date order.

Please help!

Always include two date fields in your query. One unformatted for
sorting on and the other formatted as desired for display (don't sort on
that one).

 

[John W. Vinson]

Hi,

I have a data base which gets date from an SQL server and the dates are in
General format. What I want to do is convert them to Short Date format
"dd/mm/yyyy" (Australian Dates) and then run a Total query to summarise all
the dates. All appears to work well until I populate a drop down list of
dates. Depending on how I do the conversion to the "dd/mm/yyyy". If i
Round([date],0) it appears a s a number, or if I Format([Date], "Short Date")
they dates appear in the proper format but are sorted on the lead digit and
not in date order.

The Format() function does not return a date value; it returns a Text String,
which sorts as you describe. Either set the Format property of the form
control to "dd/mm/yyyy", or sort by the actual date/time field and display the
Format() string.

 

[John Dumay]

Thanks Rick,

I left the final formating until last.

So when I disply in the combo boc I have the following:

Me.cboDateFrom = Format(Me.cboDateFrom.ItemData(0), "Short Date")

So now it displays as ordered in the query and is formated in Australian
date format or US depending on the System settings.

All is good!.

Regards,

John

Hi,

I have a data base which gets date from an SQL server and the dates are
in General format. What I want to do is convert them to Short Date
format "dd/mm/yyyy" (Australian Dates) and then run a Total query to
summarise all the dates. All appears to work well until I populate a
drop down list of dates. Depending on how I do the conversion to the
"dd/mm/yyyy". If i Round([date],0) it appears a s a number, or if I
Format([Date], "Short Date") they dates appear in the proper format but
are sorted on the lead digit and not in date order.

Please help!

Always include two date fields in your query. One unformatted for
sorting on and the other formatted as desired for display (don't sort on
that one).

 

[John Dumay]

See above response. All is good. Many thanks.

Hi,

I have a data base which gets date from an SQL server and the dates are in
General format. What I want to do is convert them to Short Date format
"dd/mm/yyyy" (Australian Dates) and then run a Total query to summarise all
the dates. All appears to work well until I populate a drop down list of
dates. Depending on how I do the conversion to the "dd/mm/yyyy". If i
Round([date],0) it appears a s a number, or if I Format([Date], "Short Date")
they dates appear in the proper format but are sorted on the lead digit and
not in date order.

The Format() function does not return a date value; it returns a Text String,
which sorts as you describe. Either set the Format property of the form
control to "dd/mm/yyyy", or sort by the actual date/time field and display the
Format() string.