MS Access VBA Code Help!!

had a Question that was answered 1/30/2011 http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26782103.html30/01/11 05:11 PM, ID: 34747454
 and every things works fine but gives a error when dealing with subtract time the VB help shows the date/time -date/time but wont do the math is there anything i can do to fix this?below is the solution i was giving again all code_formula works unless i subtract time/date then i get a runtime error 2434

----------------------------------------------------------------------------------------------------------------------------
Bitsqueezer:
Hi,

instead of always creating a new code and redeploy the database as MDE make your code more flexible so you don't need to ever change it.

First, create a new table and fill it with the needed formulas, example:
ID_Code	CODE_Short	CODE_Formula
1       H/R         24 * ({EndTime} - {StartTime}) * {Price}
2       M/P         ({EndMiles} - {StartMiles}) * {Price}
3       DT-H/R      24 * ({EndTime} - {StartTime}) * {Price} + 13
4       NT-H/R      24 * ({EndTime} - {StartTime}) * {Price} + 18

Open in new window


The words in the curly brackets are the variables which will be changed in the code later.

Now use something like this for your code:
Private Sub CALLTYPEID_AfterUpdate()
   Dim Price As Currency
   Dim strFormula As String
    
    [CallTypeExt] = DLookup("CallTypeExt", "tblCallTypes", "[CallTypeID] = " & [CALLTYPEID])
    
    If Nz(Me![END MILES], 0) <> 0 Then
        Price = DLookup("Rate", "tblCallTypePrices", "[CallTypeID] = " & Me!CALLTYPEID & " And #" & _
                        TimeValue(Me![START TIME]) & "# Between [StartTime] And [EndTime]")
        
        strFormula = DLookup("CODE_Formula", "tblCodes", "CODE_Short='" & Nz([CallTypeExt]) & "'")
        If strFormula <> "" Then
            strFormula = Replace(strFormula, "{StartTime}", CStr(Me![START TIME]))
            strFormula = Replace(strFormula, "{EndTime}", CStr(Me![END TIME]))
            strFormula = Replace(strFormula, "{StartMiles}", CStr(Me![START MILES]))
            strFormula = Replace(strFormula, "{EndMiles}", CStr(Me![END MILES]))
            strFormula = Replace(strFormula, "{Price}", CStr(Price))
            Me![DRIVERS PAY] = Eval(strFormula)
        Else
            Me![DRIVERS PAY] = Price
        End If
    End If
    
    DoCmd.RunCommand acCmdRefresh
End Sub

Open in new window



(I removed the "Cancel = True" as it make no sense in an AfterUpdate event.)

The code simply loads the right formula from the table "tblCodes" and if the short term from "CallTypeExt" could be found in this table it replaces all possible "variables" (the texts in the curly brackets) with the current values from the form and in the end it will be computed with Eval.
I did not test it, maybe you need to adjust it a little bit but it should show you the idea.

If you now need any additional formula simply add it to the table which you can do without any change in the code of the database and it will work immediately.

Cheers,

Christian


PLEASE HELP THIS IDEA WORKS GREAT JUST A FEW FLAWS THAT NEEDS TO BE WORKED OUT
Blueice13085Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BadotzCommented:
When you get to
Me![DRIVERS PAY] = Eval(strFormula)

Open in new window

what is the value of strFormula?
0
Blueice13085Author Commented:
strFormula = "24 * (4/2/2011 10:01:44 AM - 4/2/2011 9:07:44 AM) * 17"

this is what it shows in the VBA window when i highlight
Me![DRIVERS PAY] = Eval(strFormula)

Open in new window

0
Blueice13085Author Commented:
and the M/P works just fine wits the CODE_Formula


({EndMiles} - {StartMiles}) * {Price}
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Blueice13085Author Commented:
Aslo this code work(s) great, just cant update the code in .MDE

[CallTypeExt] = DLookup("CallTypeExt", "tblCallTypes", "[CallTypeID] = " & [CALLTYPEID])
[$ INV AMOUNT$] = DLookup("CallTypePrice", "tblCallTypes", "[CallTypeID] = " & [CALLTYPEID])
[PAY TYPE] = DLookup("CallPayType", "tblCallTypes", "[CallTypeID] = " & [CALLTYPEID])
   Dim Price As Currency
    
    If Nz(Me![End Miles], 0) = 0 Then
        Cancel = True
   
    End If
    
    If Cancel <> True Then
        Price = DLookup("Rate", "tblCallTypePrices", "[CallTypeID] = " & Me!CALLTYPEID & " And #" & _
            TimeValue(Me![Start Time]) & "# Between [StartTime] And [EndTime]")
        Select Case Me!CallTypeExt
            Case "H/R"
                Me![DRIVERS PAY] = 24 * (Me![End Time] - Me![Start Time]) * Price
            Case "M/P"
                Me![DRIVERS PAY] = (Me![End Miles] - Me![Start Miles]) * Price
           Case "DT-H/R"
                Me![DRIVERS PAY] = 24 * (Me![End Time] - Me![Start Time]) * Price + 13
            Case "NT-H/R"
                Me![DRIVERS PAY] = 24 * (Me![End Time] - Me![Start Time]) * Price + 18
             Case "Cancel"
                Me![DRIVERS PAY] = 0
            Case Else
                Me![DRIVERS PAY] = Price
            
        End Select
    End If
    
 DoCmd.RunCommand acCmdRefresh

Open in new window

0
BadotzCommented:
That value for strFormula does not eval, but this will
strFormula = "24 * (#4/2/2011 10:01:44 AM# - #4/2/2011 9:07:44 AM#) * 17"

Open in new window

Note the dates are wrapped in octothorpes (#).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peter57rCommented:
strFormula = "24 * (4/2/2011 10:01:44 AM - 4/2/2011 9:07:44 AM) * 17"

This formula does not conform to Access requirements.
It might work in some situations but in general it is wrong.

You must include datatime values inside # ..# delimiters.

So you need to modify your code so that you create an expression which is..

strFormula = "24 * (#4/2/2011 10:01:44 AM# - #4/2/2011 9:07:44 AM#) * 17"
You will then have a correctly expressed formula which will work.
0
BadotzCommented:
Nice work, peter57r!
0
peter57rCommented:
Just a bit behind the pace again(:-)
0
Blueice13085Author Commented:
alright so how can i get the formula to have the #..... formula reads
24 * ({EndTime} - {StartTime}) * {Price}
so would it be

24 * (#{EndTime}# - #{StartTime}#) * {Price}


0
Blueice13085Author Commented:
I selected the both of you sense the time of post was porb within 30 secs or so, and both had the correct answer i thank you all for the help and hope all understands the points i gave to each other:)
0
BadotzCommented:
No worries - glad to help.

And yes, I believe 24 * (#{EndTime}# - #{StartTime}#) * {Price} should work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.