Calendar Month Between Dates Query

[mgmercuio]

Hello all...



I have what I hope to be a very simple question and I am sure it is, to all the experts out here....but to us mere mortals, its not so easy. I have been researching for days and cannot figure it out... LOL

Anyway so I am trying to figure out how to craft a query that will "dynamically" query between the 29th day of month 1 through the 28th day of the following month.

Let me be more specific by way of example:

Lets say I own "My Big Fat Used Cars" car dealership and I have several hard working salespeople out there very day on the lot selling cars.

I have established a predetermined "selling window" which runs from the 29th of one month to the 28th of the following month. This is "selling window" helps me keep track of cars being sold for any given month and to establish/monitor sales quotas for each of my salespersons.

Lets call this selling window, the monthly sales cycle. The fiscal sales cycle runs on a monthly basis and it runs from the 29th day of month 1 through the 28th day of the following month.

Now lets say my dealership processes the " cars sold report" on the 29th day of every single month which shows all the sales from my sellers during the sales cycle. This report reflects (by salesperson) what each salesperson has sold during the fiscal sales month. Also on the 29th day, a new sales cycle begins which will run through the 28th day of the following month...and son and so on...through the complete calendar year.

I have tasked each of my salespeople to track how many cars they have sold during the active sales cycle and give me daily reports of their sales activity.



So now lets flip the page and say I am one of those hard working salespeople out there selling cars. Because I am tech savvy salesman heh heh, I am going to create a database that tracks my sales so when the boss man or woman barks out they want my report, I have it all ready and waiting.

Now lets say I have created a database to track my daily sales called, "MyBigFatDatabase." and inside my MyBigFatDatabase, I have the following table:

tbl_MyCarsSold

Inside of MyCarsSold I have 7 fields:

ProductID (PK) Autonumber
CarModel Shorttext
CarMake Shorttext
CarYear Shorttext
Sold Yes/No
PriceSold currency
DateSold Date/Time

So....I want to be able to create a query in my database that basically says, show me all the cars sold during the monthly sales cycle. This will be achieved by checking the "Sold" Yes/No field and selecting a date in my tbl_MyCarsSold table Ok I know what you thinking...."Just do a query and use the Between criteria on the DateSold Field"....easy enough right?

But here is the catch....the query needs to be dynamic. Meaning, I need to build a query that will dynamically account for every single months sale cycle. Specifically, it will go from 29th day of each month through the 28th day (at midnight) of the following month. As you know.....the number of days in the calendar months are not always the same and I can't figure out how to create a query that will change dynamically (date wise speaking) to reflect the current sales month.

Any thoughts?


Also...as a bonus question...once I get this figured out, I plan to build a dashboard in the database that has an unbound form which I can place various text fields (some bound...and some not bound) to display various bits of monthly tracking data. For instance a unbound text box that will display the result of the query I build above...I will do this by using the DLookup function within the record source of the text box's data control. I will also create "speedometer" gauges to reflect percentage of cars sold (in terms of dollars) divided by my monthly quota. This will display my percentage of quota achieved for the active sales month by way of a "speedometer" gauge.

Anyway, these are just after thoughts and I am rambling... I apologize.

So back to the query question....

Any thoughts or help from the Access Gurus?

Sincerely in your debt for your advice.
-mgm

[moke123]

how will you handle February?

For specific days in a month you can use - DateSerial(Year(YourDate),Month(YourDate),29) or DateSerial(Year(YourDate),Month(YourDate),28)
The 29th day of february is march 1st except leap years.

Seems like an odd sales period. Why not use monthly?
To get the last day of the month you would use the 0 day of the next month -DateSerial(Year(YourDate),Month(YourDate),0)

?dateserial(year(#3/1/2024#),month(#3/1/2024#),0)
2/29/2024

?dateserial(year(#3/1/2021#),month(#3/1/2021#),0)
2/28/2021

To test for a leap year

Function IsLeapYear(yr As Long) As Boolean
    IsLeapYear = (Day(DateSerial(yr, 3, 0)) = 29)
End Function

[CJ_London]

or you can use the dateadd function to add 1 month to 'yourdate' and subtract 1

between [enter date] and dateadd("m",1,[enter date])-1

[mgmercuio]

or you can use the dateadd function to add 1 month to 'yourdate' and subtract 1

between [enter date] and dateadd("m",1,[enter date])-1

Please forgive my ignorance....but is this dynamic? or do I have to do this every month?

[mgmercuio]

how will you handle February?

For specific days in a month you can use - DateSerial(Year(YourDate),Month(YourDate),29) or DateSerial(Year(YourDate),Month(YourDate),28)
The 29th day of february is march 1st except leap years.

Seems like an odd sales period. Why not use monthly?
To get the last day of the month you would use the 0 day of the next month -DateSerial(Year(YourDate),Month(YourDate),0)

?dateserial(year(#3/1/2024#),month(#3/1/2024#),0)
2/29/2024

?dateserial(year(#3/1/2021#),month(#3/1/2021#),0)
2/28/2021

To test for a leap year

Function IsLeapYear(yr As Long) As Boolean
    IsLeapYear = (Day(DateSerial(yr, 3, 0)) = 29)
End Function

So this is dynamic? as in code it once and it calculates automatically each month?

[CJ_London]

it can be either - as stated, you would enter a date each time you run the query. But instead of [enter date] your could reference a form control instead.

Or you can use the date function - but that might be an issue if you wanted to run last months report (or any previous month) this month

[moke123]

Heres one example of getting the dates dynamically.

[mgmercuio]

Thank you both! Thank you Moke for the example db...it helped me tremendously. My coding ability is horrible and my understanding of the coding philosphy is even worse....so pictures, videos, and or real examples is when it gets through my thick skull the best... so tahnk you!

Also, thank Ajax...for the code...not only did this help me understand the concept, but you actually sparked some other ideas I will probably incorporate into the DB.

Thank you both very very much!