Count number of unique based on a range of date

[Soundje]

Hello
I am wondering if anybody could help me on finishing this code/query or tell me what is wrong?

 ' count records in query
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim strSQL As String
    Dim beginDatum As String
    Dim eindDatum As String
    Set db = CurrentDb
    
    beginDatum = Me.begDatum
    eindDatum = Me.eindDatum
    
    strSQL = "SELECT COUNT (tbl_data_leverancier.ContractID) AS total FROM (SELECT tbl_data_leverancier.ContractID, COUNT (tbl_data_leverancier.ContractID) AS total FROM tbl_data_leverancier WHERE tbl_data_leverancier.Datum Between #'" & beginDatum "'And #'" & eindDatum & "' GROUP BY tbl_data_leverancier.ContractID)"   
      
    'Set Recordset Query
    Set rs = db.OpenRecordset(strSQL)
    RecordCount = rs.Fields(0)
     rs.Close
    Set rs = Nothing
    Set db = Nothing
 
strTitle = "AANTAL !!!"
MsgBox RecordCount, vbOKOnly + vbInformation, strTitle

Thanks a lot for all your help or ideas
Koen

 

[spikepl]

How are we supposed to guess "what's wrong" ? What do you get and what do you expect to get? That is info yu should always provide when asking for help.

 

[jdraw]

Totally agree with spikepl.

When posting a question, the reader knows nothing of you, your environment, your skills. So you have to identify What you are trying to accomplish -- preferably in plain English with no jargon and minimal database/Access. Then tell/show us what you have tried, and what results (error msg/number), and your description of the problem.

Without such context, readers can only guess----and we are not clairvoyant.

 

[Soundje]

Thanks for the feedback,

Database structure
contractID Datum Weight
2018 01/01/2015 45
2018 01/01/2015 50
2019 01/01/2015 49
2019 05/01/2015 17
2018 06/01/2015 28
2020 05/01/2015 45
2021 10/01/2015 37

The goal is to have the total count of unique contractID whchi should be --> 4
ON top of that I want to have a filter saying only contractID that have a data between 05/01/2015 and 06/01/2015 --> 3

This is actually what I would like to achieve
In the code above I created to textfield to define the
startdate = begindatum
enddate = einddatum

When I now run my current code (above) I get a syntax error for the SQLstatement..

So that is where I am asking help to have look to it

Many thanks,
Koen

 

[MarkK]

I recommend that you write you SQL in the designer, which gives you feedback about your errors immediately.

Create a new query, open it in SQL view, and paste in your SQL. It will suggest problems, which you can then fix, and it will suggest other problems, and then fix those.

Then, once you have the result you want, copy that SQL back into your code.