Sorting a query

[Guest]

I have a query with some feilds that I want sorted by three different feilds.
The frist one is a date and the next one is a last name and the last one a
first name. When I set this up in a query the dates are not in chronological
order. Is there a special way to sort dates (rather than just selecting
"accending" in the query edit window)?

 

[Guest]

Hi William,

Two possibilities come to mind. Either the field that you are using to store
your dates is not a date/time data type (perhaps it is a text data type?), or
your date field is not the first one in the ORDER BY clause. Sorting in
queries occurs in left to right fashion, when viewing the query design grid.

First verify that you are not using a text data type. If that does not
correct the problem, then try posting the SQL statement for your query. To
view and copy the SQL statement, click on View > SQL View when you have the
query open in design view.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

I checked and it was date and time. Here is the SQL data
SELECT Master.[Ser Exp], Master.Last, Master.First, Master.Address,
Master.City, Master.State, Master.Zip
FROM Master
WHERE (((Master.[Ser Exp])>=[Enter the First Date] And (Master.[Ser
Exp])<=[Enter Second Date]))
ORDER BY Master.[Ser Exp], Master.Last, Master.First;

 

[Guest]

Hi William,

Okay, the SQL statement looks correct. By chance, are you entering and
displaying your dates with four-digit years? Another possibility to look
into is adding a format statement around the parameters:

WHERE
(Master.[Ser Exp])>=Format([Enter the First Date], "mm\/dd\/yyyy")
AND
(Master.[Ser Exp])<=Format([Enter Second Date], "mm\/dd\/yyyy")

See Access MVP Allen Browne's article for more information:

International Dates in Access
http://allenbrowne.com/ser-36.html


If you still cannot get it to sort correctly, then I invite you to send me a
copy of your compacted and zipped database. If need be, you can sanitize the
data, but you should have a query that demonstrates the problem. If you'd
like to take me up on this offer, my e-mail address is available at the
bottom of the contributor's page indicated in my signature. Whatever you
decide, please do not post your e-mail address (or mine) to a newsgroup
reply. Doing so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[aaron.kempf]

Microsoft DIDNT MAKE THIS A FEATURE because they didn't think that it
was relevent.

You are talking about sorting - alphabetically- column names.. right?

-Aaron
ADP Nationalist

 

[Guest]

Yes, we are using four digit years. What would that mean?

Thanks so much for your time

Hi William,

Okay, the SQL statement looks correct. By chance, are you entering and
displaying your dates with four-digit years? Another possibility to look
into is adding a format statement around the parameters:

WHERE
(Master.[Ser Exp])>=Format([Enter the First Date], "mm\/dd\/yyyy")
AND
(Master.[Ser Exp])<=Format([Enter Second Date], "mm\/dd\/yyyy")

See Access MVP Allen Browne's article for more information:

International Dates in Access
http://allenbrowne.com/ser-36.html


If you still cannot get it to sort correctly, then I invite you to send me a
copy of your compacted and zipped database. If need be, you can sanitize the
data, but you should have a query that demonstrates the problem. If you'd
like to take me up on this offer, my e-mail address is available at the
bottom of the contributor's page indicated in my signature. Whatever you
decide, please do not post your e-mail address (or mine) to a newsgroup
reply. Doing so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I checked and it was date and time. Here is the SQL data
SELECT Master.[Ser Exp], Master.Last, Master.First, Master.Address,
Master.City, Master.State, Master.Zip
FROM Master
WHERE (((Master.[Ser Exp])>=[Enter the First Date] And (Master.[Ser
Exp])<=[Enter Second Date]))
ORDER BY Master.[Ser Exp], Master.Last, Master.First;

 

[Guest]

It's possible, although certainly not recommended, to enter just a two-digit
year. Access will make a guess as to which century you intended. Different
versions of Access can make different guesses too.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________