something like EOMONTH() in excel

[Nicky]

Forgive me for being so thorough. I try to be because I am sure there are people who know less than me and I try to make it easier to understand what is going on when viewed later.
In Excel there is a formula END OF THE MONTH or EOMONTH(). One is able to pass a cell's value through it and count how many months ahead or behind from that date. The examples of how that works is:
For month 12/3/2002 last day: eomonth(12/3/2002,0) spits out: 12/31/2002
For Current Month’s last day: eomonth(today(),0) spits out last day of current month
For Last day of month 12 months ago: eomonth(today(),-12) Spits out last day of 12 months ago’s month
For first day of next month: eomonth(today(),0)+1 Spits out first day of next month
And so on….
Upon looking through the forum, I did see something that described what I was looking for but when I used it, it didn’t seem to work. It seems that there isn’t a stock formula in Access that does this already and I agree with their approach to the problem which was to make a formula like the excel formula. For the sake of example, I will just call this formula eomonth()
I would like to do something like this in a query in the criteria box. I want my query to only show records left in this month. I envision that I could plug in <=eomonth(date(),0) in the DueDate field and have it only show those records in this month. Please let me know if that is how I should write the formula in the criteria. I think that that is right.
If you could please tell me how and where I would put this formula. I think I should just put it in a regular madule but should it go in a class module? I really don’t know the difference. If you have a different approach or thought on this please let me know, I am all eyes.
I am sorry, I am self taught in Access and sometimes I feel like I have a box with just half the puzzle pieces. I mean I talk the lingo like “passing variables” instead of “plugging in” but I feel like I learned everything piece meal and I ask questions of a novice sometimes. For instance I know what a module is persay but have no clue why it would necessarily be a class module. I also should figure out how to create and refer to global variables and also how to declare them but I am sure that will come some other time. Thanks in advance.
Again, the thread i read seemed to be the right approach but it seemed like the author and the mentor (answerer of the thread) were making assumtions of their knowledge and that they made a mistake and corrected it. I couldnt get it to work for myself. In the end apparently the author got it to work but for me there remains some ambiguity. :banghead:

 

[Galaxiom]

Put this code in a Standard Module

Public Function eomonth(ByVal InputDate As Date, ByVal Offset As Integer) As Date
 
     eomonth = DateSerial(Year(InputDate), Month(InputDate) + Offset + 1, 0)
 
End Function

It should have some error handling added too.

 

[Nicky]

Thank you so much for your time. Since I want to learn and also for anyone subsequently looking allow me to dissect this.

I took a java class once so some concepts are coming back.

It is a public Function so that it can be used anywhere in the application.

ByVal is how I am referencing the variable. I know there are 2 ways and I cannot remember the other way but here it is just storing the value of the variable and not the reference in memory right?

There are 2 inputs into the function or 2 parameters. they will be passed into the function when the function is called. Those variables are InputDate and Offset.

They are declared as date and as an integer

eomonth is also declared as a date (at the end of the line) The parameters are in the parentheses.

Next line

eomonth's value is set to the expression

Dateserial is a VBA Function that is called. Year and Month are functions too and they are nested in Dateserial. InputDate is passed as a parameter into year and date

That date will translate into the year parameter (first one of dateserial), month parameter (second one of dateserial) is adjusted by offset and then by +1 (this must be to accomodate the rule i set forth for the current month to be zero. Lastly 0 is the last parameter of the dateserial function.

I played with the function in the immediate window. By the way to any student who wants to see how i did that it was:

err = DateSerial(2012, 8+0+1, 0)
then you have to "print err"
when you change any of the parameters you must return from that line so that the variable "err" is reset and then print the new err

I discovered by playing with it that it just created a date by the year, Month i just inputed the 0 where offset would be and then I figured out that the last parameter if made a "1" gives the first day of that month. So what the formula does is if a 0 is entered is go back one day (the month before). One wants to go in that direction since month endings could be 4 different days 30 and 31, 28 or 29. So formula goes 9/1/2012 (sorry brits thats how we write it in USA) and then the 0 goes back one. The Month is 8 from the passing into the formula. since i wanted my formula to instructions to use a 0 for current month, negative for past and positive for future the plus 1 does the correction to bring it one month forward and then the 0 goes back the one day. Perfect. thank you.

I am cool to get graded on how i explained what i learned (especially if I said something wrong) but now I will try to get it into the query. that shouldnt be hard but I will shoot back a question on that if I have a problem. Thanks, the concepts were easy but in a new language sometimes its easier to have someone help you out. I am not totally cool yet with all the symantics either so this helps as a reference

 

[Galaxiom]

Chip Pearson has a good introduction to many VBA concepts. This is his offering on ByVal vs ByRef.
http://www.cpearson.com/excel/byrefbyval.aspx

BTW If you want to see a value using the Immediate Window just type something like:
? DateSerial(2012,8+0+1,0)

You can include multiple commands in one line by separating them with a colon.
err = DateSerial(2012,8+0+1,0): ? err

 

[Nicky]

Thanks so much will check it out