How to give where condition in the select query?

Question

How to give where condition in the select query?

ACCESS 2003

MY Query

SELECT RECORDNO, PERSONID, EMPNAME, TITLENAME, DEPARTMENT, NATION, 
CARDEVENTDATE, INTIME, OUTTIME, (select TOP 1 F1.CARDEVENTDATE from 
tmp_cardevent as F1 where F1.RECORDNO < F2.RECORDNO AND F1.PERSONID = 
F2.PERSONID order by F1.RECORDNO DESC, F1.PERSONID DESC) AS PrevDate, 
(select TOP 1 F1.OUTTIME from tmp_cardevent as F1 where F1.RECORDNO < 
F2.RECORDNO AND F1.PERSONID = F2.PERSONID order by F1.RECORDNO DESC, 
F1.PERSONID DESC) AS PrevOut FROM tmp_cardevent AS F2

I want to use:

WHERE CARDEVENTDATE BETWEEN '" & sdate & "' AND '" & edate & "'

From the above query how can I give the where condition, I tried this:

SELECT RECORDNO, PERSONID, EMPNAME, TITLENAME, DEPARTMENT, NATION, 
CARDEVENTDATE, INTIME, OUTTIME, (select TOP 1 F1.CARDEVENTDATE from 
tmp_cardevent as F1 where F1.RECORDNO < F2.RECORDNO AND F1.PERSONID = 
F2.PERSONID order by F1.RECORDNO DESC, F1.PERSONID DESC) AS PrevDate, 
(select TOP 1 F1.OUTTIME from tmp_cardevent as F1 where F1.RECORDNO < 
F2.RECORDNO AND F1.PERSONID = F2.PERSONID order by F1.RECORDNO DESC, 
F1.PERSONID DESC) AS PrevOut FROM tmp_cardevent AS F2
WHERE F2.CARDEVENTDATE BETWEEN '" & sdate & "' AND '" & edate & "'

But it showing “DATA TYPE MISMATCH ERROR”

And also I tried:

 SELECT RECORDNO, PERSONID, EMPNAME, TITLENAME, DEPARTMENT, NATION, 
CARDEVENTDATE, INTIME, OUTTIME, (select TOP 1 F1.CARDEVENTDATE from 
tmp_cardevent as F1 where F1.RECORDNO < F2.RECORDNO AND F1.PERSONID = 
F2.PERSONID and F2.CARDEVENTDATE BETWEEN '" & sdate & "' AND '" & edate 
& "' order by F1.RECORDNO DESC, F1.PERSONID DESC) AS PrevDate, (select 
TOP 1 F1.OUTTIME from tmp_cardevent as F1 where F1.RECORDNO < 
F2.RECORDNO AND F1.PERSONID = F2.PERSONID order by F1.RECORDNO DESC, 
F1.PERSONID DESC) AS PrevOut FROM tmp_cardevent AS F2 WHERE 
F2.CARDEVENTDATE BETWEEN '" & sdate & "' AND '" & edate & "'

But it showing same “DATA TYPE MISMATCH ERROR”

Can any one help me please

Answer 1

Access uses # as the delimiter for date literals, not '. You need to replace accordingly.

Answer 2

Rather than squirting literal strings into your SQL text, consider using prepared statements. Not only does this give you SQL injection protection, you can defer the creation of parameter values to your data access library of choice e.g. the following ADO code uses the dedicated OLE DB provider for the Access database engine to create DATETIME values and the VBA function CDate() to cast strings representing dates to Date values using the Windows regional settings on the local machine (which may not be the same as the machine with the database file, of course):

Dim sDate As String
sDate = 4/1/09"  ' ambiguous DATETIME value

Dim eDate As String
eDate = "2009-12-31 23:59:59"  ' unambiguous DATETIME value

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = CurrentProject.Connection  ' Access UI object
  .CommandText = _
      "SELECT * " & _
      "  FROM Cards " & _
      " WHERE CARDEVENTDATE BETWEEN :start_date AND :end_date;"
  .Parameters.Append .CreateParameter( _
      ":start_date", adDate, adParamInput, , CDate(sDate))
  .Parameters.Append .CreateParameter( _
      ":end_date", adDate, adParamInput, , CDate(eDate))

  Dim rs As ADODB.Recordset
  Set rs = .Execute
End With

MsgBox rs.GetString

Even better, make it a stored proc e.g. do this once at design time:

CurrentProject.Connection.Execute _
  "CREATE PROCEDURE GetCards " & _ 
  "( " & _ 
  " :start_date DATETIME, " & _ 
  " :end_date DATETIME " & _ 
  ") " & _ 
  "AS " & _ 
  "SELECT * " & _ 
  "  FROM Cards " & _ 
  " WHERE CARDEVENTDATE BETWEEN :start_date AND :end_date;"

Then at run time each time use this:

.CommandText = _
  "EXECUTE GetCards :start_date, :end_date;"

This way if your query needs to change (but the required parameters do not -- you can always append optional parameters with default values, though), you can just change the proc in the one back end rather than having to change SQL code in all the front ends.