G
Guest
I have a date feild that fills mm/dd/yyyy
Is there a way to sort by just month and year?
Is there a way to sort by just month and year?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
Hi.
Try:
SELECT OrderID, SalesDate
FROM tblSales
ORDER BY Year(SalesDate), Month(SalesDate);
.. . . where tblSales is the name of the table and SalesDate is the name of
the field containing the date to be sorted, first by year, then by month.
However, if you want to sort by month, and then by year, try:
SELECT OrderID, SalesDate
FROM tblSales
ORDER BY Month(SalesDate), Year(SalesDate);
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
Try:
SELECT OrderID, SalesDate
FROM tblSales
ORDER BY Year(SalesDate), Month(SalesDate);
.. . . where tblSales is the name of the table and SalesDate is the name of
the field containing the date to be sorted, first by year, then by month.
However, if you want to sort by month, and then by year, try:
SELECT OrderID, SalesDate
FROM tblSales
ORDER BY Month(SalesDate), Year(SalesDate);
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
G
Guest
When I tried what you said I get a message that says Invalid Syntax. How can
I fix it?
I fix it?
6
'69 Camaro
Please post your SQL statement here, and we'll take a look at it.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
G
Guest
I don't have an SQL statement. I was doing it in a query.
6
'69 Camaro
You were using the QBE (Query By Example) grid to create a query. This
automatically creates the SQL statement that Jet reads and executes to
"query the database." To view this SQL statement, select the View -> SQL
View menu and text will appear in a white window. That text is the SQL
statement. Please copy that text in its entirety and paste it into your
next message.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
automatically creates the SQL statement that Jet reads and executes to
"query the database." To view this SQL statement, select the View -> SQL
View menu and text will appear in a white window. That text is the SQL
statement. Please copy that text in its entirety and paste it into your
next message.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
G
Guest
This is what it said...
SELECT Expr1.Date FROM tblMain ORDER BY Year(Date), Month(Date);
When I tried to close the window it says...
Characters found at end of SQL statement.
SELECT Expr1.Date FROM tblMain ORDER BY Year(Date), Month(Date);
When I tried to close the window it says...
Characters found at end of SQL statement.
G
Guest
Your query is missing something, too. Like where does the Expr1 table name
come from? And you'll have trouble if you try to code something that uses a
Reserved word for the name. For example, Date is a Reserved word because it
is the name of a VBA function. You should rename the Date field in your
table to something meaningful, such as OrderDate. After you make the change
and save the table and then close it, come back to this query and change it
to:
SELECT OrderDate
FROM tblMain
ORDER BY Year(OrderDate), Month(OrderDate);
.. . . and replace OrderDate in my example with the new name you gave yours.
Ensure that there are no characters after that final semicolon on the last
line.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
come from? And you'll have trouble if you try to code something that uses a
Reserved word for the name. For example, Date is a Reserved word because it
is the name of a VBA function. You should rename the Date field in your
table to something meaningful, such as OrderDate. After you make the change
and save the table and then close it, come back to this query and change it
to:
SELECT OrderDate
FROM tblMain
ORDER BY Year(OrderDate), Month(OrderDate);
.. . . and replace OrderDate in my example with the new name you gave yours.
Ensure that there are no characters after that final semicolon on the last
line.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
G
Guest
Here is the code:
SELECT EvalDate
FROM tblMain
ORDER BY Year(EvalDate), Month(EvalDate);
I am still getting the same popup: "Characters found at end of SQL
statement." But, There is nothing at the end of the statement. What is going
on?
--RT
SELECT EvalDate
FROM tblMain
ORDER BY Year(EvalDate), Month(EvalDate);
I am still getting the same popup: "Characters found at end of SQL
statement." But, There is nothing at the end of the statement. What is going
on?
--RT
M
Michel Walsh
Hi,
Start a brand new query, in SQL view, then cut and paste the code from here
(NOT from your existing query).
Hoping it may help,
Vanderghast, Access MVP
Start a brand new query, in SQL view, then cut and paste the code from here
(NOT from your existing query).
Hoping it may help,
Vanderghast, Access MVP
G
Guest
Did you copy the text from from the Web page? You may have copied
"invisible" characters when you pasted them into the query. Try moving the
cursor to the very end of the line after the semicolon and then press the
<END> key on your keyboard. Does the cursor jump somewhere else? If not,
then move the cursor down a line and press the <END> key again. Does it jump
somewhere else? If not, check every "blank" line after the line with the
semicolon to see if there's any invisible invisible characters.
If you don't find anything, but Jet is reading something there, then that's
an indication of database file corruption. First, create a backup of your
database, then do a compact/repair.
Next, create an entirely new query. When you are at the QBE grid again,
select the View -> SQL -> SQL View to open the SQL View pane again. Type
(don't paste) the SQL statement into the new query:
SELECT EvalDate
FROM tblMain
ORDER BY Year(EvalDate), Month(EvalDate);
.. . . and then run the query. Do you have any further problems?
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
"invisible" characters when you pasted them into the query. Try moving the
cursor to the very end of the line after the semicolon and then press the
<END> key on your keyboard. Does the cursor jump somewhere else? If not,
then move the cursor down a line and press the <END> key again. Does it jump
somewhere else? If not, check every "blank" line after the line with the
semicolon to see if there's any invisible invisible characters.
If you don't find anything, but Jet is reading something there, then that's
an indication of database file corruption. First, create a backup of your
database, then do a compact/repair.
Next, create an entirely new query. When you are at the QBE grid again,
select the View -> SQL -> SQL View to open the SQL View pane again. Type
(don't paste) the SQL statement into the new query:
SELECT EvalDate
FROM tblMain
ORDER BY Year(EvalDate), Month(EvalDate);
.. . . and then run the query. Do you have any further problems?
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
G
Guest
YES!!! It worked. Thank you so much for your help.
I opened a new query and just started over. Now things are working
beautifully.
--RT
I opened a new query and just started over. Now things are working
beautifully.
--RT
6
'69 Camaro
Congratulations! I'm glad you've got it working! Delete that bad query if
you haven't already. Bad Joo-Joo has no place in your database.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
you haven't already. Bad Joo-Joo has no place in your database.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.