# Thread: Function needed (Access 2000)

1. ## 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. ## 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. ## 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. ## 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.

5. ## 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. ## 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. ## 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.

#### Posting Permissions

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