Dcount in a query

[zufie]

I am trying to get my Dcount Mod5 in my query to return every 5th
record.

Here is the:
Field Name: Expr1: DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID]) Mod 5
Total: Group By
Criteria: 0

Don't know if this any more helful to you, but here is theVBA behind
my command button that returns every fifth record:

Private Sub Command108_Click()
On Error GoTo Err_Command108_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuery5"

stLinkCriteria = "[CallDate]Between #" & Me.txtBeginQAEvry5th &
"# And #" & Me.txtEndQAStopEvry5th & "#"

Debug.Print stLinkCriteria

DoCmd.OPENFORM stDocName, acNormal, , stLinkCriteria, acFormEdit

Exit_Command108_Click:
Exit Sub

Err_Command108_Click:
MsgBox Err.Description
Resume Exit_Command108_Click

End Sub


Any suggestions would be wonderful and helpful!

Happy Monday!

Zufie

 

[John Spencer]

As a guess, you might need to include the criteria in the DCount

DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID] & " AND [CallDate]Between #" &
Forms!YourFormName!txtBeginQAEvry5th &
"# And #" & Forms!YourFormName!txtEndQAStopEvry5th & "#") Mod 5

You need to tell us what the problem is. Are you getting back the wrong
records, every record, no record? Are you getting a syntax error?

Is CallerID a number field or is it a Text Field? If it is a text field than
you need to add text delimiters.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I am trying to get my Dcount Mod5 in my query to return every 5th
record.

Here is the:
Field Name: Expr1: DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID]) Mod 5
Total: Group By
Criteria: 0

Don't know if this any more helful to you, but here is theVBA behind
my command button that returns every fifth record:

Private Sub Command108_Click()
On Error GoTo Err_Command108_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmQuery5"

stLinkCriteria = "[CallDate]Between #" & Me.txtBeginQAEvry5th &
"# And #" & Me.txtEndQAStopEvry5th & "#"

[snip]

I don't see how that stLinkCriteria can return every fifth
record regardless of what value is in the text box.

If you can guarantee that the [Caller ID] field is
consecutively numbered (autonumber field is no guarantee),

Then your Dcount could use the criteria:
. . .,"[Caller ID] Mod 5 = 1")

BUT, who cares? You are only counting every fifth record so
how is that going to give a better result than:
Expr1: Count(*) \ 5

 

[zufie]

The query just hangs and returns nothing.


CallerID is an Autonumber field.

As a guess, you might need to include the criteria in the DCount

DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID] & " AND [CallDate]Between #" &
Forms!YourFormName!txtBeginQAEvry5th &
"# And #" & Forms!YourFormName!txtEndQAStopEvry5th & "#") Mod 5

You need to tell us what the problem is.  Are you getting back the wrong
records, every record, no record?  Are you getting a syntax error?

Is CallerID a number field or is it a Text Field?   If it is a text field than
you need to add text delimiters.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am trying to get my Dcount Mod5 in my query to return every 5th
record.

Here is the:
Field Name: Expr1: DCount("[Caller ID]","IBCCP Referral","[Caller ID]
<= " & [Caller ID]) Mod 5
Total: Group By
Criteria: 0

Don't know if this any more helful to you, but here is theVBA behind
my command button that returns every fifth record:

Private Sub Command108_Click()
On Error GoTo Err_Command108_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmQuery5"

     stLinkCriteria = "[CallDate]Between #" & Me.txtBeginQAEvry5th &
"# And #" & Me.txtEndQAStopEvry5th & "#"

    Debug.Print stLinkCriteria

    DoCmd.OPENFORM stDocName, acNormal, , stLinkCriteria, acFormEdit

Exit_Command108_Click:
    Exit Sub

Err_Command108_Click:
    MsgBox Err.Description
    Resume Exit_Command108_Click

End Sub

Any suggestions would be wonderful and helpful!

Happy Monday!

Zufie- Hide quoted text -

- Show quoted text -