Querying on 2 fields

[grenee]

Good Day,

I am writhing a module which requires a line of 2 of code querying on 2 fields. I am fine with the query on 1 field but is having some challenges on the 2. I would be grateful for some assistance. Here is the working code:

Dim curDatabase As dao.Database
Dim rs As dao.Recordset
Dim rs1 As dao.Recordset

Set curDatabase = CurrentDb

Set rs = CurrentDb.OpenRecordset("Select * from [tblSignin] WHERE tblSignin.[OfficerName]='" & Form_frmSignin.txtUser & "'")



This is the non working code:

Set rs = CurrentDb.OpenRecordset("Select * from [tblSignin] WHERE tblSignin.[OfficerName]='" & Form_frmSignin.txtUser & "'" & tblSignin.[Auto Date and Time Entry]='" & Form_frmSignin.txtUser2 )

 

[Galaxiom]

Best to separate into two steps. Then you can see the string variable after it is concatenated.

strSQL = "Select * from [tblSignin] WHERE [OfficerName]='" & Form_frmSignin.txtUser & "' AND [Auto Date and Time Entry]=" & Format(Form_frmSignin.txtUser2, "\#mm\/dd\/yyyy\#")
  
Set rs = CurrentDb.OpenRecordset(strSQL)

 

[grenee]

Thanks Galaxiom.

That is good advise and information.

I know it is close to working but there is some little problem; I am getting a runtime syntax error notice.

Here is what I entered:

Private Sub Command201_Click()
Dim curDatabase As dao.Database
Dim rs As dao.Recordset
Dim rs1 As dao.Recordset
Dim strSQL As String

Set curDatabase = CurrentDb

strSQL = "Select * from [tblSignin] WHERE tblSignin.[OfficerName]='" & Form_frmSignin.txtUser & "' AND tblSignin.[Auto Date and Time Entry]='" & Format(Form_frmSignin.Text24, "\#mm\/dd\/yyyy\#")

Set rs = CurrentDb.OpenRecordset(strSQL)

If Not rs.RecordCount = 0 Then

rs.MoveLast
rs.MoveFirst
End If
MsgBox rs.RecordCount


I Believe that there may be problems with the quote which I am unable to figure out.

Can you kindly review for me?

 

[Galaxiom]

Try copying what I posted. You have an extra quote mark.