Converting MM/DD in Table to Days in Month in Query

[Ssnake57]

My Data in my Table is listed by Date, in my query I need to run a calculation on the number of days in that particular Month. Is this possible?

Larry

 

[MichaelRed]

Probably not what you are asking, but the expression for the number of days in a calendar month is easily derived:

MyDate = date()
? MyDate
2/1/03 
DaysInMnth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))
? DaysInMnth
 28 
MyDate = Date - 3
? Date
2/1/03 
DaysInMnth = Day(DateSerial(Year(MyDate), Month(MyDate) + 1, 0))
? DaysInMnth
 31

All of which may (and actually has been) easily encapsulated in a formal procedure.

The real question, however, is more probably to devine the number of days within a specific calendar month for which records exist within a specific recordset?

A clue here is here is the keyword distinct, as well as the generation of a parameterized query.

The SQL for such a thing vould look like:

SELECT DISTINCT Count(tblHolidates.HoliName) AS NumDts
FROM tblHolidates
WHERE (((tblHolidates.HoliDate) Between DateSerial(Year([MyDt]),Month([MyDt]),1) And (DateSerial(Year([MyDt]),Month([MyDt])+1,1))))
WITH OWNERACCESS OPTION;

Of course, you can just copy / paste this into the SQL view of a query and edit the table and field names to suit your db schema and 'run' it from the qbe screen. It will prompt you for a date, and you may enter any date within hte month of interest. In a production app, you will want to me a but more sophisticated, perhaps supplying the parameter via some other mechanisim, but at least with this you should be able to see the process.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[r937]

michaelred, i do believe the DISTINCT in your query is superfluous

larry, if all you want is to count the number of records in a particular month, for example february, use

select count(*)
from yourtable
where month(yourdate) = 2

if you want to know how many records by day in a particular month,

select day(yourdate), count(*)
from yourtable
where month(yourdate) = 2
group by day(yourdate)

this will list counts only for days that actually occur in the table, if you want all days listed, with 0 for the count where there were no records, you'll need a left join from some table which has all numbers from 1 to the end of that month -- holler if you want an example

rudy

 

[Ssnake57]

MichaelRed,
This looks like what I need. One question in the first line MyDate = date () can it be MyDate = [any specified date] WHer any specified date = the date field within my query?

Larry

 

[MichaelRed]

yes
MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Ssnake57]

Thanks MichaelRed for your help on this. I am probably doing this wrong somewhere, but my mind has locked up on this and I am stumped. I pasted the following in a new query SQL,

SELECT DISTINCT Count([ce_site_volumes]![volume_dt]) AS NumDts
FROM [ce_site_volumes]
WHERE ((([ce_site_volumes]![volume_dt]) Between DateSerial(Year([volume_dt]),Month([volume_dt]),1) And (DateSerial(Year([volume_dt]),Month([volume_dt])+1,1))))
WITH OWNERACCESS OPTION;

Where - [volume_dt] = 09/30/02

I need this to return the number of days in September (30)

at this time it is returning (53)

Where did I screw it up?

Larry

 

[r937]

"I need this to return the number of days in September (30)"

i don't mean to sound like a smart aleck, but you don't need a query to figure that out

do you want to count the number of records you have where the volume_dt is any day in september 2003?

or do you want the number of records where the volume_dt is in september any year?

do you want october 1st included or not?

these are all factors in deciding what your WHERE conditions should be


rudy

 

[Ssnake57]

Well r937,
The situation I have is as follows. I am working with an operations data base that records runtime within a month. All of the data was recorded once a month, usually during the last week but not always on the same date. Sample - I have run hours of 157 and mechanical downtime listed as 320 on a specific machine entered on 09/30/02. This is 9 months worth of data on 800 machines. I need a query or a formula that will relate the date of entry with the month of operations and give me the total number of days within that month, whereby I can multiply this by 24 to get the total number of hours. Then I can get the operational runtime % for that month.

IE

09/30/02 = 09 (Month)
09 = 30 (Days)
30*24= 720 (Hours)

157/720 = 21.80% runtime

The number of units running, the number of days running; all of that I have. I need a formula that I can plug into the queries that were already written when I got here that will give me the total days in any given month when it is listed by any date within that month.

Ssnake

 

[r937]

so michaelred was right, that is what you were asking

you could also pull the number of days in a month out of the air, so to speak

consider this string:

312831303130313130313031

start with month(yourdate), multiply by 2, and subtract 1

this gives you a starting position in the string, and you pull out two characters...

mid('312831303130313130313031'
, month(yourdate)*2-1, 2)

this gives you the number of days in the month

uh oh, i forgot about february in leap years

hmmm, maybe use michaelred's formulas after all...

 

[MichaelRed]

([volume_dt]),Month([volume_dt])+1,1))))

should be

([volume_dt]),Month([volume_dt])+1,0))))

(The LAST number needs to be a ZERO ("0&quot as a number.

Actually, if all you need is the actual number of DAYS in a month, the expression works with out the subtraction, as in:

MyDate = Date() 'Today is Monday, Feb. 3, 2003
? Day(DateSerial(Year(MyDate), Month(Mydate) + 1, 0))
28

although with minor modification, r937's soloution would work for the remainder of the century

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Ssnake57]

MichaelRed & r937,

You guys are great! The string function was the easiest to plug into my existing query and it is working beautifully. I truly appreciate your help, and this forum. I am self taught on Access and I muddle through, it is nice to have people to go to that are able, and most of all willing to help.

Larry

 

[MichaelRed]

... but realize it (r's string thinggy) is an error waiting to happen (in ~ 1 Year), unless you make hte necessary mods for leap year ...


easy, perhaps, but fraught with hazzard -particularly for the unwary (and perhaps un-observant) novice ...


MichaelRed
m.red@att.net

Searching for employment in all the wrong places