DCount - error 3464 data type mismatch in criteria expression

[dkmoreland]

Would someone be willing to point out my error in this DCount expression (highlighted in red)? I can't see the mismatch.

Private Sub TxtEndDate_AfterUpdate()
Dim dStart As Date
Dim dEnd As Date
Dim strSQL As String
Dim lNumJobs As Long


On Error GoTo ErrProc
Set db = CurrentDb()
Set rs = db.OpenRecordset("Production Raw Data")


dStart = Me.TxtStartDate
dEnd = Me.TxtEndDate
Debug.Print dStart
Debug.Print dEnd



strSQL = "SELECT [Production raw data].start_date, [Production raw data].end_date, [Production raw data].jobnum, [Production raw data].jobname" _
& " FROM [Production raw data]" _
& " WHERE ((([Production raw data].start_date) = dStart And [Production raw data].[enddate] = dEnd"

Debug.Print strSQL

[COLOR="Red"][B]lNumJobs = DCount("*", "Production raw data", "[start_date]='" & dStart & "' AND [end_date]='dEnd'")[/B][/COLOR]

Debug.Print lNumJobs

ExitProc:
    Set db = Nothing
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 462    'excel not open
            Set appExcel = New Excel.Application
            Resume Next
        Case 3125
            MsgBox "The selected workbook is not the correct format.", vbOKOnly
            Resume ExitProc
        Case 3201
            MsgBox "Job Prefix is not valid.  Please add the new prefix to the prefix list.  Import was cancelled.", vbOKOnly
            Resume ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
            Resume Next
    End Select
End Sub

 

[arnelgp]

Encliosed the table name in square bracket. Date criteria must be formatted in mm/dd/yyyy format and delimited by # and not by single quote.

lNumJobs = DCount("*", "[Production raw data]", "[start_date]=#" & format(dStart,"mm/dd/yyyy") & "# AND [end_date]=#" & format(dEnd,"mm/dd/yyyy") & "#"

 

[Ranman256]

usu it will forgive single quotes (') for dates. Try using #.

and your text is wrong,,youre asking if a date = string
[end_date]='dEnd'

and note you are asking for 1 start date and 1 end date...not a range.

lNumJobs = DCount("*", "Production raw data", "[start_date]=#" & dStart & "# AND [end_date]=#" & dEnd & "#")

 

[The_Doc_Man]

What range are you seeking to count? The expression as you wrote it will only give you answers of 0 or that subset of your data with very specific start and end dates.

Perhaps did you want something more like:

lNumJobs = DCount("*", "Production raw data", "[event_date] BETWEEN #" & dStart & "# AND #" & dEnd & "#")

(where I'm using event_date as the name of a field holding the date of a given sample.)

 

[dkmoreland]

What range are you seeking to count? The expression as you wrote it will only give you answers of 0 or that subset of your data with very specific start and end dates.

Perhaps did you want something more like:

lNumJobs = DCount("*", "Production raw data", "[event_date] BETWEEN #" & dStart & "# AND #" & dEnd & "#")

(where I'm using event_date as the name of a field holding the date of a given sample.)

I am counting records with very specific start and end dates. I did get this working - thanks for your response.

 

[The_Doc_Man]

OK. Good that you got it fixed up.