Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function needed (Access 2000)

    If you are using 9 different formulas, it is impossible to say what could be done if you post one bit of code out of context. Neither S_Date nor MS_Date is set in this fragment (and what does . and 8. mean?)

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function needed (Access 2000)

    I have taken over a existing Access project that uses 9 different formulas about 6 times in different places on a form. What I would like to do is create a function that could be called
    to give me the end result of the formula and cut down on the amount of code.
    This is part of the code that has one of the the formulas. I'm interested in returning MS_Date value.

    Dim MR_Start, MR_End As Long
    Dim D1, D2, D3, D4, S_Date, E_Date As Date
    Dim MR1, MR2, MR3, MR4 As Long
    .
    .
    .
    MR_Start = MR2 + (MR3 - MR2) / DateDiff("d", D2, D3) * DateDiff("d", D2, S_Date)
    DC = DateDiff("d", D3, D4)
    If DC < 14 Then
    DC = DateDiff("d", D2, D4)
    If DC < 14 Then
    DC = DateDiff("d", D1, D4)
    If DC < 14 Then
    MR_Start = 0
    MR_End = 0
    Else
    MR_End = MR4 + (MR4 - MR1) / DateDiff("d", D1, D4) * DateDiff("d", D4, E_Date)
    End If
    Else
    MR_End = MR4 + (MR4 - MR2) / DateDiff("d", D2, D4) * DateDiff("d", D4, E_Date)
    End If
    Else
    MR_End = MR4 + (MR4 - MR3) / DateDiff("d", D3, D4) * DateDiff("d", D4, E_Date)
    End If
    MS_Date = S_Date

    thanks for the assistance.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function needed (Access 2000)

    Hans,
    I edited the original post. The dots"." I just used to show that there was other code before the piece I show in the post.
    Every one of the values (D1,MR2,MR_Start...etc.) are set earlier in the code.
    This is just one of the formulas that return MS_Date with the correct value.
    Would the function have to start like this:

    Function CalC(lngMR2, lngMR5, lngMR_Start, lngMR_End As Long, dteD2, dteD5, dteS_Date, dteE_Date As Date)
    and then the formula placed next. I'm trying that but having no luck.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function needed (Access 2000)

    One of your problems is going to be that your variables are not being declared as the types you expect. In VBA, you have to dim each variable as a specific datatype or it is declared as a variant, which may not behave as expected.

    Since we don't know what is actually going on in your code, there's really no way to answer your question.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function needed (Access 2000)

    I am very sorry, but the code fragment you posted is meaningless without its context. It contains absolutely no clue of what it does, or should do, or how it is to be used. There is no way to help you if you don't provide the relevant information.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function needed (Access 2000)

    Thanks anyway,
    I hate to post the code here (22 pages long for one button click). I think it would be to hard to explain here what the code does in the application.
    I've streamlined it down to a mere 17 pages so far. I'll keep playing .
    Scott

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function needed (Access 2000)

    If you have 17 pages of code for one button click, then you probably need to start breaking that code in modular routines that each handle a single aspect of the operation and then call each routine from your button click in some sequence instead of stuffing all the possible code into that single routine. It will be easier to understand, easier to debug, and easier to post.
    Charlotte

Posting Permissions

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