Running DSum problem-Please Help

  • Thread starter Thread starter HM
  • Start date Start date
H

HM

Dear all,
I have MS Access2K all data in table is char fields.
How can I run DSum for char field? Like I want to count
how many record sex is 1 (male) and 2 is female.
I write like this but error occur :Can not sum on char
field!. Here is my code:

Dim Q1 as interger
For q1=1 to 40
Q1(1)=DSum("[sex]", "Table1", "[sex]='1' ")
Q1(2)=DSum("[sex]", "Table1", "[sex]='2' ")
Next Q1
Please help, thank you-HM
 
This should work.
Q1(1)=DCount("[sex]", "Table1", "[sex]='1' ")
Q1(2)=DCount("[sex]", "Table1", "[sex]='2' ")

Martin
 
You can use DCount to count records:
NumMales = DCount( "*", "Table1", "[sex]='1' " )

Another trick which is useful in queries is to sum a boolean (true/false)
value, which is 0 for False and -1 for True. Then you can use the Abs
function to flip the sign and give you a count:
NumMales: Abs(Sum([sex]='1'))
NumFemales: Abs(Sum([sex]='2'))

There are more serious problems with the code you have below though. You
seem to be repeating the operation 40 times in a loop, but then you are
treating the loop control variable (Q1) as an array and assigning results to
it.

This doesn't make sense at all. Perhaps you can explain what you're trying
to achieve?

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Yes, it is work wonderful.
Thank you :)
HM
-----Original Message-----
This should work.
Q1(1)=DCount("[sex]", "Table1", "[sex]='1' ")
Q1(2)=DCount("[sex]", "Table1", "[sex]='2' ")

Martin
-----Original Message-----
Dear all,
I have MS Access2K all data in table is char fields.
How can I run DSum for char field? Like I want to count
how many record sex is 1 (male) and 2 is female.
I write like this but error occur :Can not sum on char
field!. Here is my code:

Dim Q1 as interger
For q1=1 to 40
Q1(1)=DSum("[sex]", "Table1", "[sex]='1' ")
Q1(2)=DSum("[sex]", "Table1", "[sex]='2' ")
Next Q1
Please help, thank you-HM
.
Click to expand...
.
Click to expand...
 
Hi Graham,
I using Dcount it is working wonderful.
I want create a loop count to 40 in order to assign 40
variables to the report. Do you see it is work or not? I
am not try yet?
Do you have any ideals with my For loop?
Best Regards,
HM
-----Original Message-----
You can use DCount to count records:
NumMales = DCount( "*", "Table1", "[sex]='1' " )

Another trick which is useful in queries is to sum a boolean (true/false)
value, which is 0 for False and -1 for True. Then you can use the Abs
function to flip the sign and give you a count:
NumMales: Abs(Sum([sex]='1'))
NumFemales: Abs(Sum([sex]='2'))

There are more serious problems with the code you have below though. You
seem to be repeating the operation 40 times in a loop, but then you are
treating the loop control variable (Q1) as an array and assigning results to
it.

This doesn't make sense at all. Perhaps you can explain what you're trying
to achieve?

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Dear all,
I have MS Access2K all data in table is char fields.
How can I run DSum for char field? Like I want to count
how many record sex is 1 (male) and 2 is female.
I write like this but error occur :Can not sum on char
field!. Here is my code:

Dim Q1 as interger
For q1=1 to 40
Q1(1)=DSum("[sex]", "Table1", "[sex]='1' ")
Q1(2)=DSum("[sex]", "Table1", "[sex]='2' ")
Next Q1
Please help, thank you-HM
Click to expand...


.
Click to expand...
 
Hi HM

HM said:
Hi Graham,
I using Dcount it is working wonderful.
Click to expand...

Great! :-)
I want create a loop count to 40 in order to assign 40
variables to the report. Do you see it is work or not? I
am not try yet?
Do you have any ideals with my For loop?
Click to expand...

Well, there is no point in performing the same DCount 40 times, as you will
get the same result each time. Why do you want 40 variables, all with the
same value?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Back
Top