Managing Dates Query in ACCESS with SQL Server background-CodePudding

I am doing my project on ACCESS for the "front" part with the forms, and in SQL Server for the "back-end" part with the databases, everything works fine exect for one thing : I don't understand how to filter Access's forms with SQL Server's date type.

I have looked for everything on the web and all the solutions don't seems to work for my case (maybe because of the ACCESS/SQL Server env), things like :

Me.Form1.Filter = "[date born] = #" & Format("11/04/2022", "dd/mm/yyyy") & "#"
'with VBA directly

SELECT * FROM dbo_Person WHERE [date born] = Format('11/04/2022','dd/mm/yyyy') ;
-- with SQL Request from ACCESS on the table

but everytime I have the same issue: nothing is displayed even if there is rows who have this date in the right column. It is like if no row respected this condition even though it is not the case. I'm guessing it may be an issue with SQL Server Date type who doesn't convert well on ACCESS.

That is how is desplayed by default in my ACCESS the dates of the column (my ACCESS software is setuped in French btw)

The data is well recognize as "not a String" because when I try to filter with a String an error appears.

Can someone help me to know where I missed something or misstyped something please ?

Edit : the Date column seems actually to be recognized as Short String in ACCESS, it may be the issue but still don't know how to fix because it is indeed a date type on sql server

"Texte court" can be translated to "short text" or short String

CodePudding user response：

First, if you use data type DateTime2 in SQL Server, that will be read as text in Access.

So, change that to DateTime.

Next, use the universal format yyyy-mm-dd when filtering date values:

FilterDate = DateSerial(2022, 4, 11)
Me.Filter = "[date born] = #" & Format(FilterDate, "yyyy\/mm\/dd") & "#"

' Linked table in Access:

Sql = "SELECT * FROM dbo_Person WHERE [date born] = #" & Format(FilterDate, "yyyy\/mm\/dd") & "#;"