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')
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 & " ,SUBSTRING(Datum,01,10) as JMT "
sTmp = sTmp & " ,SUBSTRING(Datum,12,02) as HH "
sTmp = sTmp & " FROM EDV_MDEKopf "
...
End Function
OLD: ("SELECT distinct [JMT]" returns ALL rows whereas "SELECT distinct [Auftragsart]" is working !?)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
NEW: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)
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)