Calculated text box

  • Thread starter Thread starter mon
  • Start date Start date
M

mon

Hi, I have a list of records with field: item numbers and
field: hours. I can sum the total hours, but as well I
would like to have the sum of hours for just one of the
item numbers (ie item7) and a sum of the hours for all the
rest of the hours (ie not item7). I've tried queries and
the expression builder, but can't seem to make anything
work. Help. mon
 
You could use DSum statements in the Control Source of the textboxes.

Examples:
=DSum("[HoursField]", "TableName", "[ItemField]=7")
=DSum("[HoursField]", "TableName", "[ItemField]<>7")

The first one will total the hours for item 7 and the second one will total
the hours for all the items except 7.
 
Thanks Wayne, I am getting closer. This will indeed
seperate the total of the hours, but only for the entire
table. Now I would like to total the hours in a report
which is based on a select query where the criteria is
jobnumber? So basically the total hours will be seperated
to total hours/job and travel and not-travel per job. (At
the moment every field in those columns show the same
number ie the total for the whole table. Thanks Again, mon
-----Original Message-----
You could use DSum statements in the Control Source of the textboxes.

Examples:
=DSum("[HoursField]", "TableName", "[ItemField]=7")
=DSum("[HoursField]", "TableName", "[ItemField]<>7")

The first one will total the hours for item 7 and the second one will total
the hours for all the items except 7.

--
Wayne Morgan
Microsoft Access MVP


Hi, I have a list of records with field: item numbers and
field: hours. I can sum the total hours, but as well I
would like to have the sum of hours for just one of the
item numbers (ie item7) and a sum of the hours for all the
rest of the hours (ie not item7). I've tried queries and
the expression builder, but can't seem to make anything
work. Help. mon
Click to expand...


.
Click to expand...
 
sorry I meant parameter query not criteria
-----Original Message-----
Thanks Wayne, I am getting closer. This will indeed
seperate the total of the hours, but only for the entire
table. Now I would like to total the hours in a report
which is based on a select query where the criteria is
jobnumber? So basically the total hours will be seperated
to total hours/job and travel and not-travel per job. (At
the moment every field in those columns show the same
number ie the total for the whole table. Thanks Again, mon
-----Original Message-----
You could use DSum statements in the Control Source of the textboxes.

Examples:
=DSum("[HoursField]", "TableName", "[ItemField]=7")
=DSum("[HoursField]", "TableName", "[ItemField]<>7")

The first one will total the hours for item 7 and the second one will total
the hours for all the items except 7.

--
Wayne Morgan
Microsoft Access MVP


Hi, I have a list of records with field: item numbers and
field: hours. I can sum the total hours, but as well I
would like to have the sum of hours for just one of the
item numbers (ie item7) and a sum of the hours for all the
rest of the hours (ie not item7). I've tried queries and
the expression builder, but can't seem to make anything
work. Help. mon
Click to expand...


.
Click to expand...
.
Click to expand...
 
You can use more than one criteria in the DSum statement.

Example:
=DSum("[HoursField]", "TableName", "[ItemField]=7 And [IDField]=3")

Also, you can run DSum on a query as well as a table. Just put the query's
name in for TableName.
 
Back
Top