French date issue? Access said that #11/30/2016# is greater than #01/06/2018#

[Gabrieel]

HI,

I am really stock in Date calcul:I have to calculate time spent in storage by some cars by month ex: in June how many day spent a car on my yard?. I had a lot of parameter . for example Gate in if the car came within the month the timer start from the Gate in so I would say if dtGateIn > dtBeginPer then dtBeginPer = dtGateIn and it is here I am stock because I do not know why access think that #11/30/2016# is greater than #01/06/2018#
to make things properly I convert each date in US format (I am living in Belgium, the french part so my computer parameter is set in french)

Public Function DtUs(ByVal dDate)
    DtUs = format(CDate(dDate), "mm/dd/yyyy")
End Function

this is my main code

Public Function DelaiStkRBLMois(ByVal dDebPer As Date, ByVal dFinPer As Date, ByVal dSTK_TGC As Date, ByVal dDtIn As Date, ByVal dDtOut As Date, Optional ByVal sProprio As String) As Long
'D'abord on elimine les vehicules sortie TGC rentré une deuxieme fois
        Dim dDeb, dFin  As Date
        If sProprio = "BCD000377" Or sProprio = "BCD060377" Or sProprio = "BCD000679" Or sProprio = "BCD060679" Or sProprio = "BCD000517" Or sProprio = "BCD000589" Or sProprio = "BCD060589" Or sProprio = "BCD000585" Or sProprio = "BCD000590" Or sProprio = "BCD060585" Or sProprio = "BCD060590" Then
                DelaiStkRBLMois = 0
        Else
            'Borne Inférieur
            If DtUs(dDtIn) > DtUs(dDebPer) Then
            dDeb = DtUs(dDtIn)
            Else
            dDeb = DtUs(dDebPer)
            End If
            Debug.Print dDeb
            'Borne Supérierur
            If DtUs(dDtOut) < DtUs(dFinPer) Then
                If DtUs(dSTK_TGC) < DtUs(dDtOut) And DtUs(dSTK_TGC) > DtUs(dDeb) Then
                dFin = DtUs(dSTK_TGC)
                Else
                dFin = DtUs(dDtOut)
                End If
            Else
                If DtUs(dSTK_TGC) < DtUs(dFinPer) Then
                dFin = DtUs(dSTK_TGC)
                Else
                dFin = DtUs(dFinPer)
                End If
            End If
            Debug.Print dFin
            'Calcul
            'If DateDiff("d", dDeb, dFin) < 0 Then
            '     DelaiStkRBLMois = 0
            'Else
                DelaiStkRBLMois = DateDiff("d", DtUs(dDeb), DtUs(dFin))
                If DelaiStkRBLMois < 0 Then
                DelaiStkRBLMois = 0
                End If
            'End If
        End If
End Function

so for this

?DelaiStkRBLMois(#01/06/2018#, #30/06/2018#, #16/10/2017#, #30/11/2016#, #28/06/2018#,"651000517")

I got 575 days ne cause for it 30/11/2016 is higher than 01/06/2018

do you so what I could have done wrong?

Thanks in advance for your help I'm hammering the wall with my head now! :banghead::banghead::banghead:

 

[June7]

Dealing with international dates is known issue. See if this helps: http://allenbrowne.com/ser-36.html

 

[Galaxiom]

Public Function DtUs(ByVal dDate)
    DtUs = format(CDate(dDate), "mm/dd/yyyy")
End Function

If DtUs(dSTK_TGC) < DtUs(dDtOut) And DtUs(dSTK_TGC) > DtUs(dDeb) Then

I do not know why access think that #11/30/2016# is greater than #01/06/2018#

Because you are not comparing those date values in your function

Format() returns a string. Your code making a text comparison and "11/30/2016" is greater than "01/06/2018"

DelaiStkRBLMois = DateDiff("d", DtUs(dDeb), DtUs(dFin))

In this code the strings are being implicitly cast into dates by default using your Regional Settings (then anything else it thinks might work if that fail)s because they are presented to a function that expects dates.

Avoid implicit casting. Work with dates.