Run-time error 3021 (1 Viewer)

[PatAccess]

Hello Guys,
The following code worked in another form and returns a string but
I am getting run-time error 3021 from my code. When I step through it sqlDateEarned shows a value but the value is not transmitting to strDateEarned so I am getting the error message. Can you please help me. (It is support to return a date).

Private Sub cmdDateEarned_Click()
    Dim sqlDateEarned As String
    Dim strDateEarned As Date
    
    sqlDateEarned = "SELECT DateEarned FROM [QryPE] WHERE DateEarned=#" & Me.DateEarned.Value & "# AND FName='" & Me.cboFName.Column(1) & "' AND [State]='" & Me.cboStates.Value & "'"
    strDateEarned = CurrentDb.OpenRecordset(sqlDateEarned).Fields(0).Value
    
    If IsNull(strDateEarned) Then
        MsgBox "There is no earned date for this license", vbCritical, "No Date Earned"
    Else
        MsgBox "This license was earned on " & strDateEarned & ".", , "Date Earned"
    End If

End Sub

Thank you,

 

[Gasman]

No idea what error 3021 is, and I am not going to go looking to find out.
Is any data returned by the sql statement?
Why are you looking for DateEarned when that is one of the values you are supplying?

 

[theDBguy]

Hi. You could just use DCount() for this, I think.

 

[Micron]

Or even DLookup, as it appears that there would only be one date value or no date value.

PatAccess - advise to always post the error message as well. There must be around 3,000 of them and it is just luck if anyone recognizes just what the number means. Besides, some of the numbers signify different errors, in which case it all depends on the message.
A couple of tips:
- you don't need .Value as it is the default property
- you might find using a variable prefix (e.g. str) confusing when you use it for multiple data types.

 

[CJ_London]

if your query doesn't return at least one record, then the line

strDateEarned = CurrentDb.OpenRecordset(sqlDateEarned).Fields(0).Value


will error with 'can't find current record' (error 3021)

recommend use dcount as already suggested

if dcount("*","QryPE","DateEarned=#" & Me.DateEarned.Value & "# AND FName='" & Me.cboFName.Column(1) & "' AND [State]='" & Me.cboStates.Value & "'")=0 then 'no record found

else 'record found

end if

presume your country date format is US style (mm/dd/yyyy) otherwise you will need to use the format function to format it that way