SELECT DISTINCT on passthrough does not work after changing columntype

[J.Kannengiesser]

Hi,
we have a very strange problem: using Access as frontend we are getting data from out MS-SQL-Server.
We noticed that an old table was storing a date-value within a varchar column; we now changed the field to datetime and now get this problem in Access:
in a form we attach the data by code using a passthrough query; the user has several dorpdown for filtering and the dropdwon is filled by a "SELECT distinct xxx" on this passthrough.
That is and was working, but in the old version the dropdown for the datefield was a substring() and now is a format() and the dropdown contains ALL date-values for every row !?

We could only manage this by changing the SQL from "SELECT DISTINCT JMT ..." to "SELECT ... GROUP BY JMT" -

So now my question:
Why is "SELECT DISTINCT" working for all other fields but not for the fields created by format()-function ? This is done by the SQL-server and should be unseen by Access !

---

OLD: (String containing '2024-01-24 10:30:15')

Function GET_SQL() As String
Dim sTmp As String


sTmp = ""
sTmp = sTmp & " With Daten AS ("
sTmp = sTmp & " SELECT EDV_MDEKopf.id "
sTmp = sTmp & " ,SUBSTRING(Datum,01,10) as JMT "
sTmp = sTmp & " ,SUBSTRING(Datum,12,02) as HH "
sTmp = sTmp & " FROM EDV_MDEKopf "
...
End Function

NEW: (datetime containing '2024-01-24 10:30:15.123')

Function GET_SQL() As String
Dim sTmp As String
sTmp = ""
sTmp = sTmp & " With Daten AS ("
sTmp = sTmp & " SELECT EDV_MDEKopf.id "
sTmp = sTmp & " ,format(Zeitstempel,'yyyy-MM-dd') as JMT "
sTmp = sTmp & " ,format(Zeitstempel,'HH') as HH "
sTmp = sTmp & " FROM EDV_MDEKopf "
...
End Function

OLD: ("SELECT distinct [JMT]" returns ALL rows whereas "SELECT distinct [Auftragsart]" is working !?)

Call Create_PassThrough(Me.Name & "_PT", GET_SQL())
Me.RecordSource = Me.Name & "_PT"

sQry = "SELECT distinct [Auftragsart] FROM " & Me.Name & "_PT ORDER BY [Auftragsart]"
Me.SEL_Buchungsart.RowSource = sQry
sQry = "SELECT distinct [JMT] FROM " & Me.Name & "_PT ORDER BY [JMT] DESC"
Me.SEL_Datum.RowSource = sQry
Me.SEL_Datum.Value = Me.SEL_Datum.ItemData(0)

NEW:

sQry = "SELECT [Auftragsart] FROM " & Me.Name & "_PT GROUP BY [Auftragsart] ORDER BY [Auftragsart]"
Me.SEL_Buchungsart.RowSource = sQry
sQry = "SELECT [JMT] FROM " & Me.Name & "_PT GROUP BY [JMT] ORDER BY [JMT] DESC"
Me.SEL_Datum.RowSource = sQry
Me.SEL_Datum.Value = Me.SEL_Datum.ItemData(0)

[Minty]

Don't use a Format or Substring as that returns a string and is not a very efficient function.

If this is in Access Use

DateValue([YourDateTImeField])

Or in the Passthrough to SQL Server

Cast([YourDateTImeField] As Date) as MyDateOnly

Personally I would do this in the server as part of the PT, for efficiency.

[J.Kannengiesser]

with Cast([YourDateTImeField] As Date) the "SELECT DISTINCT" is working - thanks !

But still wondering why in ACEESS "GROUP BY" is working and "DISTINCT" is not when using the format()-function within the passthrough ...

[Minty]

with Cast([YourDateTImeField] As Date) the "SELECT DISTINCT" is working - thanks !

But still wondering why in ACEESS "GROUP BY" is working and "DISTINCT" is not when using the format()-function within the passthrough ...

Glad that helped!

Group By : No idea I'm afraid without seeing in the data and having a little play around with it, and the query.