Month Number in VBA

[pib311 via AccessMonster.com]

Hi all, thanks in advance.

I have a table with a few projects, months, and accounting information as
such:

Project Month Amount (WHAT I WANT MONTH NUMBER)
Proj1 3/1/2009 100 1
Proj1 4/1/2009 150 2
Proj1 5/1/2009 200 3
Proj2 4/1/2009 100 1
Proj2 5/1/2009 150 2

Project 1 has its first month in 3/1/09 whereas Proj 2 is in 4/1/09. To be
able to compare project accounting info against other projects, I wanted to
include a column that would indicate which month that data set is for, for
instance, 3/1/09 would be month number 1 for Proj1.

I figured the best way to do this was through VBA, but don't know where to
start. Any suggestions?

 

[Jeff Boyce]

Check Access HELP for the Month() command.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[GB]

Once you figure out how to get out the data you want from the date, you
should be able to see that what you will have is a calculated field. There
is no need (and breaks normalization) to store the month number if it is
infact calculated from a date field. Therefore it could be calculated on the
fly either in VBA or in a query for example.

If however, it is truly something not based off of another field, then yeah
go fill in your table.

Thing is though, the example provided in your data table appears to have no
correlation to the data presented in the month field. In some cases it is 2
months before the entered date, and in others it is as much as 3 months off.
You could perform an update query on your table once you create the field, if
you don't have too many "unique" records to update where you use the month
conversion and subtract applicable value from the result. Don't know how
many projects you are talking about.

Check Access HELP for the Month() command.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[pib311 via AccessMonster.com]

Jeff,

Thanks for the help. I am familiar witht the month() function, but it will
not accomplish what I need to do. I am not looking to get to the calendar
month, but more or less a dynamic number that specifies the month's ascending
order for a given project.

The function you specified would provide the following:

Month(#3/1/2009#) as 3 where in my example Proj1 it would have been 1, since
it is the first instance of data for that project.

Check Access HELP for the Month() command.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all, thanks in advance.

[quoted text clipped - 16 lines]

I figured the best way to do this was through VBA, but don't know where to
start. Any suggestions?

 

[John Spencer]

You could use an expression like the following in a query. This should work
fairly well if you have only a few records. If there are a lot of records,
then you might be better off using a nested query.


DateDiff("m",MonthDate,DMin("MonthDate","theTable","Project=""" & [Project] &
""""))


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

So it sounds like you are saying that the number you are after is the
Month() of some date minus the Month() of a project's first date...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the help. I am familiar witht the month() function, but it will
not accomplish what I need to do. I am not looking to get to the calendar
month, but more or less a dynamic number that specifies the month's
ascending
order for a given project.

The function you specified would provide the following:

Month(#3/1/2009#) as 3 where in my example Proj1 it would have been 1,
since
it is the first instance of data for that project.

Check Access HELP for the Month() command.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all, thanks in advance.

[quoted text clipped - 16 lines]

I figured the best way to do this was through VBA, but don't know where
to
start. Any suggestions?

 

[Jeff Boyce]

Hmmmm? What happens when a project starts in December and runs for 2.5
years? John's DateDiff() function would be better...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for the help. I am familiar witht the month() function, but it will
not accomplish what I need to do. I am not looking to get to the calendar
month, but more or less a dynamic number that specifies the month's
ascending
order for a given project.

The function you specified would provide the following:

Month(#3/1/2009#) as 3 where in my example Proj1 it would have been 1,
since
it is the first instance of data for that project.

Check Access HELP for the Month() command.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all, thanks in advance.

[quoted text clipped - 16 lines]

I figured the best way to do this was through VBA, but don't know where
to
start. Any suggestions?

 

[CuriousMark]

Not at my computer so I can't come up with the SQL, but try this:

Use a sum query to determine the StartMonth of each project (minimum of
Month for each project). Then combine that result with the table in a second
query and use a calculated field to find the difference between month and
StartMonth. Remember to add 1. If the project goes past December, then use an
IIF statement with the year(month) function to add 12*years to the result if
the current month is in a different year from the StartMonth.

 

[CuriousMark]

Finally got to my computer, so I could work on the SQL. Here is the first
totals query. I changed the field names to ProjName and Period instead of
Project and Month, to avoid confusion:

SELECT tblProjects.ProjName, Min(tblProjects.Period) AS MinOfPeriod
FROM tblProjects
GROUP BY tblProjects.ProjName;

Then, the second query uses the table and the above query (qryStartDate),
joined on the ProjName field. Turns out you don't need the IIF statement
because if it is the same year, that result factors to zero. ProjMonth is the
calculated field you want.

SELECT tblProjects.ProjName, tblProjects.Period, tblProjects.Amount,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1 AS ProjMonth
FROM tblProjects INNER JOIN qryStartDate ON tblProjects.ProjName =
qryStartDate.ProjName
ORDER BY tblProjects.ProjName,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1;

 

[GB]

So, actually what you want is for each project, to sort by date in ascending
order and assign a sequential number to that date. If you want it on the
fly, then may I recommend a query?

The next question though is if you need to have every record in your
database presented at once, or do you need to only have that information as
you are looking at records for a given project?

If it is for records of a given project, then you can use a query built in
access without VBA and you could use like a query wizard or build it
yourself. The query would sort the dates (placed before your "counter") and
a user defined field that has an autonumber.

If you want to use all of the records, then I would recommend VBA and sort
of do an update query.

Basically pull out each project, then get your recordset to be sorted on the
date. For each date in the project, increment a counter and set your
recordset field for that projects date equal to your new number. When you
are done with that project, reset your counter to 1, and go to the next
project and reperform.

I don't know the details of the SQL to do the sorting, but if you had a
sorted query that has a enforced referential integrity relationship with your
original data, then you could use the query as your record source. Also if
you had a query that returned unique records on just the project list and had
an enforced referenctial integrity relationship assigned, then you could also
use that query to go through each project. Would need two counters, or two
types of loop. Either a For I = 1 to max records, or a for each c in
Recordset.

Jeff,

Thanks for the help. I am familiar witht the month() function, but it will
not accomplish what I need to do. I am not looking to get to the calendar
month, but more or less a dynamic number that specifies the month's ascending
order for a given project.

The function you specified would provide the following:

Month(#3/1/2009#) as 3 where in my example Proj1 it would have been 1, since
it is the first instance of data for that project.

Check Access HELP for the Month() command.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all, thanks in advance.

[quoted text clipped - 16 lines]

I figured the best way to do this was through VBA, but don't know where to
start. Any suggestions?

 

[pib311 via AccessMonster.com]

The datediff function is a great suggestions. I attempted to incorporate the
code, and then found out that there can be gaps within months (project goes
on hold and month is not included in data). For the purpose of this analysis,
the skpped months would not get a value. I have about 5000 records to run
through, and this is all being done within a query, not a table (as I
incorrectly inferred before).

I did come up with a a loop sequence that took forever. I have included it
here for review (in case this would actually be helpfull):

function monthcounter(dt_date as date, int_project as integer)

set db = currentdb
set rst = db.openrecordset("SELECT Distinct cDate From

WHERE project
= int_project Order By cDate")

int_counter = 0

rst.movefirst

do until rst.eof

IF rst.cdate <= (date of record) then
int_counter = int_counter + 1
rst.movenext
end if

loop

where

is the table the query is based upon.

I am surprised there is not a simple way to do this. I might just have to
put this in a table and do this update above on a monthly basis.

Finally got to my computer, so I could work on the SQL. Here is the first
totals query. I changed the field names to ProjName and Period instead of
Project and Month, to avoid confusion:

SELECT tblProjects.ProjName, Min(tblProjects.Period) AS MinOfPeriod
FROM tblProjects
GROUP BY tblProjects.ProjName;

Then, the second query uses the table and the above query (qryStartDate),
joined on the ProjName field. Turns out you don't need the IIF statement
because if it is the same year, that result factors to zero. ProjMonth is the
calculated field you want.

SELECT tblProjects.ProjName, tblProjects.Period, tblProjects.Amount,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1 AS ProjMonth
FROM tblProjects INNER JOIN qryStartDate ON tblProjects.ProjName =
qryStartDate.ProjName
ORDER BY tblProjects.ProjName,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1;

Not at my computer so I can't come up with the SQL, but try this:

[quoted text clipped - 24 lines]

 

[CuriousMark]

Did you try the queries I suggested? did they not work, or was that not the
info you wanted?

The datediff function is a great suggestions. I attempted to incorporate the
code, and then found out that there can be gaps within months (project goes
on hold and month is not included in data). For the purpose of this analysis,
the skpped months would not get a value. I have about 5000 records to run
through, and this is all being done within a query, not a table (as I
incorrectly inferred before).

I did come up with a a loop sequence that took forever. I have included it
here for review (in case this would actually be helpfull):

function monthcounter(dt_date as date, int_project as integer)

set db = currentdb
set rst = db.openrecordset("SELECT Distinct cDate From

WHERE project
= int_project Order By cDate")

int_counter = 0

rst.movefirst

do until rst.eof

IF rst.cdate <= (date of record) then
int_counter = int_counter + 1
rst.movenext
end if

loop

where

is the table the query is based upon.

I am surprised there is not a simple way to do this. I might just have to
put this in a table and do this update above on a monthly basis.

Finally got to my computer, so I could work on the SQL. Here is the first
totals query. I changed the field names to ProjName and Period instead of
Project and Month, to avoid confusion:

SELECT tblProjects.ProjName, Min(tblProjects.Period) AS MinOfPeriod
FROM tblProjects
GROUP BY tblProjects.ProjName;

Then, the second query uses the table and the above query (qryStartDate),
joined on the ProjName field. Turns out you don't need the IIF statement
because if it is the same year, that result factors to zero. ProjMonth is the
calculated field you want.

SELECT tblProjects.ProjName, tblProjects.Period, tblProjects.Amount,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1 AS ProjMonth
FROM tblProjects INNER JOIN qryStartDate ON tblProjects.ProjName =
qryStartDate.ProjName
ORDER BY tblProjects.ProjName,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1;

Not at my computer so I can't come up with the SQL, but try this:

[quoted text clipped - 24 lines]

I figured the best way to do this was through VBA, but don't know where to
start. Any suggestions?

 

[GB]

I would agree, something about this function seems to imply a long search
through the data. I would think that if you are intending to use this update
on every project each time it is run, then you could use the entire recordset
as your data, but have it sorted by project and then by date.

If the project changes then reset the int_counter to 1.

The only benefit I see of comparing rst.cdate <= (date of record) is to
shorten the number of records reviewed.

Also, why is this a function if it doesn't return anything? and probably
should clean up the memory after using rst and db.

Also wondering if there is a way to step through the recordset that is
perhaps faster than movenext; although, that is very convenient to use.

I recommend the single recordset usage, only because the computer has to
open the recordset for each project, rather than opening it once and doing
all of the work associated with every project along the way. If the call to
monthcounter goes through every project, then there is no gain by repeatedly
opening and closing the recordset (which is only closed by access, not by the
programming).

The datediff function is a great suggestions. I attempted to incorporate the
code, and then found out that there can be gaps within months (project goes
on hold and month is not included in data). For the purpose of this analysis,
the skpped months would not get a value. I have about 5000 records to run
through, and this is all being done within a query, not a table (as I
incorrectly inferred before).

I did come up with a a loop sequence that took forever. I have included it
here for review (in case this would actually be helpfull):

function monthcounter(dt_date as date, int_project as integer)

set db = currentdb
set rst = db.openrecordset("SELECT Distinct cDate From

WHERE project
= int_project Order By cDate")

int_counter = 0

rst.movefirst

do until rst.eof

IF rst.cdate <= (date of record) then
int_counter = int_counter + 1
rst.movenext
end if

loop

where

is the table the query is based upon.

I am surprised there is not a simple way to do this. I might just have to
put this in a table and do this update above on a monthly basis.

Finally got to my computer, so I could work on the SQL. Here is the first
totals query. I changed the field names to ProjName and Period instead of
Project and Month, to avoid confusion:

SELECT tblProjects.ProjName, Min(tblProjects.Period) AS MinOfPeriod
FROM tblProjects
GROUP BY tblProjects.ProjName;

Then, the second query uses the table and the above query (qryStartDate),
joined on the ProjName field. Turns out you don't need the IIF statement
because if it is the same year, that result factors to zero. ProjMonth is the
calculated field you want.

SELECT tblProjects.ProjName, tblProjects.Period, tblProjects.Amount,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1 AS ProjMonth
FROM tblProjects INNER JOIN qryStartDate ON tblProjects.ProjName =
qryStartDate.ProjName
ORDER BY tblProjects.ProjName,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1;

Not at my computer so I can't come up with the SQL, but try this:

[quoted text clipped - 24 lines]

I figured the best way to do this was through VBA, but don't know where to
start. Any suggestions?

 

[GB]

Yesterday after writing, I was thinking of what CuriousMark has suggested.
If you can identify the first date for each project (potentially using an
array) then you should be able to use the datediff function that CuriousMark
has suggested (or one similar) to determine how many months have passed for
the project in question to be able to compare it with a "similar" project.

The fastest way I know to determine the first date for each project is to
sort the data by project first, then by date and for each unique project take
the first date value returned.

If you developed a function that is able to be called from a query, then the
function could be passed the project number and date associated with the
current record. Within the function it would look up the oldest date
associated with that project, then perform a month to month calculation of
the "date difference".

Would probably have to add the value of 1 to the result, or at least ensure
that if the current date is equal to the oldest date that you get a value of
1, and if one month later, that you get the value of 2.

This of course assumes that the months data in question always is entered
for the month it describes. I.e., say christmas time, wouldn't want to have
12/1 and 12/24 to describe two separate months unless you apply some sort of
midmonth cutoff in the date difference calculation.

In this regards, there is no programming needed to step through all of the
records like the procedure that was posted, access handles most of it.

Did you try the queries I suggested? did they not work, or was that not the
info you wanted?

The datediff function is a great suggestions. I attempted to incorporate the
code, and then found out that there can be gaps within months (project goes
on hold and month is not included in data). For the purpose of this analysis,
the skpped months would not get a value. I have about 5000 records to run
through, and this is all being done within a query, not a table (as I
incorrectly inferred before).

I did come up with a a loop sequence that took forever. I have included it
here for review (in case this would actually be helpfull):

function monthcounter(dt_date as date, int_project as integer)

set db = currentdb
set rst = db.openrecordset("SELECT Distinct cDate From

WHERE project
= int_project Order By cDate")

int_counter = 0

rst.movefirst

do until rst.eof

IF rst.cdate <= (date of record) then
int_counter = int_counter + 1
rst.movenext
end if

loop

where

is the table the query is based upon.

I am surprised there is not a simple way to do this. I might just have to
put this in a table and do this update above on a monthly basis.

Finally got to my computer, so I could work on the SQL. Here is the first
totals query. I changed the field names to ProjName and Period instead of
Project and Month, to avoid confusion:

SELECT tblProjects.ProjName, Min(tblProjects.Period) AS MinOfPeriod
FROM tblProjects
GROUP BY tblProjects.ProjName;

Then, the second query uses the table and the above query (qryStartDate),
joined on the ProjName field. Turns out you don't need the IIF statement
because if it is the same year, that result factors to zero. ProjMonth is the
calculated field you want.

SELECT tblProjects.ProjName, tblProjects.Period, tblProjects.Amount,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1 AS ProjMonth
FROM tblProjects INNER JOIN qryStartDate ON tblProjects.ProjName =
qryStartDate.ProjName
ORDER BY tblProjects.ProjName,
(Year([Period])-Year([MinOfPeriod]))*12+(Month([Period])-Month([MinOfPeriod]))+1;

Not at my computer so I can't come up with the SQL, but try this:

[quoted text clipped - 24 lines]
I figured the best way to do this was through VBA, but don't know where to
start. Any suggestions?