Hi

i m trying to extract data from two different tables in same .mdb file where two tables are: StudentProfile and Accounts
mdldb is name of module where i've connected my vb with database
srchAccounts is the form where user enters student ID to search account info of that student.
my code is as follows:

Private Sub Form_Load()
Dim a As Integer

a = Val(SrchAccounts.txtEnterID.Text)


txtID.Text = a
cmbInstall.AddItem (One)
cmbInstall.AddItem (Two)
cmbInstall.AddItem (Three)
cmbInstall.AddItem (Four)
cmbInstall.Text = Two


Dim sql As String
Dim rs As New ADODB.Recordset
'On Error GoTo err1
mdldb.dbconnect
Set rs = mdldb.conn.Execute("select sp.FirstName, sp.Course, sp.Semester, ac.TotalFee, ac.Installments, ac.AmountsPaid, ac.DateOfPay, ac.DuesRemaining, ac.DueDate from Accounts ac, StudentProfile sp where sp.StudentID = ac.StudentID and sp.StudentID= " & a & "") 'error is here is sql query, it says type mismatch in criteria expression

AccountsInfo.Show
txtStdName.Text = rs!FirstName
txtCourseName.Text = rs!Course
txtCohort.Text = rs!Semester
txtFee.Text = rs!TotalFee
cmbInstall.Text = rs!Installments
txtPaid.Text = rs!AmountsPaid
txtDateOfPay = rs!DateOfPay
txtDuesRem = rs!DuesRemaining
txtDueDate = rs!DueDate

End Sub

help me plz
Ana

What are the datatypes of those fields (especially StudentID)?
Or try to use JOIN clause in SELECT.

Peko

hi peko!

Data types of StudentID is Numeric,
i tried using INNER JOIN but now it says missing operator in the sql query. would u be able identify whats missing in this statement?

"SELECT StudentProfile.StudentID, StudentProfile.FirstName, StudentProfile.LastName, StudentProfile.Course, StudentProfile.Semester, Accounts.TotalFee, Accounts.Installments, Accounts.AmountsPaid, Accounts.DateOfPay, Accounts.DuesRemaining, Accounts.DueDate FROM StudentProfile where StudentProfile.StudentID=" & a & " INNER JOIN Accounts ON StudentProfile.StudentID=Accounts.StudentID;"

thanx Ana

Type WHERE clause after JOIN

e.g.
SELECT <fields> FROM <tables> INNER JOIN <joins> WHERE <condition>

Peko!! i tried putting INNER JOIN before WHERE but it says 'syntax error in JOIN operation' is this format correct?

"SELECT StudentProfile.StudentID, StudentProfile.FirstName, StudentProfile.LastName, StudentProfile.Course, StudentProfile.Semester, Accounts.TotalFee, Accounts.Installments, Accounts.AmountsPaid, Accounts.DateOfPay, Accounts.DuesRemaining, Accounts.DueDate FROM StudentProfile, Accounts INNER JOIN Accounts ON StudentProfile.StudentID=Accounts.studentID where StudentProfile.StudentID=" & a & ""

Ana

Kick item accentuated with bold and try again, or send me please, fields description of these tables (I try query syntax home)

Peko

Hi Peko!!
Thankx ! it worked after kicking the bold item.
I really appreciate for ur continual help.
Ana