Displaying just the date from a date/time field

[bknight]

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?

[HansV]

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));

[bknight]

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.

[HansV]

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

[bknight]

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

Yes, except the date field displays a Julian number. How to format that "date" to Date?

[HansV]

Set the Format property of that column to Short Date or Long Date, according to your preference.

[bknight]

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

[bknight]

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.

[bknight]

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?

[bknight]

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.

[Gasman]

So what do you run for tomorrow or another day?

[bknight]

Open the query in design view go to the last three fields, pick the day, pick the month, pick the year and run it.

[bknight]

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.

[bknight]

Interesting, the query doesn't work for today??

[HansV]

Do you have records for today with ActionID 47 or 49

[bknight]

Yes as you may see.

[HansV]

In that case, I don't know what causes the problem without seeing (a copy of) the database.

[bknight]

You have mail.

[HansV]

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.)

[bknight]

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.