Date format with Quickpart Database in Word

Question

I try to change the date format in a quickpart database function.

The format is in American (mm/d/yyyy) but i want to change in the French format (dd.MM.yyyy).

This is my code :

DATABASE \d "C:\Users\taagede1\Dropbox\Samaritains\Soldes et indemnités\2018\Total soldes.xlsx" \c "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\taagede1\Dropbox\Samaritains\Soldes et indemnités\2018\Total soldes.xlsx;Mode=Read;Extended Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:System database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\"\";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" \s "SELECT Quoi, Date , Heure Début, Heure Fin, Total FROM Engagements$ WHERE ((NomPrenom = 'AubortLoic') AND (Payé IS NULL )) ORDER BY Date" \l "26" \b "191" \h

This is the result:

I have tried to add this:

{ DATABASE [\@ "dd.MM.yyyy"] \* MERGEFORMAT }

But i have a very ugly result (all buggy)

Answer 1

The OLEDB driver for Excel (and Access - it's the same one) supports a limited number of functions that can be used on the data via the Select query, among them Format. It's similar, but not identical to the VBA function of the same name.

In my test the following Select phrase worked (extracted from the Database field code for better visibility):

 \s "SELECT Quoi, Format([Date], 'dd.MM.yyyy') AS FrDate, Heure
  Début, Heure Fin, Total FROM Engagements$ WHERE ((NomPrenom = 'AubortLoic') AND (Payé IS NULL )) ORDER BY Date

Note that the date format is in single, not double quotes. You can use anything for the alias (the column header), except another field name. So it can't be Date if that's the field name in the data source. It could be Le Date, but in this case, due to the spaces, it would have to be in square brackets: [Le Date].