none
DateAdd Trouble RRS feed

  • Question

  • I am trying to set the value of a field based on what is entered in a different field.  What i mean is i need to have a DateAdd computation, where the user inputs the number of days, and the DateAdd function fills in the End_Date Field.

    This is what i have:

     Private Sub Days_Changed()
                Dim Dinterval As DateInterval
                Dim Days As Double
                Dim Start_Date As Date
                Dinterval = DateInterval.Day

                Me.[End_Date] = DateAdd(Dinterval, [Days], [Start_Date])
            End Sub

    No matter whats entered in the Days Field.  I get 01/01/1001

    Tuesday, August 23, 2011 3:07 PM

Answers

  • Try explicitly casting the parameters to the correct type (but you will have to check that the fields actually contain a value as they are nullable).

    Private Sub Days_Changed()
      Me.[End_Date] = DateAdd(DateInterval.Day, CDbl(Me.[Days]), CDate(Me.[Start_Date]))   
    End Sub


    Simon Jones
    • Marked as answer by Newbie Wayne Tuesday, August 23, 2011 7:01 PM
    Tuesday, August 23, 2011 6:12 PM

All replies

  • You've defined new variables but not given them a value. They are not necessary.

    If you want to take Days and Start_Date from the entity you'd write something like:

    Private Sub Days_Changed()
      Me.[End_Date] = DateAdd(DateInterval.Day, Me.[Days], Me.[Start_Date])    
    End Sub
    

    Simon Jones
    Tuesday, August 23, 2011 3:34 PM
  • I am getting the following errors.  I went back to the table to check datatypes  End_Date is Date,  Days is Double, Start_Date is Double.

     

    Error  1    Overload resolution failed because no accessible 'DateAdd' can be called without a narrowing conversion:
        'Public Function DateAdd(Interval As String, Number As Double, DateValue As Object) As Date': Argument matching parameter 'Interval' narrows from 'Microsoft.VisualBasic.DateInterval' to 'String'.


        'Public Function DateAdd(Interval As String, Number As Double, DateValue As Object) As Date': Argument matching parameter 'Number' narrows from 'Double?' to 'Double'.


        'Public Function DateAdd(Interval As Microsoft.VisualBasic.DateInterval, Number As Double, DateValue As Date) As Date': Argument matching parameter 'Number' narrows from 'Double?' to 'Double'.


        'Public Function DateAdd(Interval As Microsoft.VisualBasic.DateInterval, Number As Double, DateValue As Date) As Date': Argument matching parameter 'DateValue' narrows from 'Date?' to 'Date'.    C:\Documents and Settings\Wayne Williams\My Documents\Visual Studio 2010\Projects\Application17\Application17\Common\UserCode\BillInfo.vb    25    29    Common

    Tuesday, August 23, 2011 5:46 PM
  • Start_Date Is Date.  TYPO!!!!!!!
    Tuesday, August 23, 2011 5:47 PM
  • Try explicitly casting the parameters to the correct type (but you will have to check that the fields actually contain a value as they are nullable).

    Private Sub Days_Changed()
      Me.[End_Date] = DateAdd(DateInterval.Day, CDbl(Me.[Days]), CDate(Me.[Start_Date]))   
    End Sub


    Simon Jones
    • Marked as answer by Newbie Wayne Tuesday, August 23, 2011 7:01 PM
    Tuesday, August 23, 2011 6:12 PM
  • Hi 

    Simon's answer is correct to achieve what you want to have defined two values:

    A start date date type.

    A number of intervals to add.

    Usually this is defined in two other fields in the same table, and simply apply the formula based on those values.

    Jaime

    Tuesday, August 23, 2011 6:15 PM
  • Sorry Just felt a tremor from an earthquake, but back to work now and ill give this a shot.  Thanks for showing me about the CDbl and CDate aspects.  I am learning, frustrating but fun.

     

    Tuesday, August 23, 2011 6:37 PM
  • That worked like a charm.  I never new about the CDbl or the CDate but i am still taking a class in VB.  Thank you guys you have been a big help.

    Now one last issue and i am done with this project.

    how to sort by fiscal year.

    Calculated field will not work. 

    Thanks again both of you.

    Tuesday, August 23, 2011 7:10 PM
  • Hi 

    That order required?

    Jaime

    Tuesday, August 23, 2011 7:23 PM
  • I am looking to have my list / details screen paged by fiscal years.

    there will only be 12 entries per year per page,  per account

    Tuesday, August 23, 2011 8:00 PM
  • Please to give more information?
    Tuesday, August 23, 2011 8:06 PM
  • Fiscal Year dates generally come in the same order as calendar year dates so it makes no difference for sorting. So sorting by calendar date should be good enough. If you think differently you will have to explain some more about what you want to achieve.

    (Fiscal Years do make a difference for grouping but that is a different matter.)

     


    Simon Jones
    Tuesday, August 23, 2011 10:04 PM
  • I agree that the Fiscal Year follows the same order as calendar year logic. I just wanted to make it easy for my users that each page in on my account screen (which is list detail) is a fiscal year.   So that being said page one would be all entries for an account in 2007,  Page 2 all entries for 2008.   So that is a grouping issue right?  And that would have to be done in the query filters?

    Wednesday, August 24, 2011 11:29 AM
  • Yes I'd say that would be a filtering problem, not sorting.

    It's not really grouping either but that depends what you mean by "Page".

    Paging in LightSwitch is the term used to limit the number of records retrieved from the database to a particular (fixed) number - the default is 45. This is to speed up the response of the application ensuring a user doesn't have to wait while thousands of rows are retrieved. LightSwitch will only retrieve the first n (45) and then wait for the user to review those and decide if he/she wants to edit one or move on to the next "page" of (45) results.

    Because the paging number is fixed it is not really possible to use this mechanism to group results as you don't know how many will be in each group.


    Simon Jones
    Wednesday, August 24, 2011 11:38 AM
  • You have been a great help.  I must say.  Now what do you use for reporting.  I have looked at the extensions and they all look similar.

    Wednesday, August 24, 2011 12:05 PM
  • SQL Server Reporting Services.

    • It is free with SQL Server.
    • It is easy to install on the SQL Server box or another server.
    • It integrates with SharePoint (if you want it to).
    • Users can design their own reports using the free Report Builder tool.
    • Developers can use Visual Studio.
    • You can make high level reports drill down to more detailed reports.
    • You can make lists, tables or Pivot Tables.
    • You can include charts and maps.
    • You can show reports in Windows Forms or Web applications.
    • (There is no built-in Silverlight viewer, yet, but you can buy a third party one.)
    • From LightSwitch just shell out to show the appropriate report page in a web browser.
    • It exports reports to Excel, PDF etc.
    • Users can schedule reports to be run on a schedule they specify and have the report sent to them automatically by email.

    Simon Jones
    Wednesday, August 24, 2011 12:13 PM
  • HA !  Going to Run To IT Right Now!  I am testing this on the Express Edition.  I need to see what they have.

    Wednesday, August 24, 2011 12:30 PM
  • SSRS is free with SQL Server Express edition but some of the more advanced features such as integration with SharePoint or scheduling reports are only available in Standard Edition and above.

    See http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx


    Simon Jones
    Wednesday, August 24, 2011 1:11 PM
  • Hi 

    I think the article below also may be interested in:

    Printing Sql Server Reports (.rdlc) With LightSwitch

    Jaime

    Wednesday, August 24, 2011 3:00 PM
  • The scheduling is a must.  Waiting for a visit from Santa, I mean IT.   I see I have the Express Edition, and then there is Express Advance, Ill look into what my options are.  I have the database in Express.

    And i see that Jaime H has an article too.

    The info is much appreciated

    Wednesday, August 24, 2011 3:05 PM
  • Looking at this right now...
    Wednesday, August 24, 2011 3:05 PM
  • "SQL Server Express with Advanced Services Features" is just SQL Server Express with extra tools and is what you need for Reporting Services. It is still free.

    SQL Server 2008 Express with Advanced Services provides the following capabilities:

    • Basic installation of SQL Server Management Studio, a graphical management tool that makes it easy to manage and administer SQL Server Express databases.

    • Reporting Services.

    • BI Development Studio. This provides an integrated report creation and design environment to create reports.

    • Full-text Search, a powerful search engine for searching text-intensive data.

    SQL Server Express is JUST the database engine.

    See http://msdn.microsoft.com/en-us/library/ms365248.aspx


    Simon Jones
    Wednesday, August 24, 2011 3:52 PM
  • Again thanks for your help yesterday,  We are attempting to save money where ever possible, to keep positions, my Lightswitch solution while ever so rudimentary, by some standards will get the job done.  Now i will go look at how to get the reports i need done in the report builder 3.0.  I am still going to have to group things by fiscal year. (sigh).
    Thursday, August 25, 2011 12:13 PM
  • Hi 

    Perhaps you require is to get the records filtered by date range. If so see the following example of a query that serves as the basis for a EditableGrid which automatically creates two local properties that allow the user to set the range dates with which to filter certain colletion of records.

     

    Private Sub CarsSoldBetween_PreprocessQuery(P_StartDate As System.Nullable(Of Date), P_EndDate As System.Nullable(Of Date), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.CarSold))
          If P_StartDate.HasValue AndAlso P_EndDate.HasValue Then
            query = query.Where(Function(a) a.SoldDate >= P_StartDate And a.SoldDate <= P_EndDate)
          End If
        End Sub
    

    Jaime

     

    Thursday, August 25, 2011 1:48 PM
  • heck i need that too Jamie..  If a user has to look up all electric bills from a provider from a date range.  So i will modify that for my project.  

    What i did with the fiscal year  problem was to go back  to the server tables and add a column, FiscalYear1 as Int.   Back in lightswitch I updated my data-source and now I have a column of FiscalYear1 now usable, unlike the calculated field.   My goal was to Piggyback the result i needed for my field to be generated while the computed field Fiscal Year calculated. I am using a simple calculation of:  Me.[FiscalYear1] = IIf(Month(Me.[Start_Date]) > 6, Year(Me.[Start_Date]), Year(Me.[Start_Date]) - 1), the computed field was the same thing, it just returned 2007, 2008, 2009, 2010 ETC!!! 

    but unlike the computed field i can now use my field FiscalYear1.

    Crazy i know but until i learn more visual basic it will do for a bit.

    Thursday, August 25, 2011 2:11 PM
  • Glad to read that you are finding solutions to their needs within Lightswitch.

    Jaime

    Thursday, August 25, 2011 2:16 PM
  • Thank you very much. I searched for weeks. Now I resolved.

    Harald

    Sunday, January 15, 2012 7:09 PM