Query criteria's

[bknight]

I have a field that is populated by time in D/M/Yr H/M/S format. I would like to group by Day so I input Day([Time}) etc.

Code: Select all

SELECT Int(([NinjaTrader2024].[Time])) AS Tradedate, NinjaTrader2024.Instrument, Sum(NinjaTrader2024.Profit) AS [Daily P_L], Sum(Abs([Quantity])) AS [#Trades], Sum([commission]+[fees]) AS [Com&Fee]
FROM NinjaTrader2024
GROUP BY Int(([NinjaTrader2024].[Time])), NinjaTrader2024.Instrument, Year([NinjaTrader2024].[Time]), NinjaTrader2024.Account_display_name, Day([NinjaTrader2024].[Time]), Month([NinjaTrader2024].[Time])
HAVING (((Year([NinjaTrader2024].[Time]))=Year([Time])) AND ((Day([NinjaTrader2024].[Time]))=Day([Time])) AND ((Month([NinjaTrader2024].[Time]))=Month([Time])));

It out puts everything from the table. What I wanted was a single day not everything.
What do i need to change to out put only one day?
The query worked fine with inputting 2024 for the year. 3 for the month and 15 for te day, but I have to change the day everyday and I was trying to have a criteia that does te same thing as inputting a specific day.

[HansV]

In this query, [NinjaTrader2024].[Time] is exactly the same as [Time] so the HAVING clause doesn't restrict the result at all: Year([NinjaTrader2024].[Time]))=Year([Time]) is always true.
If you want to return the records for today's date, use

SELECT Int(([NinjaTrader2024].[Time])) AS Tradedate, NinjaTrader2024.Instrument, Sum(NinjaTrader2024.Profit) AS [Daily P_L], Sum(Abs([Quantity])) AS [#Trades], Sum([commission]+[fees]) AS [Com&Fee]
FROM NinjaTrader2024
WHERE Int(([NinjaTrader2024].[Time]))=Date()
GROUP BY Int(([NinjaTrader2024].[Time])), NinjaTrader2024.Instrument, Year([NinjaTrader2024].[Time]), NinjaTrader2024.Account_display_name, Day([NinjaTrader2024].[Time]), Month([NinjaTrader2024].[Time])

If you want to return the records for a specific date, either use a parameter:

SELECT Int(([NinjaTrader2024].[Time])) AS Tradedate, NinjaTrader2024.Instrument, Sum(NinjaTrader2024.Profit) AS [Daily P_L], Sum(Abs([Quantity])) AS [#Trades], Sum([commission]+[fees]) AS [Com&Fee]
FROM NinjaTrader2024
WHERE Int(([NinjaTrader2024].[Time]))=[Enter the Date]
GROUP BY Int(([NinjaTrader2024].[Time])), NinjaTrader2024.Instrument, Year([NinjaTrader2024].[Time]), NinjaTrader2024.Account_display_name, Day([NinjaTrader2024].[Time]), Month([NinjaTrader2024].[Time])

or enter it in a text box on a form, say txtDate on frmInput:

SELECT Int(([NinjaTrader2024].[Time])) AS Tradedate, NinjaTrader2024.Instrument, Sum(NinjaTrader2024.Profit) AS [Daily P_L], Sum(Abs([Quantity])) AS [#Trades], Sum([commission]+[fees]) AS [Com&Fee]
FROM NinjaTrader2024
WHERE Int(([NinjaTrader2024].[Time]))=[Forms]![frimInput]![txtDate]
GROUP BY Int(([NinjaTrader2024].[Time])), NinjaTrader2024.Instrument, Year([NinjaTrader2024].[Time]), NinjaTrader2024.Account_display_name, Day([NinjaTrader2024].[Time]), Month([NinjaTrader2024].[Time])

[bknight]

The Db is on another lap and I'll attempt the first probably to avoid creating another item. I'll post latter today or tomorrow. Thanks

[bknight]

That worked after I entered a four digit year