Solved

Question on VBA Coding

Posted on 2014-04-15
16
320 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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 1

Author Comment

by:Anthony6890
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
Ok, great.  I'll try it tomorrow in the office and let you know the results.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:Anthony6890
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
Like I showed, without using Right().

/gustav
0
 
LVL 1

Author Comment

by:Anthony6890
Comment Utility
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 49

Expert Comment

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

/gustav
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
Gustav, your fn worked.  I adjusted the addmonth to one month prior and it all works well.  Thank you.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You are welcome!

/gustav
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now