How to retrieve a particular value from table and use it for calculation

[shandoo27]

hi,
I wish to retrieve a particular value from the table and use it for comparison and calculation purposes .
In the VBA code i tried using Dlookup function , but it returns a variant and i can't compare with an integer. Is there any possible way where i can extract tht value as an integer so that i can do comparison and calculation.

Thanks in advance

 

[boblarson]

Just use a Dlookup and if necessary wrap it in the CInt function to ensure it is of the integer type.

 

[shandoo27]

I tried the above Cint function as u said but still no change. I am facing the same problem . I have posted below the SUb for the button, Could you please check and tell whether i am making some grave blunder or syntax mistake ?

thanks in advance for the help .

 

[shandoo27]

Private Sub compare_Click()
Dim TOBEEFTMIN As Integer
Dim TOBEEFTMAX As Integer
Dim ACT_EF_T As Integer
Dim EF_REMARK As String
Dim EF_DIFF As Integer
TOBEEFTMAX = CInt(DLookup("EF_T_MAX", "ADMIN_ACCESS", "FORM_CHOICE='TO_BE' AND PRJT_NAME=FORMS!ACTUALS!PRJT And TLA=FORMS!ACTUALS!SUB_PRJT And CAT=FORMS!ACTUALS!CATEGORY AND TASK=FORMS!ACTUALS!TSK"))
TOBEEFTMIN = CInt(DLookup("EF_T_MIN", "ADMIN_ACCESS", "FORM_CHOICE='TO_BE' AND PRJT_NAME=FORMS!ACTUALS!PRJT And TLA=FORMS!ACTUALS!SUB_PRJT And CAT=FORMS!ACTUALS!CATEGORY AND TASK=FORMS!ACTUALS!TSK"))
ACT_EF_T = CInt(DLookup("SUM(EF_T)", "ACTUALS", "PRJT_NAME=FORMS!ACTUALS!PRJT And TLA=FORMS!ACTUALS!SUB_PRJT And CAT=FORMS!ACTUALS!CATEGORY AND TASK=FORMS!ACTUALS!TSK"))

If ACT_EF_T > TOBEEFTMIN And ACT_EF_T < TOBEEFTMAX Then
EF_REMARK = "OK"
EF_DIFF = 0
End If
If ACT_EF_T > TOBEEFTMAX Then
EF_REMARK = "BAD"
EF_DIFF = (ACT_EF_T - TOBEEFTMAX)
End If
If ACT_EF_T < TOBEEFTMIN Then
EF_REMARK = "EXCELLENT"
EF_DIFF = (TOBEEFTMIN - ACT_EF_T)
End If
Dim SQLC As String
SQLC = " INSERT INTO METRIC VALUES(FORMS!ACTUALS!PRJT,FORMS!ACTUALS!SUB_PRJT,FORMS!ACTUALS!CATEGORY,FORMS!ACTUALS!TSK,EF_DIFF,EF_REMARKS);"
DoCmd.RunSQL SQLC
End Sub