Adding to a date switches from italian to english format

[marcotognon]

Hi!

I have this code in an Access 2000 database working on WXp and W7 machines:

Function CalcoloPresenze()
    Dim rst As Recordset
    Dim sqlQuery, parametro As String
    Dim Giorno As Integer
    Dim TotaleGiorni As Integer
    Dim Presenze As Integer
    Dim InizioPeriodo As Date
    Dim FinePeriodo As Date
    Dim Data As Date

    InizioPeriodo = Format(Forms!frmProgressoCalcoloPresenze!Calendario_DataInizio, "mm/dd/yyyy")
    FinePeriodo = Format(Forms!frmProgressoCalcoloPresenze!Calendario_DataFine, "mm/dd/yyyy")
    
    If InizioPeriodo <= FinePeriodo Then
        TotaleGiorni = FinePeriodo - InizioPeriodo + 1
        SysCmd acSysCmdInitMeter, "Calcolo Presenze: ", TotaleGiorni
    Else
        MsgBox "Il periodo selezionato non è valido.", vbCritical + vbOKOnly
        Exit Function
    End If
    
    Data = Format(InizioPeriodo, "mm/dd/yyyy")
    Presenze = 0
    
    For Giorno = 0 To TotaleGiorni - 1
        Data = Format(DateAdd("d", Giorno, Format(InizioPeriodo, "mm/dd/yyyy")), "mm/dd/yyyy")
        
        sqlQuery = "SELECT DISTINCT Count(Clienti.Cittadinanza) AS Presenti " + _
            "FROM Clienti INNER JOIN Soggiorni ON Clienti.IdCliente = Soggiorni.CodCliente " + _
            "WHERE (((Soggiorni.Arrivo)<#" & Format(Data, "mm/dd/yyyy") & "#) AND ((Soggiorni.Partenza)>#" & Format(Data, "mm/dd/yyyy") & "#) AND ((Soggiorni.Presente)=True)) " + _
                "OR (((Soggiorni.Arrivo)<#" & Format(Data, "mm/dd/yyyy") & "#) AND ((Soggiorni.Partenza) Is Null) AND ((Soggiorni.Presente)=True)) " + _
                "OR (((Soggiorni.Arrivo)=#" & Format(Data, "mm/dd/yyyy") & "#) AND ((Soggiorni.Partenza)>#" & Format(Data, "mm/dd/yyyy") & "#) AND ((Soggiorni.Presente)=True)) " + _
                "OR (((Soggiorni.Arrivo)=#" & Format(Data, "mm/dd/yyyy") & "#) AND ((Soggiorni.Partenza) Is Null) AND ((Soggiorni.Presente)=True));"
                        
        Set rst = CurrentDb.OpenRecordset(sqlQuery, dbOpenSnapshot)
        
        Presenze = Presenze + rst.Fields("Presenti").Value
        SysCmd acSysCmdUpdateMeter, Giorno
        
        rst.Close
        Set rst = Nothing
        
    Next Giorno
    MsgBox "Il totale delle presenze del periodo è " & Presenze, vbInformation, "Totale presenze del periodo"
    
    SysCmd acSysCmdRemoveMeter
    DoCmd.Close acForm, "frmProgressoCalcoloPresenze"
    
End Function

The code is launched from a Form (frmProgressoCalcoloPresenze) containing two Calendar controls (Calendario_DataInizio and Calendario_DataFine).

The problem is that when cycling through the dates all goes well while the date's month is <= 12, then it switches to the italian format (dd/mm/yyyy), even if I forced the use of the english format (mm/dd/yyyy) in every step.

E.g.:
Cycle from 07/01/2010 to 07/31/2010

The dateadd() function generates the following values:
07/01/2010
07/02/2010
07/03/2010
07/04..10/2010
07/11/2010
07/12/2010
13/07/2010 !!!!!!!!!!!!
14/07/2010... and so on...

Which, obviously, returns wrong data.



Any help?

Thank you very much in advance!!!

Marco

[Rhino373]

I'm not sure I understand. Given the English format (07/13/2010) and the Italian format (13/07/2010). They are functionally the equivalent. This should be no different than me running a custom format "yyyymmdd" or 20100713. It's really just the same date.

What happens if you drop all these Format statements? I'd guess it's always in Italian format with a custom view in the English format. We'll when the day gets higher than the English month format can handle it has to swap it because there is no month higher than 12.

[Rhino373]

The fact that it understands this statement:
TotaleGiorni = FinePeriodo - InizioPeriodo + 1

means that the dates are working fine. I'm gussing you selected the 1st day and last days of the month for a trial and it looped the correrct number of times. So the end of the month date was in the correct format in relation to the earlier date for the math to come out right.

[marcotognon]

Hi Rhino!
Thanks for considering my problem!

About your first post, I probably didn't specify that it's not the format that's changing, it's the date itself! This comes out looking at the "Immediate window" (I translate the italian name of the window showing the variable's values while debugging in VBA): the values are always shown in american format, and there you see the dates changing from #12/07/2010# to #07/13/2010#...
There's something not working when adding 1 day to the date #12/07/2010#...

The other sympthom is that if I query day by day, e.g. only the 12, only the 13th and so on, the sum returned by the routine is correct, but not when giving a period of time.
Example:
12/7 sum = 84; 13/7 sum = 26; Total = 110.
Total sum from 12/7 to 13/7 <> 110....

[marcotognon]

Update: PROBLEM SOLVED!!!

It seems that ONLY the dates used into the SQL string are to be transformed to American format, and that ANY other date must be handled as is.

I removed all format() functions but those in the SQL string and magically it started working.

Thanks again Rhino for mumbling with me over this issue!

Marco.