assigning result of SQL select to a label

[toon10]

It’s been years since I did Access programming so apologies if this is remarkably simple! I have an SQL statement which produces a count figure (in this case 312.) All I want to do is assign this SQL “SELECT DISTINCT Count(tblSales.[Latest Price]) AS LPrice FROM tblSales;” result to a form label.

I used to do this sort of thing in my sleep but I can’t seem to get it working!

Thanks

 

[PHV]

If VBA for access:
Me![label name].Caption = DCount("[Latest Price]", "tblSales")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[toon10]

Thanks for the reply.

Unfortunately, the text box displays the "#Name?" text rahter than the result. That will also include duplicates so I need to use the DISTINCT keyword somewhere.

Thanks

 

[toon10]

My mistake, slight typo. I think thats OK!

Cheers

 

[toon10]

Actually, that still returns a count of all records and not DISTINCT.

 

[PHV]

JetSQL lacks the Count(DISTINCT ...) aggregate function.
You may try this:
Dim rs As DAO.Recordset, sql As String
sql = "SELECT Count(*) FROM (SELECT DISTINCT [Latest Price] FROM tblSales) D"
Set rs = CurrentDb.OpenRecordset(sql)
Me![label name].Caption = rs(0)
rs.Close
Set rs = Nothing


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[toon10]

That does the trick.

Thanks for the help, much appreciated.
Andrew