Dates & Strings with SQL

[CPERKINS84]

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

[DrewDog_21]

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:

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.