Creating current month criteria
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Creating current month criteria
I saw a few create month criteria for a specific month, but how would it be for the current month?
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating current month criteria
You could use a calculated column in a query
TheMonth: Int([DateField])-Day([DateField]
where DateField is your date field and set the Criteria row to
Date()-Day(Date())
Clear the Show check box for his column.
Alternatively:
TheMonth: Format([DateField],"yyyymm")
with Criteria
Format([Date(),"yyyymm")
TheMonth: Int([DateField])-Day([DateField]
where DateField is your date field and set the Criteria row to
Date()-Day(Date())
Clear the Show check box for his column.
Alternatively:
TheMonth: Format([DateField],"yyyymm")
with Criteria
Format([Date(),"yyyymm")
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
Hmm
I used Int([SettledDate])-Day([SettledDate]) and had no return records. I guess I misunderstood your post.
I used Int([SettledDate])-Day([SettledDate]) and had no return records. I guess I misunderstood your post.
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
Then I used TheMonth: Int([SettledDate])-Day([SettledDate]) with Date()-Day(Date()) in the criteria Still no records.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating current month criteria
It should work. See screenshot.
Top: table.
Middle: query in design view.
Bottom: query in datasheet view.
(I use ISO date format yyyy-mm-dd)
Top: table.
Middle: query in design view.
Bottom: query in datasheet view.
(I use ISO date format yyyy-mm-dd)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
Unless I can't type
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating current month criteria
That looks correct. So either there are no records with SettledDate in the current month, or there is something wrong with the SettledDate field. I'd have to see a copy of the database...
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
You have it I believe. Same as the queries you helped me with in another thread recently.
Look familiar?
ETA: Dates are in the form of mm/dd/yyyy
Code: Select all
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS TotalCount, Sum(QuTrades.Profit) AS TotalSum, -Sum([Profit]>0) AS PosCount, -Sum([Profit]<0) AS NegCount, [PosCount]/[TotalCount] AS PosPerc, [NegCount]/[TotalCount] AS NegPerc, Avg(QuTrades.Profit) AS TotalAvg, -Sum([Profit]*([Profit]>0)) AS PosSum, IIf([PosCount]=0,Null,[PosSum]/[PosCount]) AS PosAvg, -Sum([Profit]*([Profit]<0)) AS NegSum, IIf([NegCount]=0,Null,[NegSum]/[NegCount]) AS NegAvg
FROM QuTrades
GROUP BY QuTrades.Symbol
ORDER BY Last(QuTrades.SettledDate) DESC;
ETA: Dates are in the form of mm/dd/yyyy
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating current month criteria
This doesn't work?
Code: Select all
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS TotalCount, Sum(QuTrades.Profit) AS TotalSum, -Sum([Profit]>0) AS PosCount, -Sum([Profit]<0) AS NegCount, [PosCount]/[TotalCount] AS PosPerc, [NegCount]/[TotalCount] AS NegPerc, Avg(QuTrades.Profit) AS TotalAvg, -Sum([Profit]*([Profit]>0)) AS PosSum, IIf([PosCount]=0,Null,[PosSum]/[PosCount]) AS PosAvg, -Sum([Profit]*([Profit]<0)) AS NegSum, IIf([NegCount]=0,Null,[NegSum]/[NegCount]) AS NegAvg
FROM QuTrades
WHERE SettledDate-Day(SettledDate)=Date()-Day(Date())
GROUP BY QuTrades.Symbol
ORDER BY Last(QuTrades.SettledDate) DESC;
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
I'm terribly embarrassed, but I left a dangling criterion in the SettledDate field which removed all the records. Your query work and I ask for forgiveness.
Thanks Hans
Thanks Hans
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
Now I ask how to include this bit of code into the Sql I posted above. I attempted to add the expression but got an error the expressions wasn't part of an aggregate(?), so I deleted that and just added the criteria to the SellementDate field and that cut the observed output to nil.
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
I double posted and didn't see you reply, yes it does work.
Thanks
Thanks
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
How would you set up a query to select a specific month/year i.e. 04/2021 and/or year. If this takes more than one or more additional queries so be it
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating current month criteria
For a specific year such as 2021, use
WHERE Year(SettledDate)=2021
For a specific month and year, for example 04/2021, use
WHERE Format(SettledDate,"mm/yyyy") = "04/2021"
WHERE Year(SettledDate)=2021
For a specific month and year, for example 04/2021, use
WHERE Format(SettledDate,"mm/yyyy") = "04/2021"
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
Code: Select all
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS TotalCount, Sum(QuTrades.Profit) AS TotalSum, -Sum([Profit]>0) AS PosCount, -Sum([Profit]<0) AS NegCount, [PosCount]/[TotalCount] AS PosPerc, [NegCount]/[TotalCount] AS NegPerc, Avg(QuTrades.Profit) AS TotalAvg, -Sum([Profit]*([Profit]>0)) AS PosSum, IIf([PosCount]=0,Null,[PosSum]/[PosCount]) AS PosAvg, -Sum([Profit]*([Profit]<0)) AS NegSum, IIf([NegCount]=0,Null,[NegSum]/[NegCount]) AS NegAvg
FROM QuTrades
WHERE ((Format([SettledDate],"mm/yyyy")="04/2021"))
GROUP BY QuTrades.Symbol
ORDER BY Last(QuTrades.SettledDate) DESC;
The yearly had modifications, but displayed what seems reasonable.
Code: Select all
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS TotalCount, Sum(QuTrades.Profit) AS TotalSum, -Sum([Profit]>0) AS PosCount, -Sum([Profit]<0) AS NegCount, [PosCount]/[TotalCount] AS PosPerc, [NegCount]/[TotalCount] AS NegPerc, Avg(QuTrades.Profit) AS TotalAvg, -Sum([Profit]*([Profit]>0)) AS PosSum, IIf([PosCount]=0,Null,[PosSum]/[PosCount]) AS PosAvg, -Sum([Profit]*([Profit]<0)) AS NegSum, IIf([NegCount]=0,Null,[NegSum]/[NegCount]) AS NegAvg
FROM QuTrades
WHERE (((Year([SettledDate]))="2021"))
GROUP BY QuTrades.Symbol
ORDER BY Last(QuTrades.SettledDate) DESC;
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating current month criteria
An alternative for month/year:
WHERE Year([SettledDate])=2021 AND Month([SettledDate])=3
WHERE Year([SettledDate])=2021 AND Month([SettledDate])=3
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1412
- Joined: 08 Jul 2016, 18:53
Re: Creating current month criteria
If I deleted the = "2021" in the yearly would I get an input box for the year? Same for the month/year. That way I wouldn't need to hard code specific dates in the code and make it more general?
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Creating current month criteria
WHERE Year([SettledDate])=[Specify the year]
and
WHERE Year([SettledDate])=[Specify the year] AND Month([SettledDate])=[Specify the month (1-12)]
and
WHERE Year([SettledDate])=[Specify the year] AND Month([SettledDate])=[Specify the month (1-12)]
Best wishes,
Hans
Hans