Solved

Lightswitch difference between two dates in months (VB) does not have to be exact

Posted on 2013-06-27
8
798 Views
Last Modified: 2013-06-28
I have two date entries that are filled out with random dates (datfields). I want to compute the time between two dates in months. Dividing the days by 30 is fine does not have to be exact. The computed field is a String. Not sure if this matters or if it would be better as an integer.

I know the following is not correct, but it should give some type of idea of what I am trying to do.


            If Not StartDate Is Nothing And Not EndDate Is Nothing Then
                result = (StartDate - EndDate) /30
0
Comment
Question by:Rlm214
[X]
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
  • 4
  • 4
8 Comments
 
LVL 40
ID: 39283581
Assuming dates in d1 and d2:

DateDiff(DateInterval.Month, d2, d1)
0
 

Author Comment

by:Rlm214
ID: 39284644
I've tried the above. However it seems that actual dates need listed in that code to work. I have two dates for each entry tracking in my program. I need it to look at those two dates and come up with the difference in months.

I receive the following error with the code below.

Datediff(DateInterval.Month, StartDate, EndDate)

I get the error "Overload resolution failed because no acessible 'DateDiff' can be called wthout a narrowing conversion:........
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 39285222
The method works on Date variables. Your dates are probably in another format such as a String.

Try the following:

Datediff(DateInterval.Month, CDate(StartDate), CDate(EndDate))
0
Independent Software Vendors: 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!

 

Author Comment

by:Rlm214
ID: 39285281
To be more specific about the situation

Name: LeaseCommencementDate
Type: DATE

Name LeaseExpirationDate
Type: DATE

TotalTerm
Type: STRING
Properties: IS COMPUTED

EDIT METHOD =
code
Error =
error
0
 
LVL 40
ID: 39285357
If I understand, CDate corrected the call to DiffDate, but you get a new error this time.

This one happens somewhere else and might be more difficult to grasp because it does not come from your code.

Why do you store the result in a String, while it is a number? Always try to use the correct type for values. Dates should be in Date variables, numbers in the most appropriate type for the values you expect, most of the time an Integer for numbers that do not have decimals. Otherwise you often end up with conversion problems here and there.

But that might not be your new problem. Where does that MULTI_USER comes from?
0
 

Author Comment

by:Rlm214
ID: 39285381
Things seem to be on the right track. Even with the error during my upload, your code is working in my application. The months are being calculated as expected.

In my SQL database lists my date entries as DateTime. My lightswitch application lists them as Dates. I'm not sure if this has anything to do with it or not. Possibly that my computed field is a string instead of integer?

The important thing is that the code is working. I can work on the discrepancy between lightswitch and the database.
0
 

Author Comment

by:Rlm214
ID: 39285466
I resolved the error. Thanks again for your help!!!

The error was coming from publishing my lightswitch program with the "TotalTerm" as a non calculated field, then trying to publish again after making it a calculated field.

To correct the error, I deleted "TotalTerm" from my database and everything is now working as planned.
0
 
LVL 40
ID: 39285793
You do not have to "fear" differences in types between SQL Server and VB.

The Date type in VB is in fact a datetime, it always contain time information along with the date. Anyway, it does not hold the information in the same format as SQL Server, but the conversion is automatically done for you by the methods that LightSwitch use to communicate with SQL Server.

The same holds true for many types of variables. For instance, VB as only String to work with text, while SQL Server has many different types for Text. They will always be mapped correctly for you in the background.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

634 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