I have Access db with more than one table.
one table is employ with fields Employid, Name,Month, Salary, TA. I would like to know the coding to search the database based on the Employid and find the Sum of the records in the Salary column and also of the TA column(for the particular employ) and displayed as caption of two labels.
Please help.
Use
Select Sum(Salary), Sum(TA) where Employid=1
Hope I understand you correctly.
Yes you understood the point.But I needed most was the second part how to display the result in a label or text box. How to refer to the Sum(salary) and Sum(TA)?
Also What about the name of the table in the select statement? I am having more than one table in the DB.
The field names would be SumofSalary and SumofTA or you could use the Recordset methods .Fields(0) for The Salary sum and .Fields(1) for the TA sum.
Yes, I for got the name of the table..
Select Sum(Salary), Sum(TA) from YourTable where Employid=1
I have tried this but it does not work. I give here the code I wrot. Please help me to find out the error.
Private Sub Command1_Click()
On Error GoTo eh:
Dim findStr As String, RsSearch As ADODB.Recordset
findStr = select Sum(Salary), Sum(TA)From Employ where Employid = & _
' & Text3.Text & '
Set RsSearch = New ADODB.Recordset
RsSearch.Open findStr, Cn, adOpenDynamic
If RsSearch.EOF And RsSearch.BOF Then
MsgBox Search Could not find any matching data, vbInformation, Invalid Search Criteria
GoTo CloseRsSearch:
End If
Text1.Text = RsSearch.Fields(0)
Text2.Text = RsSearch.Fields(1)
CloseRsSearch:
RsSearch.Close: Set RsSearch = Nothing
Exit Sub
eh:
MsgBox Err.Source & reports & Err.Description, , Error & Err.Number
End Sub
Private Sub Form_Load()
On Error GoTo eh:
Conn = Provider=Microsoft.JET.OLEDB.4.0;Data Source= & App.Path & employproj.mdb
Set Cn = New ADODB.Connection
With Cn
.ConnectionString = Conn
.CursorLocation = adUseClient
.Open
End With
Set Rs = New ADODB.Recordset
Rs.Open Employ, Cn, adOpenDynamic, adLockOptimistic, adCmdTable
Exit Sub
eh:
MsgBox Err.Source & reports & Err.Description, , Error & Err.Number
End Sub
Do a Debug.Print findStr the error will be glaring at you
Can you please explain me the use of Debug.Print?
or would you suggest some reference? I am not familiar with it and dont know how and where to place it in the project.
When I run the project in the IDE no error is reported but the text box dont show the sum.please help.
place Debug.Print Findstr after
FindStr = select Sum(Salary), Sum(TA)From Employ where Employid = & _
' & Text3.Text & '
In the IDE you will see what the value of this is in the immediate window. Post that information.
Place a breakpoint on the next line after the Debug.Print statement.
Your msgbox will never show up because
If RsSearch.EOF And RsSearch.BOF Then
Can never be true. It can't be at the beginning of the file and the end of the file at the same time. Use one or the other or
If RsSearch.EOF or RsSearch.BOF Then
Thanks a lot for the reply.It is realy helpful for beginners like me. I could learn the use of debug print.Now the code is working.Thanks.