error 3601 too few parameters, expected 1

[JeroenMioch]

Can anyone look at my SQL and look if i made a mistake ?

Dim strUsername As String
strUsername = fOSUserName()


   Dim strSQL As String
    strSQL = "SELECT DISTINCT IDAgenda FROM tblAgenda " _
     & "WHERE AfspraakGeexporteerd = 'No' AND Date() <= DatumAgenda AND time() < TijdAgenda AND ChoosePostBox = strUsername"
      With CurrentDb.OpenRecordset(strSQL)
       If .RecordCount > 0 Then

'do stuff

       else
'Outta here
       exit sub
end with
end if

[ranman256]

not sure if you can
With CurrentDb.OpenRecordset(strSQL)
(it may work,tho never tried)

try
set rst = CurrentDb.OpenRecordset(strSQL)
if rst.recordcount > 0 then

BUT, ChoosePostBox = strUsername": is inside the quote...it must be outside, and have quotes around it ...STRINGS.

... and ChoosePostBox ='" & strUsername & "'"

[JeroenMioch]

Yes, that was one of the issues.. (i should have known that pff).
Still doesnt work though. the .recordcount method is correct though, i use it all the time

[John_G]

The error message you are getting suggests that one or more of the fields referenced in your where clause do not appear in the table tblAgenda.

You might also try changing Date() <= DatumAgenda to DatumAgenda >= date() and time() < TijdAgenda to TijdAgenda > time(). I have a hunch (unconfirmed!) that MS Access sometimes assumes the value on the left of the comparison is a table/query field.

[ssanfu]

There are a couple of issues I see.

The first is that you have crossed control structures. I took out everything except the control syntax:

    With CurrentDb.OpenRecordset(strSQL)
        If .RecordCount > 0 Then

        Else

        End With
    End If

See how the "End With" and the "End If" are swapped?



And the error you are getting (usually) means the SQL is expecting a parameter. You need to concatenate variables (values) in the SQL string.
I changed you code a little. Try executing this and look in the immediate window.

Sub test11()
    Dim strUsername As String
    Dim strSQL As String

'    strUsername = "Mighty Mouse" ' for my testing - I don't have fOSUserName()
    strUsername = fOSUserName()

    strSQL = "SELECT DISTINCT IDAgenda "
    strSQL = strSQL & " FROM tblAgenda "
    strSQL = strSQL & " WHERE AfspraakGeexporteerd = 'No' "
    strSQL = strSQL & " AND Date() <= DatumAgenda "
    strSQL = strSQL & " AND time() < TijdAgenda "
    strSQL = strSQL & " AND ChoosePostBox = strUsername"

'Comment out or delete after debugging ----------------
    Debug.Print strSQL
    Stop
'end debugging  ------------------------

    With CurrentDb.OpenRecordset(strSQL)
        If .RecordCount > 0 Then

            'do stuff

        Else
            'Outta here
            Exit Sub
        End If
    End With

End Sub

Is the SQL what you would expect?

Here is the revised sub, modified as John_G suggested (and I agree):

Sub test11()
    Dim strUsername As String
    Dim strSQL As String

'    strUsername = "Mighty Mouse" ' for my testing - I don't have fOSUserName()
    strUsername = fOSUserName()

    strSQL = "SELECT DISTINCT IDAgenda "
    strSQL = strSQL & " FROM tblAgenda "
    strSQL = strSQL & " WHERE AfspraakGeexporteerd = 'No'"
    strSQL = strSQL & " AND DatumAgenda >= #" & Date & "#"
    strSQL = strSQL & " ANDTijdAgenda > #" & Time() & "#"
    strSQL = strSQL & " AND ChoosePostBox = '" & strUsername & "'"
    
'Comment out or delete after debugging ----------------
    Debug.Print strSQL
    Stop
'end debugging  ------------------------

    With CurrentDb.OpenRecordset(strSQL)
        If .RecordCount > 0 Then

            'do stuff

        Else
            'Outta here
            Exit Sub
        End If
    End With

End Sub

Compare the other SQL string to this SQL string.

[JeroenMioch]

Hey guys and thanks for the replies.

After a few tweaks i could get the code working. However i have discovered that my code lacks the correct Date / time check.
When you think about it, it is to be expected really.
The SQL doesnt count records that is before the time, On whatever day.

That is obviously not my goal :P

I tried this code (notice the blue '+' sign between date and time)
But SQL doesnt get that obviously.

    Dim strUsername As String
    Dim strSQL As String


    strUsername = fOSUserName()


    strSQL = "SELECT DISTINCT IDAgenda "
    strSQL = strSQL & " FROM tblAgenda "
    strSQL = strSQL & " WHERE AfspraakGeexporteerd = False "
    strSQL = strSQL & " AND DatumAgenda >= #" & Date & "#"
    strSQL = strSQL & " + TijdAgenda > #" & Time & "#"
    strSQL = strSQL & " AND ChoosePostBox = '" & strUsername & "'"


    With CurrentDb.OpenRecordset(strSQL)
        If .RecordCount > 0 Then
        
        MsgBox .RecordCount
        Else
        MsgBox "No records"
        
        End If
    End With

Any thoughts on this ?

[JeroenMioch]

Right i figured it out on my own :

    Dim strUsername As String
    Dim strSQL As String
    Dim strDateTime As Date
    
    strUsername = fOSUserName()
    
    strSQL = "SELECT DISTINCT IDAgenda "
    strSQL = strSQL & " FROM tblAgenda "
    strSQL = strSQL & " WHERE AfspraakGeexporteerd = False "
    strSQL = strSQL & " AND DatumAgenda + TijdAgenda > #" & strDateTime & "#"
    strSQL = strSQL & " AND ChoosePostBox = '" & strUsername & "'"