Dcount return incorrect result for filtered record

S

shiro

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
 
A

Allen Browne

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.
 
S

shiro

Mr Allen,
Haven't test your suggestion,just going to try it.
And about the code,
it just about paste problem,actual code is like below:


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



And the Dcount is on a textbox on "Sales Product" form.Where the property
of it's data source is set to:

=DCount(" [TotalSales]","[Sales Product_qry]","[Status]='Cash'")

[TotalSales] is an expresion field in [Sales Product_qry].

Rgds,

Shiro


Allen Browne said:
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

Reply to group, rather than allenbrowne at mvps dot org.

shiro said:
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
 
S

shiro

Mr Allen,
I have tested your code but it brought me a new
problem.An Access Message appear
"The open form was cancelled'

It mean I can't open the "Sales Product" form.
What I see in the immediate window is correct filter.

In the on open even of Sales Product form I put some
code to check whether the Filter form is open.If it's not
openning will be cancelled and else Me.FilterOn = True.
Do you think the problem is in the 'on open' even of the
form Sales Product.

And the other
On Current even of Sales Product I also added the code
Me.FilterOn = True ,so the user can't move to another
record.

Rgds,

Shiro




shiro said:
Mr Allen,
Haven't test your suggestion,just going to try it.
And about the code,
it just about paste problem,actual code is like below:


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



And the Dcount is on a textbox on "Sales Product" form.Where the property
of it's data source is set to:

=DCount(" [TotalSales]","[Sales Product_qry]","[Status]='Cash'")

[TotalSales] is an expresion field in [Sales Product_qry].

Rgds,

Shiro


Allen Browne said:
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

Reply to group, rather than allenbrowne at mvps dot org.

shiro said:
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
 
A

Allen Browne

Okay, you will need to remove some of those things, and take it one step at
a time. Once you get one thing working, you can then work on the next.

The OpenForm should work if:
a) the Filter is valid, and
b) the form name is correct, and
c) you are not messing it up with events in the target form.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top