Queries and Date Fields

[BurtonBach]

I constantly have problems with this and I am hoping someone can explain it to me. Does Access always output text into a query regardless of what the formating of the data is in the table?

I am generally needing to query by date and then confine the output to a specific date range. Once again I am having problems and I am wondering if it is because the field is no longer in date format after the query is performed. So when I include the "Between (11/1/2006) And (11/30/2006)" in the Criteria field it gives me no returns.

I am working with a very simple query. Here is a copy of the SQL language of the query:

SELECT Transactions.Date, Transactions.Card1, Transactions.TransTotal
FROM Transactions;

I would greatly appreciate any explanations and suggestions.
Thank you!

[ADezii]

I constantly have problems with this and I am hoping someone can explain it to me. Does Access always output text into a query regardless of what the formating of the data is in the table?

I am generally needing to query by date and then confine the output to a specific date range. Once again I am having problems and I am wondering if it is because the field is no longer in date format after the query is performed. So when I include the "Between (11/1/2006) And (11/30/2006)" in the Criteria field it gives me no returns.

I am working with a very simple query. Here is a copy of the SQL language of the query:

SELECT Transactions.Date, Transactions.Card1, Transactions.TransTotal
FROM Transactions;

I would greatly appreciate any explanations and suggestions.
Thank you!

Specifying "Between (11/1/2006) And (11/30/2006)" as a Criteria tells the Query Engine to search for that specifc String and not a Date Range. The fact that you did not mention if you received a Data Type Mismatch Error tells me that your Date Field is defined as Text and not Date/Time. The Criteria for searching a date Field for a given range would be Between #11/1/2006# And #11/30/2006#. The underlying Field, however, must be defined as Date/Time in order to assure accurate results.

[BurtonBach]

Thank you! That worked. So am I safe to take from this that performing a query does not change the format of the date field? (I am trying to increase my understanding of Access in general.)

Also, now that that is working I seem to have another problem. I want to group the data coming out of the query by date. I have set the first column to group and the second colum to sum and yet I am getting approx 10 entries all from 12/1/2006. Do I need to do something else to get them to goup?

[NeoPa]

This may be helpful (Literal DateTimes and Their Delimiters (#).)

[ADezii]

Thank you! That worked. So am I safe to take from this that performing a query does not change the format of the date field? (I am trying to increase my understanding of Access in general.)

Also, now that that is working I seem to have another problem. I want to group the data coming out of the query by date. I have set the first column to group and the second colum to sum and yet I am getting approx 10 entries all from 12/1/2006. Do I need to do something else to get them to goup?

Be more specific on the Fields involved in the GROUP BY and SUM clauses

[BurtonBach]

Please let me know if any more info would be helpful.

Here is my query displayed in SQL:
SELECT Transactions.Date, Sum(Transactions.TransTotal) AS SumOfTransTotal
FROM Transactions
GROUP BY Transactions.Date
ORDER BY Transactions.Date;

And here is a sample of the data returned:
Date SumOfTransTotal
12/13/2005 5.12
12/13/2005 5.12
12/13/2005 5.12
12/13/2005 5.12
12/15/2005 1.15
12/15/2005 1
12/15/2005 0.12
12/15/2005 6.56
12/16/2005 19.14
12/16/2005 12
12/16/2005 8.16
12/17/2005 11.11

Why will it not group all the 12/13 or 12/15's together? I have done other queries like this and not had this problem. What am I overlooking?

Thanks,

[NeoPa]

If Transactions.Date is stored as a Date/Time field and has a time component which is simply not displayed, then that would explain this behaviour.

[BurtonBach]

What do you recommend as a solution? Cdate? Format command? I am not experienced at all with this kind of problem. Thanks.

I just had another thought. Is there a way to put in wild cards for the time stamps so that it will still group by the day?

[NeoPa]

In short No.
You will need to use :

SELECT [Date],Sum([TransTotal]) AS SumOfTransTotal
FROM Transactions
GROUP BY Format([Date],'yyyymmdd')
ORDER BY Format([Date],'yyyymmdd')

[BurtonBach]

I attempted to translate your suggestion into my database with the following code:

SELECT Transactions.Date, Sum(Transactions.TransTotal) AS SumOfTransTotal
FROM Transactions
GROUP BY Transactions.Date, Format([Transactions.Date],'yyyymmdd')
ORDER BY Transactions.Date, Format([Transactions.Date],'yyyymmdd');

And the output is:

Date SumOfTransTotal
12/13/2005 5.12
12/13/2005 5.12
12/13/2005 5.12
12/13/2005 5.12
12/15/2005 1.15
12/15/2005 1
12/15/2005 0.12
12/15/2005 6.56
12/16/2005 19.14
12/16/2005 12
12/16/2005 8.16
12/17/2005 11.11
12/19/2005 9.68

It still has not grouped all the 12/13s. Are there other possible reasons for this besides a timestamp that is unseen in the date field?

[NeoPa]

Yes.
The possible, in fact definite, reason it doesn't work is that you changed it not to. Of course your version didn't work. You included the Date field - hence GROUPing by the time element by definition.
Why do you come back here and ask for more help before even trying the version I suggested?

[BurtonBach]

I am sorry if somehow I have messed up. Did I miss something? If I did it was innocent. I tried the code you suggested exactly as suggested and it returned the following error:

You tried to execute a query that does not include the specified expression 'Date' as part of an aggregate function.

I was trying to learn from what you wrote and thought that if I attempted to change the table names I might figure it out. I don't understand what I did that was wrong. The ability to ask questions in these networks is important to me. I will not do whatever I am not supposed to do.

[NeoPa]

You're good Burton.
You confused me by leaving out the info you just posted. From your earlier post it seemed as if you'd just decided, without checking what I'd posted, to make your own changes.
Making your own changes after my code failed, is a good thing. Not telling me the sequence so that I could understand too, was where the confusion arose.
As I say - not a problem.
Your error message makes sense and indicates to me what I had overlooked.
A more reliable version should be (and I have used a different concept here in view of the problem found) :

SELECT CDate(Int(Transactions.Date)) AS [Date],
       Sum([TransTotal]) AS SumOfTransTotal
FROM Transactions
GROUP BY CDate(Int(Transactions.Date))
ORDER BY CDate(Int(Transactions.Date))

[BurtonBach]

Wow. That seems to have worked. I am going to run some other steps and hopefully it will take. Thank you very much!

One other quick question. Without getting a book on SQL (which I plan to do) is there somewhere on this network or on the web that has a Cliff Notes version of when to use brackets and when to use parenthesis and other language formatting tips?

Thank you again!

[NeoPa]

Not that I'm aware of I'm afraid.
You may find something if you follow through on some of the links in (Links to useful sites)

Oh, and btw, you may express surprise when my code fails - but when it works - that's just harsh ;)