How do i count and store "selected" check boxes in a query?

  • Thread starter Thread starter Barbara
  • Start date Start date
B

Barbara

I found this function: =Abs(Sum([FieldName])) and it did return the number as
i wanted; however, it will only work when put it in a Report Label. I would
like to store this total in a query so i can use it later as '1 of 10' in a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on the form
based on StartTestNo and EndTestNo (prompt user to enter it). I would like
to use the Total number and write it as "1 of 10", "2 of 10" if and only if
IsGroup is checked.

thanks
 
Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value set on, for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
 
If i want to store the value to the table, how do i do that? I need to use
it later in a report. All orders with the check on value will be group
together.

Michel Walsh said:
Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value set on, for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
Barbara said:
I found this function: =Abs(Sum([FieldName])) and it did return the number
as
i wanted; however, it will only work when put it in a Report Label. I
would
like to store this total in a query so i can use it later as '1 of 10' in
a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on the form
based on StartTestNo and EndTestNo (prompt user to enter it). I would
like
to use the Total number and write it as "1 of 10", "2 of 10" if and only
if
IsGroup is checked.

thanks
Click to expand...
Click to expand...
 
You should be able, in a form, to set a control bound to a field, in the
table you want to save the value. Set the control to the expression:

Me.ControlName = ABS( DSUM( "fieldName", "tableName" ) )

as a line of VBA code, as example.


You can also use an INSERT query:


DoCmd.RunSQL "INSERT INTO tableNameToInsertNewRow
(fieldNameGettingTheValue) VALUES (ABS( DSUM( 'fieldName', 'tableName' ) ) )
"


as example, append one new record to table tableNameToInserNewRow, putting
the desired total in the field fieldNameGettingTheValue.





Vanderghast, Access MVP


Barbara said:
If i want to store the value to the table, how do i do that? I need to
use
it later in a report. All orders with the check on value will be group
together.

Michel Walsh said:
Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value set on,
for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
Barbara said:
I found this function: =Abs(Sum([FieldName])) and it did return the
number
as
i wanted; however, it will only work when put it in a Report Label. I
would
like to store this total in a query so i can use it later as '1 of 10'
in
a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on the form
based on StartTestNo and EndTestNo (prompt user to enter it). I would
like
to use the Total number and write it as "1 of 10", "2 of 10" if and
only
if
IsGroup is checked.

thanks
Click to expand...
Click to expand...
Click to expand...
 
i am sorry, but when i use the "me.countrolname=abs(dsum("fieldname",
"tablename")), it give me this: #name?; so when i take off the
"me.controlname"; it shows me the total count.

click here for a printscreen of my form:
http://www.hosetech.com/control-source.jpg

As for the "INSERT QUERY"; where can/do I add such line of code? In the
Form design view or in a query view? I also have a query just for getting
the total of IsGroup; if it's adding the line of code in the query view;
where?

thank you for being patient, I am a newbie! :)



Michel Walsh said:
You should be able, in a form, to set a control bound to a field, in the
table you want to save the value. Set the control to the expression:

Me.ControlName = ABS( DSUM( "fieldName", "tableName" ) )

as a line of VBA code, as example.


You can also use an INSERT query:


DoCmd.RunSQL "INSERT INTO tableNameToInsertNewRow
(fieldNameGettingTheValue) VALUES (ABS( DSUM( 'fieldName', 'tableName' ) ) )
"


as example, append one new record to table tableNameToInserNewRow, putting
the desired total in the field fieldNameGettingTheValue.





Vanderghast, Access MVP


Barbara said:
If i want to store the value to the table, how do i do that? I need to
use
it later in a report. All orders with the check on value will be group
together.

Michel Walsh said:
Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value set on,
for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
I found this function: =Abs(Sum([FieldName])) and it did return the
number
as
i wanted; however, it will only work when put it in a Report Label. I
would
like to store this total in a query so i can use it later as '1 of 10'
in
a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on the form
based on StartTestNo and EndTestNo (prompt user to enter it). I would
like
to use the Total number and write it as "1 of 10", "2 of 10" if and
only
if
IsGroup is checked.

thanks
Click to expand...
Click to expand...
Click to expand...
 
If you assign it as control source in the property sheet rather than in VBA
code, use only

=abs(dsum("fieldname", "tablename"))


and use [ ] around ill formed name (those with space in them, as example):


=ABS(DSUM("IsGrouped ","[tbl Date Log2]")



To use an insert query, you will have to use VBA code (as a procedure
answering the Click event of a button, as example), and it is more involved
than just using a control property. NOTE though that assigning the control
property to a computed expression (like here, with:
=abs(dsum("fieldname", "tablename")) ) makes that control NOT bound to
any field of a table, and thus, won't save the result. It will only SHOWS
it, and always compute it, so it will always UP TO DATE, which is generally
preferable to 'computation saved into a table', which may NOT be so
perfectly synchronized with what is in the tables.


Vanderghast, Access MVP



Barbara said:
i am sorry, but when i use the "me.countrolname=abs(dsum("fieldname",
"tablename")), it give me this: #name?; so when i take off the
"me.controlname"; it shows me the total count.

click here for a printscreen of my form:
http://www.hosetech.com/control-source.jpg

As for the "INSERT QUERY"; where can/do I add such line of code? In the
Form design view or in a query view? I also have a query just for getting
the total of IsGroup; if it's adding the line of code in the query view;
where?

thank you for being patient, I am a newbie! :)



Michel Walsh said:
You should be able, in a form, to set a control bound to a field, in the
table you want to save the value. Set the control to the expression:

Me.ControlName = ABS( DSUM( "fieldName", "tableName" ) )

as a line of VBA code, as example.


You can also use an INSERT query:


DoCmd.RunSQL "INSERT INTO tableNameToInsertNewRow
(fieldNameGettingTheValue) VALUES (ABS( DSUM( 'fieldName',
'tableName' ) ) )
"


as example, append one new record to table tableNameToInserNewRow,
putting
the desired total in the field fieldNameGettingTheValue.





Vanderghast, Access MVP


Barbara said:
If i want to store the value to the table, how do i do that? I need to
use
it later in a report. All orders with the check on value will be group
together.

:

Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value set on,
for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
I found this function: =Abs(Sum([FieldName])) and it did return the
number
as
i wanted; however, it will only work when put it in a Report Label.
I
would
like to store this total in a query so i can use it later as '1 of
10'
in
a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on the
form
based on StartTestNo and EndTestNo (prompt user to enter it). I
would
like
to use the Total number and write it as "1 of 10", "2 of 10" if and
only
if
IsGroup is checked.

thanks
Click to expand...
Click to expand...
Click to expand...
 
ok. since i have to have the values to be use later; i add an "On Exit Event
Procedure" for the checkbox "IsGroup" as follow:


Private Sub IsGroup_Exit(Cancel As Integer)
DoCmd.RunSQL "INSERT INTO tbl Data Log2 (GroupCount) VALUES (ABS( DSUM(
'IsGroup', '[tbl Data Log2]' ) ) )"
End Sub

Got the following error msg:

got Run-Time Error '3134': Syantax error in INSERT INTO statement

What i am trying to accomplished is to count the number of order that has
the "IsGroup" check and so when i print label for the orders; i can print
only those group and write it as '1 of 17', '2 of 17' etc. please help!

thanks

Michel Walsh said:
If you assign it as control source in the property sheet rather than in VBA
code, use only

=abs(dsum("fieldname", "tablename"))


and use [ ] around ill formed name (those with space in them, as example):


=ABS(DSUM("IsGrouped ","[tbl Date Log2]")



To use an insert query, you will have to use VBA code (as a procedure
answering the Click event of a button, as example), and it is more involved
than just using a control property. NOTE though that assigning the control
property to a computed expression (like here, with:
=abs(dsum("fieldname", "tablename")) ) makes that control NOT bound to
any field of a table, and thus, won't save the result. It will only SHOWS
it, and always compute it, so it will always UP TO DATE, which is generally
preferable to 'computation saved into a table', which may NOT be so
perfectly synchronized with what is in the tables.


Vanderghast, Access MVP



Barbara said:
i am sorry, but when i use the "me.countrolname=abs(dsum("fieldname",
"tablename")), it give me this: #name?; so when i take off the
"me.controlname"; it shows me the total count.

click here for a printscreen of my form:
http://www.hosetech.com/control-source.jpg

As for the "INSERT QUERY"; where can/do I add such line of code? In the
Form design view or in a query view? I also have a query just for getting
the total of IsGroup; if it's adding the line of code in the query view;
where?

thank you for being patient, I am a newbie! :)



Michel Walsh said:
You should be able, in a form, to set a control bound to a field, in the
table you want to save the value. Set the control to the expression:

Me.ControlName = ABS( DSUM( "fieldName", "tableName" ) )

as a line of VBA code, as example.


You can also use an INSERT query:


DoCmd.RunSQL "INSERT INTO tableNameToInsertNewRow
(fieldNameGettingTheValue) VALUES (ABS( DSUM( 'fieldName',
'tableName' ) ) )
"


as example, append one new record to table tableNameToInserNewRow,
putting
the desired total in the field fieldNameGettingTheValue.





Vanderghast, Access MVP


If i want to store the value to the table, how do i do that? I need to
use
it later in a report. All orders with the check on value will be group
together.

:

Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value set on,
for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
I found this function: =Abs(Sum([FieldName])) and it did return the
number
as
i wanted; however, it will only work when put it in a Report Label.
I
would
like to store this total in a query so i can use it later as '1 of
10'
in
a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on the
form
based on StartTestNo and EndTestNo (prompt user to enter it). I
would
like
to use the Total number and write it as "1 of 10", "2 of 10" if and
only
if
IsGroup is checked.

thanks
Click to expand...
Click to expand...
Click to expand...
 
The ill form name of the table is still part of the problem. Try adding the
[ ] around it, this time, near the keyword INTO, as in:



Private Sub IsGroup_Exit(Cancel As Integer)
DoCmd.RunSQL "INSERT INTO [tbl Data Log2] (GroupCount) VALUES (ABS( DSUM(
'IsGroup', '[tbl Data Log2]' ) ) )"
End Sub


That should append a new record into the said table, as long as all the
other fields in the table either allow nulls, either have a default value
(or are autonumber), and that no data integrity rule is violated (such as
not having duplicated value, etc.)

If there is more than ONE field to be specified, use list of names, and
list of values:


DoCmd.RunSQL
"INSERT INTO [tbl Data Log2] (GroupCount , GroupName)
VALUES (ABS( DSUM( 'IsGroup', '[tbl Data Log2]' ) ) ,
FORMS!FormName!ControlWithTheGroupNameToBeSaved )"



will still append one new record, this time with GroupCount field =
ABS(SUM(... ) and GroupName field = the value in the said control for
the said form

as example. If you add a third field in the list of fields, be sure to add a
third value in the list of values.




Vanderghast, Access MVP


Barbara said:
ok. since i have to have the values to be use later; i add an "On Exit
Event
Procedure" for the checkbox "IsGroup" as follow:


Private Sub IsGroup_Exit(Cancel As Integer)
DoCmd.RunSQL "INSERT INTO tbl Data Log2 (GroupCount) VALUES (ABS( DSUM(
'IsGroup', '[tbl Data Log2]' ) ) )"
End Sub

Got the following error msg:

got Run-Time Error '3134': Syantax error in INSERT INTO statement

What i am trying to accomplished is to count the number of order that has
the "IsGroup" check and so when i print label for the orders; i can print
only those group and write it as '1 of 17', '2 of 17' etc. please help!

thanks

Michel Walsh said:
If you assign it as control source in the property sheet rather than in
VBA
code, use only

=abs(dsum("fieldname", "tablename"))


and use [ ] around ill formed name (those with space in them, as
example):


=ABS(DSUM("IsGrouped ","[tbl Date Log2]")



To use an insert query, you will have to use VBA code (as a procedure
answering the Click event of a button, as example), and it is more
involved
than just using a control property. NOTE though that assigning the
control
property to a computed expression (like here, with:
=abs(dsum("fieldname", "tablename")) ) makes that control NOT bound
to
any field of a table, and thus, won't save the result. It will only SHOWS
it, and always compute it, so it will always UP TO DATE, which is
generally
preferable to 'computation saved into a table', which may NOT be so
perfectly synchronized with what is in the tables.


Vanderghast, Access MVP



Barbara said:
i am sorry, but when i use the "me.countrolname=abs(dsum("fieldname",
"tablename")), it give me this: #name?; so when i take off the
"me.controlname"; it shows me the total count.

click here for a printscreen of my form:
http://www.hosetech.com/control-source.jpg

As for the "INSERT QUERY"; where can/do I add such line of code? In
the
Form design view or in a query view? I also have a query just for
getting
the total of IsGroup; if it's adding the line of code in the query
view;
where?

thank you for being patient, I am a newbie! :)



:

You should be able, in a form, to set a control bound to a field, in
the
table you want to save the value. Set the control to the expression:

Me.ControlName = ABS( DSUM( "fieldName", "tableName" ) )

as a line of VBA code, as example.


You can also use an INSERT query:


DoCmd.RunSQL "INSERT INTO tableNameToInsertNewRow
(fieldNameGettingTheValue) VALUES (ABS( DSUM( 'fieldName',
'tableName' ) ) )
"


as example, append one new record to table tableNameToInserNewRow,
putting
the desired total in the field fieldNameGettingTheValue.





Vanderghast, Access MVP


If i want to store the value to the table, how do i do that? I need
to
use
it later in a report. All orders with the check on value will be
group
together.

:

Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value set
on,
for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
I found this function: =Abs(Sum([FieldName])) and it did return
the
number
as
i wanted; however, it will only work when put it in a Report
Label.
I
would
like to store this total in a query so i can use it later as '1
of
10'
in
a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on
the
form
based on StartTestNo and EndTestNo (prompt user to enter it). I
would
like
to use the Total number and write it as "1 of 10", "2 of 10" if
and
only
if
IsGroup is checked.

thanks
Click to expand...
Click to expand...
Click to expand...
 
thank you. when i first change the code as you indicate, it was ok. a
warning window pop-up and warn me that i am about to append 1 row so i click
"yes".

But when i close the form; a window pop-up and says it is adding a new
record to my table. after i click no; i got a Run Time eror '2501' - The
RunSQL action was canceled. if i click "END", it takes me back to the form;
then i try to close it again, and the process repeats again. if i select
DEBUG; it takes me to the VBA code and highlisted my new line of code.

when i close the VBA window; got a warning that says "this comand will stop
the debugger"; i click ok; it closes the VBA window and takes me back to my
table.

when i open my table; i got an empty row of record.

what do i need to do to prevent it from adding a new row when i close the
form?

many thanks.


Michel Walsh said:
The ill form name of the table is still part of the problem. Try adding the
[ ] around it, this time, near the keyword INTO, as in:



Private Sub IsGroup_Exit(Cancel As Integer)
DoCmd.RunSQL "INSERT INTO [tbl Data Log2] (GroupCount) VALUES (ABS( DSUM(
'IsGroup', '[tbl Data Log2]' ) ) )"
End Sub


That should append a new record into the said table, as long as all the
other fields in the table either allow nulls, either have a default value
(or are autonumber), and that no data integrity rule is violated (such as
not having duplicated value, etc.)

If there is more than ONE field to be specified, use list of names, and
list of values:


DoCmd.RunSQL
"INSERT INTO [tbl Data Log2] (GroupCount , GroupName)
VALUES (ABS( DSUM( 'IsGroup', '[tbl Data Log2]' ) ) ,
FORMS!FormName!ControlWithTheGroupNameToBeSaved )"



will still append one new record, this time with GroupCount field =
ABS(SUM(... ) and GroupName field = the value in the said control for
the said form

as example. If you add a third field in the list of fields, be sure to add a
third value in the list of values.




Vanderghast, Access MVP


Barbara said:
ok. since i have to have the values to be use later; i add an "On Exit
Event
Procedure" for the checkbox "IsGroup" as follow:


Private Sub IsGroup_Exit(Cancel As Integer)
DoCmd.RunSQL "INSERT INTO tbl Data Log2 (GroupCount) VALUES (ABS( DSUM(
'IsGroup', '[tbl Data Log2]' ) ) )"
End Sub

Got the following error msg:

got Run-Time Error '3134': Syantax error in INSERT INTO statement

What i am trying to accomplished is to count the number of order that has
the "IsGroup" check and so when i print label for the orders; i can print
only those group and write it as '1 of 17', '2 of 17' etc. please help!

thanks

Michel Walsh said:
If you assign it as control source in the property sheet rather than in
VBA
code, use only

=abs(dsum("fieldname", "tablename"))


and use [ ] around ill formed name (those with space in them, as
example):


=ABS(DSUM("IsGrouped ","[tbl Date Log2]")



To use an insert query, you will have to use VBA code (as a procedure
answering the Click event of a button, as example), and it is more
involved
than just using a control property. NOTE though that assigning the
control
property to a computed expression (like here, with:
=abs(dsum("fieldname", "tablename")) ) makes that control NOT bound
to
any field of a table, and thus, won't save the result. It will only SHOWS
it, and always compute it, so it will always UP TO DATE, which is
generally
preferable to 'computation saved into a table', which may NOT be so
perfectly synchronized with what is in the tables.


Vanderghast, Access MVP



i am sorry, but when i use the "me.countrolname=abs(dsum("fieldname",
"tablename")), it give me this: #name?; so when i take off the
"me.controlname"; it shows me the total count.

click here for a printscreen of my form:
http://www.hosetech.com/control-source.jpg

As for the "INSERT QUERY"; where can/do I add such line of code? In
the
Form design view or in a query view? I also have a query just for
getting
the total of IsGroup; if it's adding the line of code in the query
view;
where?

thank you for being patient, I am a newbie! :)



:

You should be able, in a form, to set a control bound to a field, in
the
table you want to save the value. Set the control to the expression:

Me.ControlName = ABS( DSUM( "fieldName", "tableName" ) )

as a line of VBA code, as example.


You can also use an INSERT query:


DoCmd.RunSQL "INSERT INTO tableNameToInsertNewRow
(fieldNameGettingTheValue) VALUES (ABS( DSUM( 'fieldName',
'tableName' ) ) )
"


as example, append one new record to table tableNameToInserNewRow,
putting
the desired total in the field fieldNameGettingTheValue.





Vanderghast, Access MVP


If i want to store the value to the table, how do i do that? I need
to
use
it later in a report. All orders with the check on value will be
group
together.

:

Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value set
on,
for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
I found this function: =Abs(Sum([FieldName])) and it did return
the
number
as
i wanted; however, it will only work when put it in a Report
Label.
I
would
like to store this total in a query so i can use it later as '1
of
10'
in
a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on
the
form
based on StartTestNo and EndTestNo (prompt user to enter it). I
would
like
to use the Total number and write it as "1 of 10", "2 of 10" if
and
only
if
IsGroup is checked.

thanks
Click to expand...
Click to expand...
Click to expand...
 
It is because you used the "On Exit Event Procedure". I thought it was
making sense, for your case, to add the record only when you try to exit
(close) the form. IF that makes no sense, use some other event, such as
adding a button for that specific purpose and then, add the code under the
Click event for that button. To save a record, the end user of your form
will then have to click that button.

Vanderghast, Access MVP


Barbara said:
thank you. when i first change the code as you indicate, it was ok. a
warning window pop-up and warn me that i am about to append 1 row so i
click
"yes".

But when i close the form; a window pop-up and says it is adding a new
record to my table. after i click no; i got a Run Time eror '2501' - The
RunSQL action was canceled. if i click "END", it takes me back to the
form;
then i try to close it again, and the process repeats again. if i select
DEBUG; it takes me to the VBA code and highlisted my new line of code.

when i close the VBA window; got a warning that says "this comand will
stop
the debugger"; i click ok; it closes the VBA window and takes me back to
my
table.

when i open my table; i got an empty row of record.

what do i need to do to prevent it from adding a new row when i close the
form?

many thanks.


Michel Walsh said:
The ill form name of the table is still part of the problem. Try adding
the
[ ] around it, this time, near the keyword INTO, as in:



Private Sub IsGroup_Exit(Cancel As Integer)
DoCmd.RunSQL "INSERT INTO [tbl Data Log2] (GroupCount) VALUES (ABS(
DSUM(
'IsGroup', '[tbl Data Log2]' ) ) )"
End Sub


That should append a new record into the said table, as long as all the
other fields in the table either allow nulls, either have a default value
(or are autonumber), and that no data integrity rule is violated (such as
not having duplicated value, etc.)

If there is more than ONE field to be specified, use list of names, and
list of values:


DoCmd.RunSQL
"INSERT INTO [tbl Data Log2] (GroupCount , GroupName)
VALUES (ABS( DSUM( 'IsGroup', '[tbl Data Log2]' ) ) ,
FORMS!FormName!ControlWithTheGroupNameToBeSaved )"



will still append one new record, this time with GroupCount field =
ABS(SUM(... ) and GroupName field = the value in the said control
for
the said form

as example. If you add a third field in the list of fields, be sure to
add a
third value in the list of values.




Vanderghast, Access MVP


Barbara said:
ok. since i have to have the values to be use later; i add an "On Exit
Event
Procedure" for the checkbox "IsGroup" as follow:


Private Sub IsGroup_Exit(Cancel As Integer)
DoCmd.RunSQL "INSERT INTO tbl Data Log2 (GroupCount) VALUES (ABS( DSUM(
'IsGroup', '[tbl Data Log2]' ) ) )"
End Sub

Got the following error msg:

got Run-Time Error '3134': Syantax error in INSERT INTO statement

What i am trying to accomplished is to count the number of order that
has
the "IsGroup" check and so when i print label for the orders; i can
print
only those group and write it as '1 of 17', '2 of 17' etc. please
help!

thanks

:

If you assign it as control source in the property sheet rather than
in
VBA
code, use only

=abs(dsum("fieldname", "tablename"))


and use [ ] around ill formed name (those with space in them, as
example):


=ABS(DSUM("IsGrouped ","[tbl Date Log2]")



To use an insert query, you will have to use VBA code (as a procedure
answering the Click event of a button, as example), and it is more
involved
than just using a control property. NOTE though that assigning the
control
property to a computed expression (like here, with:
=abs(dsum("fieldname", "tablename")) ) makes that control NOT
bound
to
any field of a table, and thus, won't save the result. It will only
SHOWS
it, and always compute it, so it will always UP TO DATE, which is
generally
preferable to 'computation saved into a table', which may NOT be so
perfectly synchronized with what is in the tables.


Vanderghast, Access MVP



i am sorry, but when i use the "me.countrolname=abs(dsum("fieldname",
"tablename")), it give me this: #name?; so when i take off the
"me.controlname"; it shows me the total count.

click here for a printscreen of my form:
http://www.hosetech.com/control-source.jpg

As for the "INSERT QUERY"; where can/do I add such line of code? In
the
Form design view or in a query view? I also have a query just for
getting
the total of IsGroup; if it's adding the line of code in the query
view;
where?

thank you for being patient, I am a newbie! :)



:

You should be able, in a form, to set a control bound to a field,
in
the
table you want to save the value. Set the control to the
expression:

Me.ControlName = ABS( DSUM( "fieldName", "tableName" ) )

as a line of VBA code, as example.


You can also use an INSERT query:


DoCmd.RunSQL "INSERT INTO tableNameToInsertNewRow
(fieldNameGettingTheValue) VALUES (ABS( DSUM( 'fieldName',
'tableName' ) ) )
"


as example, append one new record to table tableNameToInserNewRow,
putting
the desired total in the field fieldNameGettingTheValue.





Vanderghast, Access MVP


If i want to store the value to the table, how do i do that? I
need
to
use
it later in a report. All orders with the check on value will be
group
together.

:

Under a form you can use

ABS( DSUM( "fieldName", "tableName" ) )

to compute the number of time the said field has a check value
set
on,
for
the given table.


Hoping it may help,
Vanderghast, Access MVP
\
I found this function: =Abs(Sum([FieldName])) and it did return
the
number
as
i wanted; however, it will only work when put it in a Report
Label.
I
would
like to store this total in a query so i can use it later as
'1
of
10'
in
a
label. Right now, it just have the total number on the label.

Here's what I am trying to accomplish is this:

I am counting the number of orders where IsGroup is checked on
the
form
based on StartTestNo and EndTestNo (prompt user to enter it).
I
would
like
to use the Total number and write it as "1 of 10", "2 of 10"
if
and
only
if
IsGroup is checked.

thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top