Changing date format in recordset

[davideitaly]

Hi. I am referring to this thread: https://www.accessforums.net/showthread.php?t=71371 that i marked as solved, even if there's still a hell of a problem that I'd like to treat separately.
I set a form from which I can populate a word dotx. A code is ruling everything on the basis of a sequence of dates (DATA_CONTRATTO), selecting the most recent ones in the recordset. Here's the significant part of the code:

Private Sub ESPORTA_OCCASIONALE_Click()Dim Wrd As Word.Application, Doc As Word.Document
Dim Rst As DAO.recordset
Dim Modello As String, NomeFile As String, i As Integer
Dim Record As String, SQL As String
Dim Tbl As String * 1
Dim ReplSel As Boolean


    Modello = CurrentDb.Name
    Modello = Left(Modello, Len(Modello) - Len(Dir(Modello))) & "modello_occasionale.dotx"


    On Error Resume Next
    Set Wrd = GetObject(, "Word.Application")
    If Err.Number = 429 Then
        Set Wrd = CreateObject("Word.Application")
    End If
    
    On Error GoTo 0
    
    Wrd.Visible = True
    Wrd.Activate
    ReplSel = Wrd.Options.ReplaceSelection
    Wrd.Options.ReplaceSelection = True
    Set Doc = Wrd.Documents.Add(Modello)
    Doc.Activate
    
    Doc.Bookmarks("Nome8").Select
    Wrd.Selection.TypeText Me.NOME

Etc...

SQL = "FROM Anagrafica_docenti INNER JOIN (Anagrafica_materie INNER JOIN (Anagrafica_corsi INNER JOIN Anagrafica_art_corsi ON Anagrafica_corsi.ID_Anagrafica_corsi = Anagrafica_art_corsi.CORSO) ON Anagrafica_materie.ID_anagmaterie = Anagrafica_art_corsi.MATERIA) ON Anagrafica_docenti.ID_Anagrafica_docenti = Anagrafica_art_corsi.ID_Anagrafica_docenti" & _
"ORDER BY Anagrafica_art_corsi.DATA_CONTRATTO DESC , Anagrafica_art_corsi.PROTOCOLLO DESC;"


Set recordset = CurrentDb.OpenRecordset("Select * from sqlbookmark WHERE ID_Anagrafica_docenti=" & Me.ID_Anagrafica_docenti & " AND DATA_CONTRATTO=#" & DMax("DATA_CONTRATTO", "sqlbookmark", "ID_Anagrafica_docenti=" & Me.ID_Anagrafica_docenti) & "#")
    
    Doc.Bookmarks("Data_contratto").Select
    Wrd.Selection.TypeText Me!DATA_CONTRATTO

Etc.

I noticed that it works perfectly since it's impossible to confuse US and EU formatted dates (I mean mm/dd/yyyy and dd/mm/yyyy).
For example if it deals with 27/03/2018 (27th March 2018), everything goes well. But when it deals with for example 02/03/2018 (2nd March 2018) it says "invalid use of Null".

So, the question is: how can I force the recordset to use the EU date format when it has to perform the recordset select?

I tried inserting this:

Format(DATA_CONTRATTO, "dd/mm/yyyy")

in the "select" sequence, but it didn't work.

Anyone can help me please?



Thanks in advance!

[pbaldy]

Not sure exactly where you're trying to format it. For opening the recordset, it has to be in US format:

http://allenbrowne.com/ser-36.html

For output, you should be able to use what you showed to format it in EU format.

[ssanfu]

A word of caution:

Dim Record As String, SQL As String

"SQL" is a reserved word (and a property) and shouldn't be used as an object name nor a variable name. It will cause you grief because troubleshooting will be difficult (mose times).

Better would be "sSQL" or "strSQL" or "SQLstring"........

[davideitaly]

Not sure exactly where you're trying to format it. For opening the recordset, it has to be in US format:

http://allenbrowne.com/ser-36.html

For output, you should be able to use what you showed to format it in EU format.

I have to format DATA_CONTRATTO in DMax parenthesis as it rules the ordering of records for populating the word file. Or maybe the following single instruction for inserting the date, again based on DATA_CONTRATTO...

I already knew the link you quoted. I took my attempts from there, but with no success...

[davideitaly]

A word of caution:

"SQL" is a reserved word (and a property) and shouldn't be used as an object name nor a variable name. It will cause you grief because troubleshooting will be difficult (mose times).

Better would be "sSQL" or "strSQL" or "SQLstring"........

Thanks for advising, I will soon change it!

[isladogs]

As already pointed out, use US date format

Format(DMax(............),"mm/dd/yyyy")

[davideitaly]

As already pointed out, use US date format

Format(DMax(............),"mm/dd/yyyy")

It works. Thank you a lot!