Creating current month criteria

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Creating current month criteria

Post by bknight »

I saw a few create month criteria for a specific month, but how would it be for the current month?

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

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")
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

Hmm
I used Int([SettledDate])-Day([SettledDate]) and had no return records. I guess I misunderstood your post.

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

Then I used TheMonth: Int([SettledDate])-Day([SettledDate]) with Date()-Day(Date()) in the criteria Still no records.

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

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)

S1257.png
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

Unless I can't type
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

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...
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

You have it I believe. Same as the queries you helped me with in another thread recently.

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;
Look familiar?

ETA: Dates are in the form of mm/dd/yyyy

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

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;
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

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

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

That's OK, it happens to all of us.
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

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.

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

See the SQL that I posted.
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

I double posted and didn't see you reply, yes it does work.
Thanks

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

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

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

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"
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

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;
This seems appropriate after modifying slighty.


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;

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

An alternative for month/year:

WHERE Year([SettledDate])=2021 AND Month([SettledDate])=3
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

Re: Creating current month criteria

Post by bknight »

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?

User avatar
HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Creating current month criteria

Post by HansV »

WHERE Year([SettledDate])=[Specify the year]

and

WHERE Year([SettledDate])=[Specify the year] AND Month([SettledDate])=[Specify the month (1-12)]
Regards,
Hans