'Select Distinct' query in VBA

[bermetj]

I have a dynamic report generated based on selections on a form. User
selects values from combo boxes and then selects which report to run.
How would i write a "select distinct" query in VBA? Currently I have
the following code:

strSQL = "Distinct [Compound] " & strCompound & " AND [Indication] " &
strIndication & _
" AND [SiteStatus] " & strStatus & " AND [PI Lname] " &
strLName

strOrder = "[PI LName], [Compound], [Indication]"

With Reports![rpt Quick Reference]
.Filter = strSQL
.FilterOn = True
.OrderBy = strOrder
.OrderByOn = True
End With

Thank you!

 

[Barry Gilbert]

I don't believe you can use the distinct predicate in a filter statement.

I would suggest that you use the value from your form as criteria in the
query that the report is based on. This way you won't need a filter
statement. In the criteria row under each field in the query, point to a
control on your form like this:

Forms!MyForm!txtCompound

If you want to make any field optional, use this:
Like "*" & Forms!MyForm!txtCompound

This says that, if the user leaves that control blank, it will return all
values from that table field.

Barry

 

[JK]

strSQL = "SELECT Distinct [Compound] " .... etc

Regards/JK