dcount return 0

[Jon]

Greeting

I have a continues form that has the employee ID ,Name and a fields start
from Sat to Thru. This form is used for showing employee attendance. I put
unbound text to count the field which contain "absent" and I put the
following code but the result is "0"

Private Sub Form_Current()
Me.Text16 = DCount("[ID]", "Attendance",
"[Sat]+[Sun]+[Mon]+[Thu]+[Wend]+[Thru]='Absent'")
End Sub

Any Help please?

 

[Dale Fye]

Jon,

1. First off, if you have column names of [Sat], [Sun], ..., then you are
not usind Access like the database it is, but like a spreadsheet. You really
need to read up on relational database structure
(http://www.allenbrowne.com/casu-22.html) and redesign your table.

2. Assuming that you have some sort of numeric value in fields [Sat],
[Sun], ..., then adding the fields (using the + operator) will result in a
numeric value, not a string ("Absent"). If any of theses fields are NULL,
then the sum of the fields will also be NULL. If, these fields are strings,
rather than numeric values, then adding them together like you have will also
result in a NULL value if any of the strings is NULL, or will result in a
concatenated string (the values of each field added to the end of each other)
if none of the values are NULL.

When you to these newsgroups, you really should provide more information,
such as your table structure, the data types of the fields, and even provide
a sample of the data that you might expect to find in your table.

HTH
Dale

 

[aaron_kempf]

I'm _NOT_ so sure that they need to redesign the table.

Honestly-- I don't agree with your diagnosis.

Keep the table like it is; and learn how to write SQL.
And question what the jerks around here tell you.

There is the option of 'SET ANSI NULLS ON' or OFF in SQL Server to
avoid your 'access bug' of adding fields together.

THanks

-Aaron

Jon,

1.  First off, if you have column names of [Sat], [Sun], ..., then you are
not usind Access like the database it is, but like a spreadsheet.  You really
need to read up on relational database structure
(http://www.allenbrowne.com/casu-22.html) and redesign your table.

2.  Assuming that you have some sort of numeric value in fields [Sat],
[Sun], ..., then adding the fields (using the + operator) will result in a
numeric value, not a string ("Absent").  If any of theses fields are NULL,
then the sum of the fields will also be NULL.  If, these fields are strings,
rather than numeric values, then adding them together like you have will also
result in a NULL value if any of the strings is NULL, or will result in a
concatenated string (the values of each field added to the end of each other)
if none of the values are NULL.

When you to these newsgroups, you really should provide more information,
such as your table structure, the data types of the fields, and even provide
a sample of the data that you might expect to find in your table.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Greeting

I have a continues form that has the employee ID ,Name and a fields start
from Sat to Thru. This form is used for showing employee attendance. I put
unbound text to count the field which contain "absent" and I put the
following code but the result is "0"

Private Sub Form_Current()
Me.Text16 = DCount("[ID]", "Attendance",
"[Sat]+[Sun]+[Mon]+[Thu]+[Wend]+[Thru]='Absent'")
End Sub

Any Help please?- Hide quoted text -

- Show quoted text -

 

[akphidelt]

hahaha, I find absolutely nothing wrong with the advice Dale gave. If you
actually read the original post you will notice that there is a lot wrong
with the set up of the query. If you have 7 columns for days that is not in a
Crosstab query then you have a bad set up... and you can't add up Values and
get Text.

I find your diagnosis extremely wrong!

I'm _NOT_ so sure that they need to redesign the table.

Honestly-- I don't agree with your diagnosis.

Keep the table like it is; and learn how to write SQL.
And question what the jerks around here tell you.

There is the option of 'SET ANSI NULLS ON' or OFF in SQL Server to
avoid your 'access bug' of adding fields together.

THanks

-Aaron

Jon,

1. First off, if you have column names of [Sat], [Sun], ..., then you are
not usind Access like the database it is, but like a spreadsheet. You really
need to read up on relational database structure
(http://www.allenbrowne.com/casu-22.html) and redesign your table.

2. Assuming that you have some sort of numeric value in fields [Sat],
[Sun], ..., then adding the fields (using the + operator) will result in a
numeric value, not a string ("Absent"). If any of theses fields are NULL,
then the sum of the fields will also be NULL. If, these fields are strings,
rather than numeric values, then adding them together like you have will also
result in a NULL value if any of the strings is NULL, or will result in a
concatenated string (the values of each field added to the end of each other)
if none of the values are NULL.

When you to these newsgroups, you really should provide more information,
such as your table structure, the data types of the fields, and even provide
a sample of the data that you might expect to find in your table.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Greeting

I have a continues form that has the employee ID ,Name and a fields start
from Sat to Thru. This form is used for showing employee attendance. I put
unbound text to count the field which contain "absent" and I put the
following code but the result is "0"

Private Sub Form_Current()
Me.Text16 = DCount("[ID]", "Attendance",
"[Sat]+[Sun]+[Mon]+[Thu]+[Wend]+[Thru]='Absent'")
End Sub

Any Help please?- Hide quoted text -

- Show quoted text -