Displaying just the date from a date/time field
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Displaying just the date from a date/time field
I designed a query this morning to display a sum of data that has date/time but I only want to display date so that a grouping will only show one specific date.
SELECT quDailyProfitSum.Tradedate, quDailyProfitSum.Symbol, Sum(quDailyProfitSum.SumOfProfit) AS [Daily P/L]
FROM quDailyProfitSum
GROUP BY quDailyProfitSum.Tradedate, quDailyProfitSum.Symbol
HAVING ((Day([tradedate])=8));
The record source for Thia query is
SELECT quTrades.Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS SumOfProfit
FROM quTrades
GROUP BY quTrades.Tradedate, quTrades.Symbol, quTrades.ActionID
HAVING (((quTrades.ActionID)=47 Or (quTrades.ActionID)=49) AND ((Day([Tradedate]))=8));
How can I change so that only one date groups out of several date/times?
SELECT quDailyProfitSum.Tradedate, quDailyProfitSum.Symbol, Sum(quDailyProfitSum.SumOfProfit) AS [Daily P/L]
FROM quDailyProfitSum
GROUP BY quDailyProfitSum.Tradedate, quDailyProfitSum.Symbol
HAVING ((Day([tradedate])=8));
The record source for Thia query is
SELECT quTrades.Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS SumOfProfit
FROM quTrades
GROUP BY quTrades.Tradedate, quTrades.Symbol, quTrades.ActionID
HAVING (((quTrades.ActionID)=47 Or (quTrades.ActionID)=49) AND ((Day([Tradedate]))=8));
How can I change so that only one date groups out of several date/times?
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Displaying just the date from a date/time field
Try this:
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS SumOfProfit
FROM quTrades
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol, quTrades.ActionID
HAVING (((quTrades.ActionID)=47 Or (quTrades.ActionID)=49) AND ((Day(quTrades.Tradedate))=8));
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS SumOfProfit
FROM quTrades
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol, quTrades.ActionID
HAVING (((quTrades.ActionID)=47 Or (quTrades.ActionID)=49) AND ((Day(quTrades.Tradedate))=8));
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
You tried to execute a query that does not include the specific expression '(quTrades.ActioID=47 Or qutrades.ActionID=49 and Day(quTrades.Tradedate)=8' as part of an aggregate function.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Displaying just the date from a date/time field
How about
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS SumOfProfit
FROM quTrades
WHERE quTrades.ActionID In (47,49) AND Day(quTrades.Tradedate)=8
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS SumOfProfit
FROM quTrades
WHERE quTrades.ActionID In (47,49) AND Day(quTrades.Tradedate)=8
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
Yes, except the date field displays a Julian number. How to format that "date" to Date?
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Displaying just the date from a date/time field
Set the Format property of that column to Short Date or Long Date, according to your preference.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
Final solution
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS [Daily P/L]
FROM quTrades
WHERE (((quTrades.ActionID) In (47,49)) AND ((Day([quTrades].[Tradedate]))=8))
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol;
In my format of the field Short or Long Date wasn't a visible choice, but I typed in Short Date and it accepted an formatted correctly.
Thanks
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS [Daily P/L]
FROM quTrades
WHERE (((quTrades.ActionID) In (47,49)) AND ((Day([quTrades].[Tradedate]))=8))
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol;
In my format of the field Short or Long Date wasn't a visible choice, but I typed in Short Date and it accepted an formatted correctly.
Thanks
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
Even though this worked, short term memory is a bit of an issue. This is specific and that will be an issue in the future, like next month or even year,
how would the criteria of the date be updated to only 2/8/23? That criterion is currently 8, for today that means yesterday but a month from now should be how? I don't need that change for several days, so ponder that for a while.
how would the criteria of the date be updated to only 2/8/23? That criterion is currently 8, for today that means yesterday but a month from now should be how? I don't need that change for several days, so ponder that for a while.
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
Tinkering around I guessed at:
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS [Daily P/L]
FROM quTrades
WHERE (((quTrades.ActionID) In (47,49)) AND ((Day([quTrades].[Tradedate]))=9) AND ((Month([quTrades].[Tradedate]))=2))
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol;
Is the year 4 digits long?
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS [Daily P/L]
FROM quTrades
WHERE (((quTrades.ActionID) In (47,49)) AND ((Day([quTrades].[Tradedate]))=9) AND ((Month([quTrades].[Tradedate]))=2))
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol;
Is the year 4 digits long?
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
Yes it is a four digit number so finally the query is
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS [Daily P/L]
FROM quTrades
WHERE (((quTrades.ActionID) In (47,49)) AND ((Day([quTrades].[Tradedate]))=9) AND ((Month([quTrades].[Tradedate]))=2) AND ((Year([quTrades].[Tradedate]))=2023))
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol;
Pick a day/month/year.
SELECT Int(quTrades.Tradedate) AS Tradedate, quTrades.Symbol, Sum(quTrades.Profit) AS [Daily P/L]
FROM quTrades
WHERE (((quTrades.ActionID) In (47,49)) AND ((Day([quTrades].[Tradedate]))=9) AND ((Month([quTrades].[Tradedate]))=2) AND ((Year([quTrades].[Tradedate]))=2023))
GROUP BY Int(quTrades.Tradedate), quTrades.Symbol;
Pick a day/month/year.
-
- 2StarLounger
- Posts: 117
- Joined: 22 Feb 2022, 09:04
Re: Displaying just the date from a date/time field
So what do you run for tomorrow or another day?
Using Access 2007/2019.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Please, please use code tags when posting code snippets, click the </>icon.
Debug.Print is your lifesaver.
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
Open the query in design view go to the last three fields, pick the day, pick the month, pick the year and run it.
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
I ran the query that has as its record source all the records, and put in Day([Tradedate])= 9, yesterday, and received a number of trades that I wasn't looking for, so the three date criteria is necessary.
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
Interesting, the query doesn't work for today??
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Displaying just the date from a date/time field
Do you have records for today with ActionID 47 or 49
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
Yes as you may see.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Displaying just the date from a date/time field
In that case, I don't know what causes the problem without seeing (a copy of) the database.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
You have mail.
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Displaying just the date from a date/time field
quDailyP/L is based on quTrades.
quTrades has the following WHERE-clause:
WHERE (((Day([TRADEDATE]))=9) AND ((Month([TRADEDATE]))=2))
so it only returns records for the 9th of February.
If you then specify today's date (the 10th of February) in the criteria of quDailyP/L, nothing is returned.
You should either modify the criteria for the 2 queries in sync, or specify them only in one of them.
(By the way, I'd use a form to specify the criteria instead of editing the query/queries each time.)
quTrades has the following WHERE-clause:
WHERE (((Day([TRADEDATE]))=9) AND ((Month([TRADEDATE]))=2))
so it only returns records for the 9th of February.
If you then specify today's date (the 10th of February) in the criteria of quDailyP/L, nothing is returned.
You should either modify the criteria for the 2 queries in sync, or specify them only in one of them.
(By the way, I'd use a form to specify the criteria instead of editing the query/queries each time.)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1402
- Joined: 08 Jul 2016, 18:53
Re: Displaying just the date from a date/time field
I remember that, now I was looking at my statement to verify what I was seeing. Everything is ok with my query now.
I think I'll change the source to the table to prevent anything like happening again.
I think I'll change the source to the table to prevent anything like happening again.