Help with my SQL statement!!!!!!!!

[THEAF]

Im using vb and access to for my project. Im trying to use this code in order to show all the payments made to a specific staff.

[CODE=vb]Private Sub Command1_Click( )

Dim strSQL As String

strSQL = "SELECT * FROM Staff "
strSQL = strSQL & "WHERE Firstname = " & txtName.Text

Set dbmyDB = OpenDatabase("E :\skol\A2 Computing\Speed y Pizza database.mdb")
Set rsStaff = dbmyDB.OpenReco rdset(strSQL, dbOpenDynaset)

If Not rsStaff.EOF Then rsStaff.MoveFir st
lstRecord.Clear

Do Until rsStaff.EOF
lstRecord.AddIt em rsStaff!Firstna me & " " & rsStaff!Lastnam e & vbTab & rsStaff!StaffID
lstRecord.ItemD ata(lstRecord.N ewIndex) = rsStaff!StaffID
rsStaff.MoveNex t
Loop
End Sub[/CODE]

I keep getting an error on the line " Set rsStaff = dbmyDB.OpenReco rdset(strSQL, dbOpenDynaset)" . It says 'Too few parameters. Expected one'
Im not very good with SQL statements so it's hard for me to solve. I would appreciate it if anyone has any good answers.
THANK U

[QVeen72]

Hi,

I guess, your First Name Field in Database must be text.. Wrap the name with Single Quotes:

[code=oracle]
strSQL = "SELECT * FROM Staff "
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"
[/code]

Regards
Veena

[THEAF]

Hi,

I guess, your First Name Field in Database must be text.. Wrap the name with Single Quotes:

[code=oracle]
strSQL = "SELECT * FROM Staff "
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"
[/code]

Veena

sorry i forgot to mention that i wanted to put txtSname.text after txtname.text so that it will show 1 person with that name and not anyone with the same first name. sorry for the trouble but how do i put this in. your improvement worked. but when i tried to fix it so that Sname comes up the list box just clears. Could you please help me.
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"

[QVeen72]

Hi,

Is it that you want to query with both Firstname and secondname...?
Try this :

[code=oracle]
strSQL = "SELECT * FROM Staff "
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"
strSQL = strSQL & " And LastName = '" & txtSName.Text & "'"

[/code]

Regards
Veena

[THEAF]

Hi,


[code=oracle]
strSQL = "SELECT * FROM Staff "
strSQL = strSQL & " WHERE Firstname = '" & txtName.Text & "'"
strSQL = strSQL & " And LastName = '" & txtSName.Text & "'"

[/code]

Regards
Veena

Again sorry about this but I'm getting the same error again on the line

 Set rsPayment = dbmyDB.OpenRecordset(strSQL, dbOpenDynaset)

that its too few parameters

[QVeen72]

Again sorry about this but I'm getting the same error again on the line

 Set rsPayment = dbmyDB.OpenRecordset(strSQL, dbOpenDynaset)

that its too few parameters

Hi,

Check the FieldNames in Database..

Regards
Veena