Dates & Strings with SQL

I am trying to create a search criteria to search on a string and a date. I keep getting a miss match error. Can anyone guide me in the right direction?

Thanks


Dim Criteria As String
Dim SSN As String
Dim ActDate As Variant
M_Act.Refresh
J_Act.Refresh
J_Act.Recordset.MoveFirst
Do Until J_Act.Recordset.EOF
SSN = J_Act.Recordset.Fields("SOCIALSECURITYNUMBER")
If J_Act.Recordset.Fields("ADate") Is Null Then
Else
ActDate = J_Act.Recordset.Fields("ADATE").Value
ActDate = DateValue(ActDate)
Criteria = "[SOCIALSECURITYNUMBER]= '" & SSN & "' and
[ADATE]= '" & ActDate & "' "
M_Act.Recordset.Find Criteria

You have to enclose the date value with pound signs like
#01/01/2000# and not quotes. Plus you might want to get
rid of that space after ActDate.

Also - are you using DAO or ADO? It looks like you are
using ADO. With ADO you can't search on multiple
criteria. Instead you use the FILTER method to filter out
records.

so it would look like this:

Code:

---

Dim Criteria As String
Dim SSN As String
Dim ActDate As Variant

M_Act.Refresh
J_Act.Refresh
J_Act.Recordset.MoveFirst

Do Until J_Act.Recordset.EOF
  SSN = J_Act.Recordset.Fields("SOCIALSECURITYNUMBER")
  If J_Act.Recordset.Fields("ADate") Is Null Then
 
  Else
      ActDate = J_Act.Recordset.Fields("ADATE").Value
      ActDate = DateValue(ActDate)
      Criteria = "[SOCIALSECURITYNUMBER]= '" & SSN & "' and[ADATE]= #" & ActDate & "#"
      M_Act.Filter = Criteria
  End If

---

then you need to test the M_Act.RecordCount property. If
it is 0 then the record was not found. If it is 1 or more
then the record exists, and you can execute M_Act.MoveFirst
and then whatever operation you need to do on the recordset.