Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Question on VBA Coding

Posted on 2014-04-15
16
Medium Priority
?
338 Views
Last Modified: 2014-04-16
Hi All,

I have the below code that is supposed to subtract one month from the AddMonth if the Day of the Month is an 01, but it isn't doing it.  I didn't write this code, so any help would be greatly appreciate...

fnDenEligible = DateSerial(Year(Hire_2), Month(Hire_2) + AddMonth + (Right(Hire_2, 2) = "01"), 1)

Open in new window

0
Comment
Question by:Anthony6890
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 58
ID: 40002478
um....it's adding a number of months (AddMonth) to Hire_2 and making it the first day of that month.

So if AddMonth = 2 and Hire_2 is  12/15/13, then the result would be 02/01/14.

Should do what you want if you pass a negative for add month.

I think I would have called it  "MonthOffset" rather then AddMonth.

Jim.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40002487
I get that part, but I need it to work this way. If Hire_2 was 12/1/2013 and the add month was 3, then the result should be 3/1/2013.  But if Hire_2 was 12/2/2013 and the add month was 3, the result should be 4/1/2013...
0
 
LVL 58
ID: 40002493
Change it to:

fnDenEligible = DateSerial(Year(Hire_2), Month(Hire_2) + AddMonth + IIF(Right(Hire_2, 2) = "01",-1,0), 1)

 Relying on the check to return -1 as true is not a hot idea.

Jim.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 1

Author Comment

by:Anthony6890
ID: 40002497
So what happens with this if the result of fnDenEligible before the check of the day is 01/01- with the -1 bring it back to 12/01 of the previous year?
0
 
LVL 58
ID: 40002521
Yes and you can try it for yourself.  Open a code window, then hit Ctrl/G, then type:

?  DateSerial(Year("13/01/01"), Month("13/01/01") + AddMonth + IIF(Right("13/01/01", 2) = "01",-1,0), 1)

and hit return.   12/01/12 is the result

Jim.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40002607
Ok, great.  I'll try it tomorrow in the office and let you know the results.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40002699
I have a bunch of date functions I created to return certain values.  This one returns the first day of the month for the given date.  So if you add 3 months to 4/15/14, you end up with 7/15/14 which will result in 7/1/14 if passed to this function.
NewDate = FstDayOfMth(DateAdd("m", 3, Hire_2))


Public Function FstDayOfMth(InDate As Date) As Date
    FstDayOfMth = DateSerial(Year(InDate), month(InDate), 1)
End Function

Open in new window

0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40003370
This is just another example why you always must avoid handling dates as strings.
The original code will work, but only for some date formats like yyyy-mm-dd.

So always handle dates as dates. No exceptions.
Here is how:

fnDenEligible = DateSerial(Year(Hire_2), Month(Hire_2) + AddMonth + Abs(Day(Hire_2) > 1), 1)

/gustav
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40004380
Jim, you're function didn't work.  Hire_2 is in this format:  mm/dd/yyyy.  For example, someone has the date of 1/1/2013 and the fnDenEligible is displaying 20130501 which is wrong it should be 20130401.

Gustav, I understand what you are saying.  How would you suggest handling this?  

Thank you in advance.  

-Anthony
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40004389
Like I showed, without using Right().

/gustav
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40004399
Gustav, let me ask you a question.  For this person, I had their add Month as 4 because if they were hired 1/2/2013, their fndenelig should be 4/1/2013 but if they were hired 1/1/2013 and fndenelig should be 3/1/2013.  Should the addmonth be 3 now instead?
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40004410
Yes, I thought it was 3 - from your example data.

/gustav
0
 
LVL 58
ID: 40004452
<<Jim, you're function didn't work.  Hire_2 is in this format:  mm/dd/yyyy. >>

 Well as gustav pointed out, that's because your working with a date as a string.   The function I posted only works if the day appears last:  YYYYMMDD or YYMMDD

 But certainly mm/dd/yyyy would not fly.

Jim.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40004828
OK, let's try again.  You want to add some number of months to a date.  If that date is the first of the month, no adjustment is necessary.  If that date is any other day, then move the resulting date to the first day of the NEXT month.  So, the function below handles that a little differently.  It adds "AddMonth" if the date is the first of the month or it adds "AddMonth" +1 if it is any other day.  Then the FstDayOfMth() returns the first day of the month for any date passed to it.  So if you pass in the first, you get back the first.  If you pass in the third, you get back the first.

NewDate = FstDayOfMth(DateAdd("m", IIf(Day(Hire_2) = 1,AddMonth, AddMonth +1), Hire_2))


Public Function FstDayOfMth(InDate As Date) As Date
    FstDayOfMth = DateSerial(Year(InDate), month(InDate), 1)
End Function 

Open in new window


You can't add "AddMonth" to the month in isolation as you are doing because that will not handle rolling into a new year correctly.  As has already been pointed out - ALWAYS work with date fields that are defined as dates.  NEVER attempt to do this type of processing on strings or date parts.    You don't need the function I created, you can just use built in functions but to do that, you would still need the DateSerial() function that my wrapper function uses.  The difference would be that you would need to replace the two instances of InDate with the entire expression that includes the DateAdd() and IIf() in the beginning of the example.
0
 
LVL 1

Author Comment

by:Anthony6890
ID: 40004869
Gustav, your fn worked.  I adjusted the addmonth to one month prior and it all works well.  Thank you.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40005276
You are welcome!

/gustav
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question