CODEWITHSUNDEEP
ms-accessvba
user2204315
user2204315

Reputation:

DateSerial function doesn't work in SQL query

This function correctly returns the date 30 Sep 15:

Public Function ToDate() As Date

Dim MyYr As Integer

Select Case DatePart("m", Date)
        Case 1
               MyYr = DatePart("yyyy", Date) - 1
        Case Else
               MyYr = DatePart("yyyy", Date)
End Select

ToDate = DateSerial(MyYr, cmbMth, 1) - 1

End Function

But it won’t work if I try to put it in here:

Me.RecordSource = "SELECT tblDisclosure.DBSInvoice, " & _
  "Sum(tblDisclosure.DBSFee) AS SumOfDBSFee, " & _
  "Sum(tblDisclosure.MyFee) AS SumOfMyFee, " & _
  "Count(tblDisclosure.ID) AS CountOfID, tblDisclosure.DBSInvoice " & _
  "From tblDisclosure " & _
  "GROUP BY tblDisclosure.DBSInvoice, tblDisclosure.DBSInvoice " & _
  "HAVING (((tblDisclosure.DBSInvoice) Is Not Null) AND " & _
      "((tblDisclosure.DBSInvoice) Between #10/31/2014# And ToDate)) "

I get the message Enter parameter value - ToDate.

It does work if I substitute ToDate with DateSerial(2015,9,30).

Upvotes: 1

Views: 955

Answers (2)

Gustav
Gustav

Reputation: 56026

Or replace Today with DateSerial:

tblDisclosure.DBSInvoice Between #10/31/2014# And DateSerial(Year(Date()), Month(Date()),0)

Upvotes: 2

Smandoli
Smandoli

Reputation: 7019

Try this:

"HAVING (tblDisclosure.DBSInvoice Is Not Null) AND
(tblDisclosure.DBSInvoice Between #10/31/2014# And ToDate()) "

The difference is a double parenthesis as part of the function call: ToDate().

Upvotes: 1

Related Questions