Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Question on VBA Coding

Posted on 2014-04-15
16
331 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
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 57
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 57
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 36

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 49

Accepted Solution

by:
Gustav Brock earned 500 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 49

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 49

Expert Comment

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

/gustav
0
 
LVL 57
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 36

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 49

Expert Comment

by:Gustav Brock
ID: 40005276
You are welcome!

/gustav
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

828 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