Dcount

[dwayne dibley]

What I need to do is count records based on 2 criteria(member_id and returned date is null).

I have set the below code in the after update event on the member_id field and the result will show in a separte unbound text box.

Private Sub Member_id_AfterUpdate()
Dim LTotal As Long

LTotal = DCount("member_id", "tblloan", "Member_Id = Me.Member_Id" and returned date is null)
MsgBox (LTotal)
End Sub

I/m using the message box to verify the return. Anyway It normally retruns a blank msgbox.

Any ideas??

 

[dkinley]

The problem is your syntax. When using two criteria it can get a little tricky. I know this isn't 100% correct but it should be something along the lines of ...

LTotal = DCount("member_id", "tblloan", "[Member_Id] = " & Me.Member_Id & " And IsNull([ReturnDateFieldName]))

I've only evaluated a null once before in a situation like this and do not have the db in front of me to verify - perhaps someone else can chime in if they are aware.

Here is more information though ...
http://support.microsoft.com/kb/285866

-dK

 

[namliam]

LTotal = DCount("member_id", "tblloan", "[Member_Id] = " & Me.Member_Id & " And IsNull([ReturnDateFieldName]))

Almost
LTotal = DCount("member_id", "tblloan", "[Member_Id] = " & Me.Member_Id & " And [ReturnDateFieldName] is null " )

Isnull () is 3gl,meaning VBA, is null is SQL and DLookup (99% sure of that) and you were missing the closing "

 

[dwayne dibley]

Thanks for ya help.


private Sub Member_id_AfterUpdate()
Dim LTotal As Long


LTotal = DCount("Member_id", "tblloan", "[Member_id] = " & Me.Member_Id & " And [returned_date] is null ")

MsgBox (LTotal)

This is my current code. It is returning error message:-

runtime error 3464

data ype mismatch in criteria expression. Any idea??

 

[dkinley]

I am not sure of the latter criteria, but if the ID field is a text field then that would be a source of error ....

Currently it is (for a number field): "[Member_id] = " & Me.Member_Id & "

If it is a text field then it should be: "[Member_id] = '" & Me.Member_Id & "'

As an added note for something that might come up after this is resolved. If the DLookup does not find any records meeting the criteria it will return a null. Are you handling this so the program deosn't error on the null?

For instance, suppose you wanted to do something based on this condition. A simplified way to handle the null return is something like ...

If IsNull(DCount(.......)) Then
'do what you want if there are no records
Else
'do something else
End If

-dK

EDIT: I forgot you are assigning it to a variable. To clarify if you try to use the variable the program will error because there is nothing there. Thus the expression should look like .... LTotal = Nz(DCount ...... ),0). This will ensure the variable is assigned something - here a 0 to indicate zero records instead of a null value.

 

[dwayne dibley]

I am not sure of the latter criteria, but if the ID field is a text field then that would be a source of error ....

Currently it is (for a number field): "[Member_id] = " & Me.Member_Id & "

If it is a text field then it should be: "[Member_id] = '" & Me.Member_Id & "'

As an added note for something that might come up after this is resolved. If the DLookup does not find any records meeting the criteria it will return a null. Are you handling this so the program deosn't error on the null?

For instance, suppose you wanted to do something based on this condition. A simplified way to handle the null return is something like ...

If IsNull(DCount(.......)) Then
'do what you want if there are no records
Else
'do something else
End If

-dK

EDIT: I forgot you are assigning it to a variable. To clarify if you try to use the variable the program will error because there is nothing there. Thus the expression should look like .... LTotal = Nz(DCount ...... ),0). This will ensure the variable is assigned something - here a 0 to indicate zero records instead of a null value.

It worked perfect. Thanks alot.

 

[dkinley]

Hot diggity

Ah .... You can thank the Mailman - you and I would have been pounding our heads in on the null if not for him.

-dK

 

[namliam]

@Dkinly
DLookup does return a NULL value if no match was found, DCount just returns 0. As DCount is beeing used here it is not a problem
If it is a problem NZ(Dlookup(),"") would resolve the Null nicely

@Dwayne
MsgBox (LTotal)
remove the ()

and check, indeed like dK said, the datatype of your ID.

 

[dkinley]

hehehe ...

Oh.

I was under the impression that all domain functions returned a null. My mistake Dwayne. Thanks for posting back, Mailman.

-dK

 

[namliam]

Helping 2 people in 1 post/thread... Wish that was 20... but hey 2 is good too

Happy coding folks!