Sql in vba

[Dengkee]

hello ppl,



i have table "RATE" that contains fields "ENO, GRADEC, RATEC" now i would like to write a SQL statement that returns RATEC after i supplied the data on ENO and GRADEC. Can an1 help me please?

[TheShabz]

Where is ENO and GRADEC being pulled from? I'll assume from a form and answer as if thats true. Let me know otherwise. Lets assume they are textboxes (they can be whatever) called txtENO and txtGRADEC respectively.

dim strSQL As String

strSQL = "SELECT RATEC FROM RATE WHERE ENO = " & me.txtENO & " AND GRADEC = " & Me.txtGRADEC & ";"

docmd.RunSql (strSQL)

[Dengkee]

ENO and GRADEC is a return value from a function. i tried to call the function from the sql statement you gave me but im sure if this is how to do it. Heres my code below.

Public Function LOADRATE() As String
Dim strSQL As String
strSQL = "SELECT RATEC FROM RATE WHERE ENO = " & LOADENO & " AND GRADEC = " & LOADGRADE & ";"
DoCmd.RunSQL (strSQL)
End Function

Public Function LOADENO() As String
Dim ENOC As String
ENOC = DLookup("ENO", "QUOTE")
End Function

Public Function LOADGRADE() As String
Dim IMIX As Variant
IMIX = CMTC + SCSCDC + SCDC + DSCTC + WSCTC + MSC + PFAC + CIC + WPC + FBC + SCCC + ICEC + PUMPC
GR = GRADE
Select Case GR
Case "PMPMOB"
Forms!REQUISITION!GRADEC = "PUMP MOBILIZATION"
Case "TCHARGE"
Forms!REQUISITION!GRADEC = "TRANSPORT CHARGE"
Case "GROUT"
Forms!REQUISITION!GRADEC = GRADE + IMIX
Case "MORTAR"
Forms!REQUISITION!GRADEC = GRADE + IMIX
Case Else
Forms!REQUISITION!GRADEC = "B" + GRADE + "-G" + IMIX
End Select

Forms!REQUISITION!RATE.Requery
End Function

[TheShabz]

make them public variables. then whatever they are set to in other functions will be able to be passed into that one.

Public ENO As whateverdatatype
Public GRADEC As whateverdatatype

public function whatever()
blah blah blah
assigns values to ENO and GRADEC
end function

Public Function LOADRATE() As String
Dim strSQL As String
strSQL = "SELECT RATEC FROM RATE WHERE ENO = " & LOADENO & " AND GRADEC = " & LOADGRADE & ";"
DoCmd.RunSQL (strSQL)
End Function