2

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:

enter image description here

I have tried to add this: 

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

But i have a very ugly result (all buggy)

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
DenisG
  • 21
  • 1
  • Try removing the square brackets around the date format (although I don't think it will help). Test formatting the data the way you want to receive it within the data source (an Excel file) and see if that makes any difference. Also, is the document text formatted as French? And how about the default date in the Windows environment: American or French? – Cindy Meister Dec 09 '18 at 18:23
  • @CindyMeister Thanks for your answer! unfortunaly this is not working. The data source (excel) is correctly formated... and the default date in windows too ( in french) – DenisG Dec 09 '18 at 18:39

1 Answers1

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].

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43