Re: Dcount return incorrect result for filtered record

Tech-Archive recommends: Fix windows errors by optimizing your registry

I can't see a DCount() in your code, but there is a problem with the filter string.

1. The values need to be concatenated into the string.
2. You need to use delimiters around the literal values.
3. You need the AND operator between items.
4. Brackets are optional but recommended.

Assuming that [Part Name] is a Text field, use quotes as the delimiter, and concatenate the value into the string like this:
strFilter = "([Part Name] = """ & Me.PartName_txt & """) AND "

If the quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

If [Lot No] is a Text field, it will need the quotes in the same way. If it is a Number field, you code like this:
strFilter = "([Lot No] = " & Me.Lot_txt & ") AND "

We have tagged the " AND " to the end of each one, because that makes it really easy to add as many more criteria as you need. At the end, we have to remove the trailing " AND ", so:
If strFilter <> vbNullString Then
strFilter = Left$(strFilter, Len(strFilter) - 5)
End If

If you still have problems, add the line:
Debug.Print strFilter
When it fails, open the Immediate Window (Ctrl+G) and see if you can see what's wrong with the filter string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"shiro" <shiro@xxxxxxxx> wrote in message
news:%23BJwfYulIHA.5280@xxxxxxxxxxxxxxxxxxxxxxx
Hi all,
It's strange.
Dcount formula return incorrect result if I define
where condition on open form even.I have some code
on click even of cmd_OK button.The code is :

'====================================
Dim strMessage as String
Dim strFilter as String

If IsNull(Me.PartName_txt) Then
strMessage = strMessage & _
"Enter Part name" & vbCrLf
Else
strFilter = "[Part Name] = [Forms]![Filter Data_frm]![PartName_txt]"
End If

If IsNull(Me.Lot_txt) Then
strMessage = strMessage & _
"Enter product Lot No" & vbCrLf
Else
strFilter = "[Lot No] = [Forms]![Filter Data_frm]![Lot_txt]"
End

If Len(strMessage)=0 Then
Me.Visible = False
DoCmd.OpenForm "Sales Product", acNormal, , strFilter
Else
MsgBox strMessage, vbOKOnly, "Invalid Filter"
End If
'=====================================================

The code return incorrect Dcount result,although the filter
return zero records,the value of Dcount is 1,325.
If I remove the strFilter from my code and put it in the
query's criteria row of "Sales Product" form ,
the Dcount work correctly.

What is wrong with my Dcount field?Hope somebody would like
to help me out of this problem?
Thank's

Rgds,

Shiro

.

Quantcast