Results 1 to 10 of 10

Thread: Sending value

  1. #1
    Join Date
    Oct 2005
    Posts
    55

    Unanswered: Sending value

    I have a form in my database that accepts a start date and and end date. There is a field on the form "Amount of days" that calculates the amount of days between the start date and end date. What I have done was to call my function in the control source properties that calculates the amount of days which works.However the table I used to build the form in which the "Amount of days" is does not capture the value calculated.

    How can I send the calculated value on the form to the "Amount of days"field in the table used to make the form?

    I am trying to figure this out since Tuesday.

    Thanks very much for the help.

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Have you bound the "Amount of days" control to the field in your table? If so, you will have to use the AfterUpdate event of the Start and End date textboxes to set the value of the "Amount of days" control. You can't bind a control and have it be a calculated field at the same time.

    Why not just save the start and end date as fields in your table and calculate the "Amount of days" in a query or via a calculated control on a form?
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    One of the rules of database design is "Don't store anything you can calculate"

    You can use your function in your queries and reports, too.

    However, all you are really asking is "How do I save a value from an unbound field on a form?"

    You (apparently) have some code that generates the value and updates the field on the form. You just need to add to that code:

    Code:
    ...
    strSql="Insert into sometable (somevalue) Values (yourcalculation)"
    Docmd.Runsql strSql
    ...
    Of course, not knowing your app, you may want to use an UPDATE instead of INSERT.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Oct 2005
    Posts
    55
    RedNeckGeek thanks very much for your help. I am using code to calculate the difference in dates entered but I am not sure how to use the piece of code you give me which may work for me.

    (samevalue)- what does this mean? what do I put there?

    (yourcalculation)- same thing?

    How do I get the actual value generated by the calculations into a field in a table?

    Have a look at the code I am using.

    Function funWorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long
    Dim lngTotalDays As Long
    Dim lngTotalWeeks As Long
    Dim dtNominalEndDay As Date
    Dim lngTotalHolidays As Long
    Dim lngstart As Long
    Dim lngend As Long



    'Check to see if dtStartDay > dtEndDay. If so, then switch the dates
    If dtStartDay > dtEndDay Then
    dtNominalEndDay = dtStartDay
    dtStartDay = dtEndDay
    dtEndDay = dtNominalEndDay
    End If
    'Here are how many weeks are between the two dates
    lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
    'Here are the number of weekdays in that total week
    lngTotalDays = lngTotalWeeks * 5
    'Here is the date that is at the end of that many weeks
    dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
    'Now add the number of weekdays between the nominal end day and the actual end day
    While dtNominalEndDay <= dtEndDay
    If Weekday(dtNominalEndDay, 2) <> 6 Then
    If Weekday(dtNominalEndDay, 2) <> 7 Then
    lngTotalDays = lngTotalDays + 1
    End If
    End If
    dtNominalEndDay = dtNominalEndDay + 1
    Wend

    'convert end date and startdate into long integer format for the DCount operation to avoid misreading of dates as US format
    lngstart = dtStartDay
    lngend = dtEndDay

    'Here are how many holiday days there are between the two days
    lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= " & lngend & " AND dtObservedDate >= " & lngstart & " AND Weekday(dtObservedDate,2) <> 6 AND Weekday(dtObservedDate,2) <> 7")

    'Here are how many total days are between the two dates - this is inclusive of the start and end date
    funWorkDaysDifference = lngTotalDays - lngTotalHolidays


    End Function

    How do I bring in the code you are sharing with me?

    Thanks very much for your help.

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What the hell is a funWorkDay? (just kidding)

    (somevalue) and (yourcalculation) are generic names where
    you would substitute your actual field name and the value from your
    calculation.

    What you need to do is capture the value returned by the function.
    So, rather than call your function directly as the data source for the control,
    let's add another function to the picture.

    For this exercise, I'm going to call the unbound control "ctl1".

    Code:
    Function PopulateValues()
    Dim strSql as string, lngValue as long
    'not sure where your getting the date arguments from
    lngValue=funWorkDaysDifference(dtStartDay, dtEndDay)
    'assign the values to the control
    ctl1=lngValues
    'append the value to the table
    strSql="INSERT INTO YOURTABLE (YOURFIELD) VALUES (" & lngValue & ")"
    Docmd.RunSQL strSql
    Inspiration Through Fermentation

  6. #6
    Join Date
    Oct 2005
    Posts
    55
    RedNeckGeek you have been a great help and I want to thank you very very very much for it.

    I have used what you have send me but I am getting an error.

    Error 28 out of stack space.

    Which hightlights "lngValue=funWorkDaysDifference(dtStartDay, dtEndDay)" as the error.

    I do not understand what is happening now!!! What am I doing wrong.

    Can you please look at the code I use I may have gone wrong somewhere along the line.

    Thanks for the help again.

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What values have you supplied for dtStartDay and dtEndDay when the function is called?

    Out of Stack Space could mean that you are calling a function that is already
    in the "Stack". I suppose this could happen if you're using the function I supplied as the control source property for your control. Instead, call the function from the AfterUpdate property of your EndDate control.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by RedNeckGeek
    One of the rules of database design is "Don't store anything you can calculate"
    What is the justification for storing this value?
    It seems like you're creating a lot of work for yourself when you needn't do.
    George
    Home | Blog

  9. #9
    Join Date
    Oct 2005
    Posts
    55
    Why can I not call the function within "Function funWorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long" the one that I already have?

    If I can how do I do that?

    I am not sure how to call a function within another function.

    Thanks again for the help.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Call <Function Name>
    OR
    <Function Name>()

    all depends on the context, try them both.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •