Sorting a query

[Nick T]

Hi, iv had some good feedback on this before so thanks to those who have
helped, but im still stumped! Ill try to be a little more accurate.

This data base is used in a production environment where more than one
record will be added for a set product each day. eg product "A" will be
added as a record containing a 'Total' of 20 five times each day. Therfore
this being my reason for creating my query in the following way:

But first, when a record is added, it adds the following info to my
'production table'.
Product, Production Date, Production Time, size and total quantity. So for
each day, i will have many records for one particular product with the total
quantities produced.

I have my access query set up so that it looks at a particular day, lists
each product produced on that day, and automatically adds up the 'total
quanity' produced.

So for eg. 'product A' could be produced 10 times in one day, each time
making 5 products. My 'production table would therefore have 10 records. I
then want to make my query tell me that on 01.02.08 a total of 50 'product
A's were produced. This is working, however the query is changing the date
format column from 'short date' (dd.mm.yy) as per the format in the table to
'long date' (01 Janurary 2008) in the query - this then messes up my sorting
in assending or decending order.

It is a summary query and my summary value = sum
I have grouped the info by day using the query wizard.

I hope this makes sence and hope that someone can help.

Many thanks
Nick

 

[Duane Hookom]

This question should actually be asked in the query NG unless the results are
presented in a report (then use the Report NG).

However, what is the SQL view of your query?

 

[akphidelt]

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.

 

[Nick T]

Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

 

[J_Goddard via AccessMonster.com]

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John

Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.

[quoted text clipped - 32 lines]

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[akphidelt]

Instead of using the SQL view, try going to the design view. Right click on
it, go to properties and change the input mask to Short Date and Format to
Short Date.

 

[Nick T]

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John

Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.

[quoted text clipped - 32 lines]

Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[J_Goddard via AccessMonster.com]

Hi -

Assuming the field type in the table is date/time, all you need to do is set
the sorting for the field to ascending or descending in the query design grid.
Remember, the format property of the field does not affect the sorting - it
only defines how the data is displayed.

John

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks

Hi -

[quoted text clipped - 35 lines]

 

[Duane Hookom]

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y. Playing around
with the "display format" will have no effect on the sort order.
--
Duane Hookom
Microsoft Access MVP

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John

Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Nick T]

Hi,

To confirm, my table field type is date/time and this does sort in the
correct order. Its only when i try to sort the dates in the query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y. Playing around
with the "display format" will have no effect on the sort order.
--
Duane Hookom
Microsoft Access MVP

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Duane Hookom]

You are not sorting on the [Wrap Date] field value. You are sorting on a text
representation of the value which is causing your issue. Take away the
formatting. Formatting should be reserved form controls on forms and reports.
I rarely set any formatting in a table or query.

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY [Wrap Date];
--
Duane Hookom
Microsoft Access MVP

Hi,

To confirm, my table field type is date/time and this does sort in the
correct order. Its only when i try to sort the dates in the query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y. Playing around
with the "display format" will have no effect on the sort order.
--
Duane Hookom
Microsoft Access MVP

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks


:

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Nick T]

Hi Thanks for the continued help.

Iv tried replacing my SQL info with what you sent me but i get the following
error message:

"You tried to execute a query that does not include the specified expression
'[wrap date] as part of an aggregate finction."

How do i remove the formatting you refer to?

Thanks
nick

You are not sorting on the [Wrap Date] field value. You are sorting on a text
representation of the value which is causing your issue. Take away the
formatting. Formatting should be reserved form controls on forms and reports.
I rarely set any formatting in a table or query.

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY [Wrap Date];
--
Duane Hookom
Microsoft Access MVP

Hi,

To confirm, my table field type is date/time and this does sort in the
correct order. Its only when i try to sort the dates in the query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y. Playing around
with the "display format" will have no effect on the sort order.
--
Duane Hookom
Microsoft Access MVP


:

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks


:

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Duane Hookom]

Try this SQL:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date] AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date]
ORDER BY [Production Table].[Wrap Date];

If the Wrap Date field contains a time element, you may need to use
DateValue().

--
Duane Hookom
Microsoft Access MVP

Hi Thanks for the continued help.

Iv tried replacing my SQL info with what you sent me but i get the following
error message:

"You tried to execute a query that does not include the specified expression
'[wrap date] as part of an aggregate finction."

How do i remove the formatting you refer to?

Thanks
nick

You are not sorting on the [Wrap Date] field value. You are sorting on a text
representation of the value which is causing your issue. Take away the
formatting. Formatting should be reserved form controls on forms and reports.
I rarely set any formatting in a table or query.

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY [Wrap Date];
--
Duane Hookom
Microsoft Access MVP

Hi,

To confirm, my table field type is date/time and this does sort in the
correct order. Its only when i try to sort the dates in the query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick


:

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y. Playing around
with the "display format" will have no effect on the sort order.
--
Duane Hookom
Microsoft Access MVP


:

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks


:

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Nick T]

Hi, Think we are slowly getting there.

Could you explain a littel more with regards to your last sentence about the
date field and using DateValue(). How do i do this?

Thanks Duane

Try this SQL:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date] AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date]
ORDER BY [Production Table].[Wrap Date];

If the Wrap Date field contains a time element, you may need to use
DateValue().

--
Duane Hookom
Microsoft Access MVP

Hi Thanks for the continued help.

Iv tried replacing my SQL info with what you sent me but i get the following
error message:

"You tried to execute a query that does not include the specified expression
'[wrap date] as part of an aggregate finction."

How do i remove the formatting you refer to?

Thanks
nick

You are not sorting on the [Wrap Date] field value. You are sorting on a text
representation of the value which is causing your issue. Take away the
formatting. Formatting should be reserved form controls on forms and reports.
I rarely set any formatting in a table or query.

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY [Wrap Date];
--
Duane Hookom
Microsoft Access MVP


:

Hi,

To confirm, my table field type is date/time and this does sort in the
correct order. Its only when i try to sort the dates in the query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick


:

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y. Playing around
with the "display format" will have no effect on the sort order.
--
Duane Hookom
Microsoft Access MVP


:

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks


:

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Duane Hookom]

Your query is a totals/group by with [Wrap Date] as one of the fields to
group by. This field might contain a values like:
3/3/2008
3/3/2008
or
3/3/2008 9:30:15
3/3/2008 9:30:13

Grouping by these values would create one row from the top two examples
while the values with times would output two rows. If you want the bottom
examples to create a single row in the output, you would need to remove the
time portion of the value. This can be done using the DateValue() function
like:
DateValue([Wrap Date])

--
Duane Hookom
Microsoft Access MVP

Hi, Think we are slowly getting there.

Could you explain a littel more with regards to your last sentence about the
date field and using DateValue(). How do i do this?

Thanks Duane

Try this SQL:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date] AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date]
ORDER BY [Production Table].[Wrap Date];

If the Wrap Date field contains a time element, you may need to use
DateValue().

--
Duane Hookom
Microsoft Access MVP

Hi Thanks for the continued help.

Iv tried replacing my SQL info with what you sent me but i get the following
error message:

"You tried to execute a query that does not include the specified expression
'[wrap date] as part of an aggregate finction."

How do i remove the formatting you refer to?

Thanks
nick


:

You are not sorting on the [Wrap Date] field value. You are sorting on a text
representation of the value which is causing your issue. Take away the
formatting. Formatting should be reserved form controls on forms and reports.
I rarely set any formatting in a table or query.

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY [Wrap Date];
--
Duane Hookom
Microsoft Access MVP


:

Hi,

To confirm, my table field type is date/time and this does sort in the
correct order. Its only when i try to sort the dates in the query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick


:

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y. Playing around
with the "display format" will have no effect on the sort order.
--
Duane Hookom
Microsoft Access MVP


:

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks


:

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Nick T]

Hi Duane,
Thank you for the continued help.

when you refer to "DateValue([Wrap Date])" where do i put this? In the SQL??

Thanks
Nick

Your query is a totals/group by with [Wrap Date] as one of the fields to
group by. This field might contain a values like:
3/3/2008
3/3/2008
or
3/3/2008 9:30:15
3/3/2008 9:30:13

Grouping by these values would create one row from the top two examples
while the values with times would output two rows. If you want the bottom
examples to create a single row in the output, you would need to remove the
time portion of the value. This can be done using the DateValue() function
like:
DateValue([Wrap Date])

--
Duane Hookom
Microsoft Access MVP

Hi, Think we are slowly getting there.

Could you explain a littel more with regards to your last sentence about the
date field and using DateValue(). How do i do this?

Thanks Duane

Try this SQL:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date] AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date]
ORDER BY [Production Table].[Wrap Date];

If the Wrap Date field contains a time element, you may need to use
DateValue().

--
Duane Hookom
Microsoft Access MVP


:

Hi Thanks for the continued help.

Iv tried replacing my SQL info with what you sent me but i get the following
error message:

"You tried to execute a query that does not include the specified expression
'[wrap date] as part of an aggregate finction."

How do i remove the formatting you refer to?

Thanks
nick


:

You are not sorting on the [Wrap Date] field value. You are sorting on a text
representation of the value which is causing your issue. Take away the
formatting. Formatting should be reserved form controls on forms and reports.
I rarely set any formatting in a table or query.

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY [Wrap Date];
--
Duane Hookom
Microsoft Access MVP


:

Hi,

To confirm, my table field type is date/time and this does sort in the
correct order. Its only when i try to sort the dates in the query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick


:

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y. Playing around
with the "display format" will have no effect on the sort order.
--
Duane Hookom
Microsoft Access MVP


:

Hi,
No suggestions seem to be working yet - perhaps im just out of my depth!

I can sort my tables as requried, but just cant seem to sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks


:

Hi -

That data is sorted properly if the short date is mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the Region Options of the
Control Panel. Try setting the format of the field in your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view from 'long date' to
short date, then yes, it does change the display of the info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the first two digits)

If i look at the properties on the date field in query design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Douglas J. Steele]

Yes, in the SQL around every occurrence of Wrap Date:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
DateValue([Production Table].[Wrap Date]) AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
DateValue([Production Table].[Wrap Date])
ORDER BY DateValue([Production Table].[Wrap Date]);


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Duane,
Thank you for the continued help.

when you refer to "DateValue([Wrap Date])" where do i put this? In the
SQL??

Thanks
Nick

Your query is a totals/group by with [Wrap Date] as one of the fields to
group by. This field might contain a values like:
3/3/2008
3/3/2008
or
3/3/2008 9:30:15
3/3/2008 9:30:13

Grouping by these values would create one row from the top two examples
while the values with times would output two rows. If you want the
bottom
examples to create a single row in the output, you would need to remove
the
time portion of the value. This can be done using the DateValue()
function
like:
DateValue([Wrap Date])

--
Duane Hookom
Microsoft Access MVP

Hi, Think we are slowly getting there.

Could you explain a littel more with regards to your last sentence
about the
date field and using DateValue(). How do i do this?

Thanks Duane

:

Try this SQL:

SELECT DISTINCTROW [Production Table].Product, [Production
Table].Outer,
[Production Table].[Wrap Date] AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date]
ORDER BY [Production Table].[Wrap Date];

If the Wrap Date field contains a time element, you may need to use
DateValue().

--
Duane Hookom
Microsoft Access MVP


:

Hi Thanks for the continued help.

Iv tried replacing my SQL info with what you sent me but i get the
following
error message:

"You tried to execute a query that does not include the specified
expression
'[wrap date] as part of an aggregate finction."

How do i remove the formatting you refer to?

Thanks
nick


:

You are not sorting on the [Wrap Date] field value. You are
sorting on a text
representation of the value which is causing your issue. Take
away the
formatting. Formatting should be reserved form controls on forms
and reports.
I rarely set any formatting in a table or query.

SELECT DISTINCTROW [Production Table].Product, [Production
Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap
Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY [Wrap Date];
--
Duane Hookom
Microsoft Access MVP


:

Hi,

To confirm, my table field type is date/time and this does sort
in the
correct order. Its only when i try to sort the dates in the
query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production
Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap
Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick


:

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y.
Playing around
with the "display format" will have no effect on the sort
order.
--
Duane Hookom
Microsoft Access MVP


:

Hi,
No suggestions seem to be working yet - perhaps im just out
of my depth!

I can sort my tables as requried, but just cant seem to
sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks


:

Hi -

That data is sorted properly if the short date is
mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the
Region Options of the
Control Panel. Try setting the format of the field in
your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view
from 'long date' to
short date, then yes, it does change the display of the
info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort
displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the
first two digits)

If i look at the properties on the date field in query
design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration
happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in
the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com

 

[Nick T]

great!
Thanks for all help!

Yes, in the SQL around every occurrence of Wrap Date:

SELECT DISTINCTROW [Production Table].Product, [Production Table].Outer,
DateValue([Production Table].[Wrap Date]) AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
DateValue([Production Table].[Wrap Date])
ORDER BY DateValue([Production Table].[Wrap Date]);


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Duane,
Thank you for the continued help.

when you refer to "DateValue([Wrap Date])" where do i put this? In the
SQL??

Thanks
Nick

Your query is a totals/group by with [Wrap Date] as one of the fields to
group by. This field might contain a values like:
3/3/2008
3/3/2008
or
3/3/2008 9:30:15
3/3/2008 9:30:13

Grouping by these values would create one row from the top two examples
while the values with times would output two rows. If you want the
bottom
examples to create a single row in the output, you would need to remove
the
time portion of the value. This can be done using the DateValue()
function
like:
DateValue([Wrap Date])

--
Duane Hookom
Microsoft Access MVP


:

Hi, Think we are slowly getting there.

Could you explain a littel more with regards to your last sentence
about the
date field and using DateValue(). How do i do this?

Thanks Duane

:

Try this SQL:

SELECT DISTINCTROW [Production Table].Product, [Production
Table].Outer,
[Production Table].[Wrap Date] AS [Wrap Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
[Production Table].[Wrap Date]
ORDER BY [Production Table].[Wrap Date];

If the Wrap Date field contains a time element, you may need to use
DateValue().

--
Duane Hookom
Microsoft Access MVP


:

Hi Thanks for the continued help.

Iv tried replacing my SQL info with what you sent me but i get the
following
error message:

"You tried to execute a query that does not include the specified
expression
'[wrap date] as part of an aggregate finction."

How do i remove the formatting you refer to?

Thanks
nick


:

You are not sorting on the [Wrap Date] field value. You are
sorting on a text
representation of the value which is causing your issue. Take
away the
formatting. Formatting should be reserved form controls on forms
and reports.
I rarely set any formatting in a table or query.

SELECT DISTINCTROW [Production Table].Product, [Production
Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap
Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY [Wrap Date];
--
Duane Hookom
Microsoft Access MVP


:

Hi,

To confirm, my table field type is date/time and this does sort
in the
correct order. Its only when i try to sort the dates in the
query when i
seem to have this problem.

My SQL view is as follows:

SELECT DISTINCTROW [Production Table].Product, [Production
Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date') AS [Wrap
Date By Day],
Sum([Production Table].[Total Outers]) AS [Sum Of Total Outers]
FROM [Production Table]
GROUP BY [Production Table].Product, [Production Table].Outer,
Format$([Production Table].[Wrap Date],'Short Date')
ORDER BY Format$([Production Table].[Wrap Date],'Short Date');

Look forward to hearing from you.
Nick


:

Could you share your SQL view?
Typically dates in queries prefer the U.S. format of m/d/y.
Playing around
with the "display format" will have no effect on the sort
order.
--
Duane Hookom
Microsoft Access MVP


:

Hi,
No suggestions seem to be working yet - perhaps im just out
of my depth!

I can sort my tables as requried, but just cant seem to
sort my query.
The data when set to "short date" appears as dd.mm.yyyy

Any other suggestions would be welcome.
Thanks


:

Hi -

That data is sorted properly if the short date is
mm.dd.yyyy. This display
of the "Short Date" will depend on the settings in the
Region Options of the
Control Panel. Try setting the format of the field in
your query explicitly
to dd.mm.yyyy, instead of "Short Date"

John



Nick T wrote:
Hi,
Thanks for the quick feedback.

Firstly, if i change the date format in the SQL view
from 'long date' to
short date, then yes, it does change the display of the
info from eg 01
Janurary 2008 to 01.01.2008.

I still cant sort this properly and my query sort
displays as follows for eg:

01.01.2008
01.02.2008
01.03.2008
02.01.2008
02.02.2008
02.03.2008 etc etc (it seems to be only sorting on the
first two digits)

If i look at the properties on the date field in query
design view, the drop
down menu on 'Format' is blank.

??? I can see another late nite of frustration
happening!
Look forward to your reply.

Thanks
Nick

You should be able to right click on the date field in
the query design view.
Click on properties and change the format there.
[quoted text clipped - 32 lines]
Many thanks
Nick

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com