Distinct count of records

[Tep]

Hi,

I am trying to make a distinct count of records from a dataset that is generated by the following code:

sSql = "SELECT * FROM [Leverantie vs meetgegevens] WHERE [Leverantie vs meetgegevens].lev_id = " & CbLeverantie.Column(0) & " ORDER BY bmp_id, methode;"
    [SF Leverantie overzicht].RowSource = sSql
    [SF Leverantie overzicht].Requery

I would like to put the result in the textbox [Text12] on the form [Leverantie vs meetgegevens]. The dataset is shown in a listbox on the same form. The distinct count has to be performed on the variable [bmp_id].

Can anybody give me a suggestion how to do this? It would be nice to have something like:

Me![Text12].Value = DCount("SELECT DISTINCT [bmp_id]FROM [Leverantie vs meetgegevens]WHERE [Leverantie vs meetgegevens].lev_id = " & CbLeverantie.Column(0) & "")

(This code give the error: "Compile error. Argument not optional" on the DCount.)
Or is it possible to use the sSql (which contains the dataset) in a DCount?

Thanks,
Tep

 

[John Big Booty]

Check the correct syntax for the Dcount() function.

 

[Tep]

Yes, you are wright.
It should be of course something like:

Me![Text12].Value = DCount("[bmp_id]", "[Leverantie vs meetgegevens]", "[Leverantie vs meetgegevens].lev_id = " & CbLeverantie.Column(0) & "")

But it looks like that in this code it is not possible to insert a distinct.
Thats why i was trying to put a select statement into the code.
But maybe an other way is far more easy?

 

[vbaInet]

Create a query based on this SELECT statement:

SELECT DISTINCT [bmp_id] FROM [Leverantie vs meetgegevens] WHERE lev_id = Eval("[Forms]![[COLOR=red]NameOfForm[/COLOR]]![CbLeverantie].[Column](0)");

The only bit that needs changing is the bit in red.

Then you can use the DCount() function like this:

Me![Text12].Value = DCount("*", "[COLOR=red]QueryName[/COLOR]")

The form must obviously be open when you run the code.

 

[Tep]

GREAT! It works. Thank you very much!

 

[vbaInet]

Happy to hear!