DISTINCT problems with ORDER BY random function

[rodrigohv]

Guys,

I have a problem trying to incorporate this SQL to show results in the program made in VB6, the purpose of this one is to show random results each time anyone enter to the window, however Access cannot display anything, it say ORDER BY have a problem with DISTINCT function....

I have done a VB Module into database called "Module1" which have next content:

Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function


when i test this one by separate, works perfect, but when i try to integrate this, it dont work... this file was done in a XML

HELP woulb be appreciated a LOT !!!!!!!!!!!!!!!!!!!!!!!!


SELECT DISTINCT objetos.id_objeto, IIf(IsNull((select TOP 1 propiedad1 from prop_objeto where randomizer() = 0 and prop_objeto.id_objeto = objetos.id_objeto and not isnull(propiedad1))),IIf([propiedad4]='',(select top 1 propiedad1 from prop_objeto where prop_objeto.id_objeto = objetos.id_objeto and not isnull(propiedad1)),[propiedad4]),(select TOP 1 propiedad1 from prop_objeto where prop_objeto.id_objeto = objetos.id_objeto and not isnull(propiedad1))) AS prop1, objetos.id_tipo_objeto, objetos.novedad FROM objetos INNER JOIN copias ON objetos.id_objeto = copias.id_objeto WHERE (((copias.propietario)=1) AND ((objetos.estado)=0) AND ((objetos.edad)=25) AND ((objetos.nomostrar)=False))
ORDER BY rnd((prop_objeto.propiedad1) * 0 + 1);