DCount syntax error or similar

[Paul Clark]

I have a table OwnershipLog including fields Membership_No and [Current Owner]
and want to retrieve [Current Owner] using open form field X_MemNo


This Code:
If DCount("[Current Owner]", "OwnershipLog", "Membership_No = " & Me!X_MemNo & " ") > 0 Then
MsgBox ("This Member is already a Current Owner")
Else
MsgBox "Run Code"
End If


Returns, Run-time error ‘2467’ and other variation return syntax error.
Any clues?

 

[arnelgp]

if DCount() function did not find anything according to your criteria, it will return Null.
so testing Null against 0, will result in error.
the fix is cast to Nz() function:

If Nz(DCount("[Current Owner]", "OwnershipLog", "Membership_No = " & Me!X_MemNo ), 0) > 0 Then

 

[Paul Clark]

Tried this but gets a similar error:
2467 "The expresion you entered refers to an object that is closed or doesn't exist."

 

[moke123]

try a different event. it could be the value of the control is not yet available when your code runs.

 

[Paul Clark]

Sorry found it, my error.
I had not specfied the correct form.

Dim stDocName As String

stDocName = "frm_memnocar"

The perils of copying and modifying code!

 

[sneuberg]

if DCount() function did not find anything according to your criteria, it will return Null.
so testing Null against 0, will result in error.
the fix is cast to Nz() function:

If Nz(DCount("[Current Owner]", "OwnershipLog", "Membership_No = " & Me!X_MemNo ), 0) > 0 Then

DCount returns zero if the function doesn't find anything according to the criteria. I believe you are thinking about DLookup which does return Null in that case.