DCount returns 0 and it's wrong

[Ipem]

Hello gusy,
Can somebody point out the mistake in my function? It is basically a DCount, and it should find records, but returns 0 all the time. My code is:

Function cntkit(sftd As Date, sftn As String, typid As Integer, specpaint As Boolean) As Integer 'Counts jobs kitted during shift given by sftd and sftn
Dim timeformat As String
timeformat = "\#mm\/dd\/yyyy hh\:nn\:ss\#" 'need this, to convert it to US datetime format
cntkit = DCount("[JOB]", "Archive", "[Type] =" & typid & " And [Autfinish]=False And [SpecPaint] =" & specpaint & " And ([Kit] BETWEEN " & Format(sftstart(sftd, sftn), timeformat) & " AND " & Format(sftstart(sftd, sftn), timeformat) & ")")
End Function

sftstart and sftend are functions which are returning dates. The funcion works fine if I omit the Between part of the criteria. So the problem is in that part.
WHat I'm doing wrong?

 

[pr2-eugin]

Could you please provide us the code for sftstart and sftend functions? Also some sample data?

 

[Ipem]

Thanks for the quick reply!
Those functions are very simple too. sftd is a combo box value on a form, and can have 3 values: shift names in hungarian (morning afternoon and night). sftd is a Date value from a textbox control (set to Short Date format) on the same form.

Function sftstart(sftd As Date, sftn As String) As Date
    If sftn = "Délelőtt" Then sftstart = sftd + (6 / 24)
    If sftn = "Délután" Then sftstart = sftd + (14 / 24)
    If sftn = "Éjszaka" Then sftstart = sftd + (22 / 24)
        
End Function
Function sftend(sftd As Date, sftn As String) As Date
    If sftn = "Délelőtt" Then sftend = sftd + (14 / 24)
    If sftn = "Délután" Then sftend = sftd + (22 / 24)
    If sftn = "Éjszaka" Then sftend = sftd + 1 + (6 / 24)
        
End Function

Sample data:
cdbl(sftd) = 41520.22
sftn = "Délelőtt"
typid = 2
specpaint = True

 

[DavidAtWork]

unless I'm missing something, your expression:

([Kit] BETWEEN " & Format(sftstart(sftd, sftn), timeformat) & " AND " & Format(sftstart(sftd, sftn), timeformat) & ")")

on the asumption that sftstart is returning the same date in both instances, there will only be records returned if they exist for that exact date. Have you tried running this for a Kit date that exists?

Something else to consider is your date syntax, you might want to try

BETWEEN #" & Format(sftstart(sftd, sftn), timeformat) & "# AND ..

David

 

[pr2-eugin]

Hmmm, the function seems to be okay, I would go about debugging.. Check if the information you get is okay..

Function cntkit(sftd As Date, sftn As String, typid As Integer, specpaint As Boolean) As Integer 
[COLOR=Green]'
'Counts jobs kitted during shift given by sftd and sftn
'[/COLOR]
    Dim timeFormatStr As String[COLOR=Blue], startDtTm As Date, endDtTm As Date[/COLOR]
    timeFormatStr = "\#mm\/dd\/yyyy hh\:nn\:ss\#"             [COLOR=Green]'need this, to convert it to US datetime format[/COLOR]
[COLOR=Blue]    startDtTm = Format(sftstart(sftd, sftn), timeFormatStr)
    endDtTm = Format(sftstart(sftd, sftn), timeFormatStr)
    [B]MsgBox "Start Date/Time : " & startDtTm & vbCrLf & "End Date/Time : " & endDtTm[/B][/COLOR]
    cntkit = DCount("*", "Archive", "(([Type] = " & typid & ") And ([Autfinish] = False) And ([SpecPaint] = " & specpaint & ") And ([Kit] BETWEEN " & startDtTm & " AND " & endDtTm & "))")
End Function

 

[Ipem]

Ah THX!

What a trivial mistake You are right, the two dates were the same. I've spent 2 hours, but could not notice this.
Than you all.