I have a report and using SQL to calculate some values:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As 
Integer)

   
    Dim strLease As String
     Dim tlFinance As Long  ' total finance deliveries
    Dim tlLease As Long     'total lease deliveries
    Dim tlCash As Long       ' total cash deliveries
    
    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    

 
  strLease = "SELECT Count(tblDelivery.DeliveryID) AS CountOfDeliveryID " 
& _
    "FROM tblDelivery " & _
    "WHERE tblDelivery.FinanceCategory='" & Lease & "' AND 
tblDelivery.MonthAndYear='" & [Forms]![frmRptF_I]![Text17] & "'"
    rst.Open strLease, cnn, adOpenKeyset

    MsgBox rst!CountOfDeliveryID
    

 
      
End Sub

The expression  [Forms]![frmRptF_I]![Text17]  holds a date.  I am getting 
a Data Type mismatch in criteria expression error .  I also tried:

    strLease = "SELECT Count(tblDelivery.DeliveryID) AS 
CountOfDeliveryID " & _
    "FROM tblDelivery " & _
    "WHERE (((tblDelivery.FinanceCategory)='Lease') AND 
((tblDelivery.MonthAndYear)='[Forms]![frmRptF_I]![Text17]'));"

and getting the same error.  

Where am I wrong ?

Idelle,

Change this:

"' AND tblDelivery.MonthAndYear='" & [Forms]![frmRptF_I]![Text17] & "'"

To this:

"' AND tblDelivery.MonthAndYear=#" & [Forms]![frmRptF_I]![Text17] & "#"


The delimiter for dates is #, not '.

Greg

Thanks for your quick response Gregory.
I tried the expression and getting this error ?Syntax error in date in 
query expression tblDelivery.FinanceCategory=?1.467.90002441406? AND 
tblDelivery.MonthAndYear=##?.

I even added a second double quote at the end of the expression but still 
getting the same error.


Am I missing a quote somewhere

<< I tried the expression and getting this error ?Syntax error in date in 
query expression tblDelivery.FinanceCategory=?1.467.90002441406? AND 
tblDelivery.MonthAndYear=##?. >>

It sounds like you're doing this:

"' AND tblDelivery.MonthAndYear=#" & [Forms]![frmRptF_I]![Text17] & "#'"

Take out that last single quote.

"' AND tblDelivery.MonthAndYear=#" & [Forms]![frmRptF_I]![Text17] & "#"


Greg

I want to use ADO open a query using the procedure:

    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset  
    rstCr.Open "Query5", cnn, adOpenKeyset
  

I am getting this error and unable to find the cause:   
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' 
or 'UPDATE'.

 Thanks for any help