Task . Query without MEMO and OLE fields.
Lets say we have query MyQuery with field MyField. In query you may have other fields which can use predicate DISTINCT. MyQuery can be sorted or unsorted.
We need to build a query which will not change the order of the fields but will add calculated field Num which will represent the record number in MyQuery, starting from 1.
Solution.
SQL of the query:
SELECT DISTINCT Numeration(MyField) As Num, MyField FROM MyQuery WHERE Numeration() = 0;
as you see in the query is used function Numeration. this is how it is build:
Public Function Numeration(Optional var) As Long
Static n As Long
If IsMissing(var) Then
n = 0
Else
n = n + 1
End If
Numeration = n
End Function
Comments 1. query is optimized by the sql so the value of the function in WHERE part which is independent from the query fields is calculated only once. This is used to set the initial value for sequential numeration
Comment 2. Predicate DISTINCT is used to convert the dinamic query to static . (In dinamic query it doesn't work.) Its direct role Distinct predicate is not used in this case as every record becomes unique of the enumeration although it prevent usage the MEMO and OLE fields.
You can convert those fields to the String type using Mid(Field_MEMO_OLE,1). As result there will be no conflict , but OLE field will lose it functionality. if you have only MEMO fields, then such approach can be used.
In fact you don't need this "Comments" stuff but just for the future reference.
