DCount Code Problem ?

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

Trying to calculate how many records have employees with a count greater
than 2 that worked on the same day.
That is any record that has more than 2 employees on the same day...
This is a single form with record selectors

=DCount("[EmployeeID]","Employees","[Work Date] = " & [WorkDate])
 
Dave Elliott said:
Trying to calculate how many records have employees with a count greater
than 2 that worked on the same day.
That is any record that has more than 2 employees on the same day...
This is a single form with record selectors

=DCount("[EmployeeID]","Employees","[Work Date] = " & [WorkDate])
Click to expand...

Are you getting an error message or the wrong count or what?

If an error message, you could try:

=DCount("[EmployeeID]","Employees","[Work Date] = #" & [WorkDate] & "#")

Tom Lake
 
No error message, just a zero

Tom Lake said:
Dave Elliott said:
Trying to calculate how many records have employees with a count greater
than 2 that worked on the same day.
That is any record that has more than 2 employees on the same day...
This is a single form with record selectors

=DCount("[EmployeeID]","Employees","[Work Date] = " & [WorkDate])
Click to expand...

Are you getting an error message or the wrong count or what?

If an error message, you could try:

=DCount("[EmployeeID]","Employees","[Work Date] = #" & [WorkDate] & "#")

Tom Lake
Click to expand...
 
Now getting error with your new code #Name
The Name of the field is WorkDate and the control is Work Date
Take note of the spaces





Tom Lake said:
Dave Elliott said:
Trying to calculate how many records have employees with a count greater
than 2 that worked on the same day.
That is any record that has more than 2 employees on the same day...
This is a single form with record selectors

=DCount("[EmployeeID]","Employees","[Work Date] = " & [WorkDate])
Click to expand...

Are you getting an error message or the wrong count or what?

If an error message, you could try:

=DCount("[EmployeeID]","Employees","[Work Date] = #" & [WorkDate] & "#")

Tom Lake
Click to expand...
 
=DCount( "[EmployeeID]", "Employees",
"[Work Date] = " & Format([Work Date], "\#mm/dd/yyyy\#") )


--
HTH
Van T. Dinh
MVP (Access)


Dave Elliott said:
Now getting error with your new code #Name
The Name of the field is WorkDate and the control is Work Date
Take note of the spaces





Tom Lake said:
Dave Elliott said:
Trying to calculate how many records have employees with a count greater
than 2 that worked on the same day.
That is any record that has more than 2 employees on the same day...
This is a single form with record selectors

=DCount("[EmployeeID]","Employees","[Work Date] = " & [WorkDate])
Click to expand...

Are you getting an error message or the wrong count or what?

If an error message, you could try:

=DCount("[EmployeeID]","Employees","[Work Date] = #" & [WorkDate] & "#")

Tom Lake
Click to expand...
Click to expand...
 
Further Explanation: On the form with record selectors I have
1 record , Forest Gump on Wednesday 03/16/05
3 records. Forest Gump Thursday 04/21/05; Bubba Gump Thursday 04/21/05;Bubba
Gump Thursday 04/21/05

Total of 4 records.

I need the count of employees on the day Thursday or ANY DAY that has more
than 3 employees on it
 
What do you want to do if you have 2 or more days with more than 3 employees
in each of these dates?
 
I need the count of employees for all the days that has more than 2
employees on it
The formula will wind up being for each employee after 2 the rate is 1/2 of
the pay rate which the default is 65, but whatever rate is in the rate
textbox, then the formula will need to adjust for this.
Example: Wednesday has 3 employees and rate is 65, then the first 2 people
are billed at 65 an hour and the 3rd man is billed at 32.50 for each hour he
works.
Count the TOTAL of men greater than 2 for each day.
 
Where are you using the Dcount()?

Also, DCount() only returns a *single* value but you want to know the number
of employees (>2) for different days, i.e. a set of values??? (which is not
possible with DCount()). It sounds to me a Query is more appropriate than
DCount().

Post you Table structure also. So far, you had 3 threads on the same
question but you haven't given much info about your Table Structure.
 
Table Structure: 2 tables used for form; Hours and Employees
From the Hours table these fields;HoursID-TimeID-Hours-Overtime-Work Date-OT
Rate-Pay Rate-EmployeeID
From the Employees table; EmployeeID

TimeID links the sub-form (Time and Hours) to the main form TimeCards
EmployeeID links the 2 tables and TimeID links the sub-form to the main
form. (TimeCards) Master/Child
The sub-form gives exactly what I need, all the data is there, just cant see
it the way i want

I need to get the number of records that have more than 2 employees on them
and the count of the employees
I then would have to subtract the total number of records minus the ones
that have more than 2 employees on them for my calculation. I can use
another form to do this and base it on another query but dont know how to
structure it.
Remember for 1 or 2 employees the rate is 65, then after for each employee
1/2 the rate per hour
Same thing with 55 rate.
 
Sorry but I am not getting the picture of your set-up. Post the following:

1. What is the purpose of the database? What is the purpose of the Form /
Subform?

1. The RecordSource of the Main Form ("TimeCards"). This is important as I
got no ideas what the Form is supposed to do and hence can't place your
DCount or your requirement correctly. Since the Subform already "uses" the
2 Tables (?) you described, I got no ideas of the data source for the main
Form.

2. The RecordSource of the Subform ("Time and Hours")

3. The LinkMasterFields and LinkChildField of the Subform Control.

4. In one of the earlier, you posted an example with 4 Records (3 on the
same date and 1 on a different date) and you mentioned "On the Form with
record selector", I guess you actually meant the Subform?

5. From the same sample data, I guess you want the count of days with 1 or 2
employees AND the count of days with more than 2 employees that *belong/ are
related* to the CurrentRecord of whatever on the main Form (RecordSource /
entity unknown - see point 1) so that you can work out the total hourly rate
applicable for each day?

6. What happens if 2 or more employees work on the same date on the same
CurrentRecord on the main Form but they work different number of hours at
different time? Let's say 3 employees but Employee1 works full day,
Employee2 work 1/2 day in the morning and Employee 3 works 1/4 day in the
afternoon (hence the nett workforce is less than 2)? How would you
calculate the hourly rate in this case?
 
The Db is a Invoice db. it calculates the material, labor and the suggested
price to Invoice.
The main form TimeCards has as it's recordsource the query QDates using the
table Time
The sub-form named Time and Hours uses the query Time_Hours with 3 tables
Hours;Employees;TEmpOrCon
Sub-form is tied to main via master/child relationship of TimeID in the Time
table
Sub-form Time and Hours calculates the labor by Rate/Hours Total pay for the
day and grand total pay for the record
as well as overtime rate/ overtime pay...it shows the day worked by date and
also by Day, i.e. Friday
The calculations are already in place for the time / hours, this works fine.

Just need to calculate if more than 2 employees work on any given record on
the sub-form that is one record via the main form.
Example: 3 employees work on Friday, then the rate would be $65.00 an hour
for the first 2 men, then 32.50 or 1/2 the rate for every employee after
that.
Total Invoiced for hours being (4) for all employees: $390.00
So the count of DAYS (On The Same Day) where the count of EMPLOYEES
is over 3 needs to be found for the calculation to take place.
Hope this explains better.
 
OK, much better picture in my head but please post the SQL String (of the
Query) being used as the RecordSource for the Subform [Time and Hours].

Also advise where you intend to use / place the result of the DCount().
 
SELECT Hours.SinMar, Hours.NoAllow, Hours.PayRate, Hours.Hours,
IIf([TEmpOrCon].[EmpOrCon]=0.2,1*[PayRate],[Hours]*[PayRate]) AS RegPay,
Hours.OTRate, Hours.Overtime, [Hours].[OTRate]*[Overtime]*[PayRate] AS
OTPay, [RegPay]+[OTPay] AS ExpPayPerDay, Hours.TimeID, Hours.HoursID,
Hours.EmployeeID, Employees.[First Name], Hours.[Work Date], Employees.[Last
Name], Hours.ChkNoID, IIf(IsNull([ChkNoID]),0,-1) AS Paid
FROM (Hours LEFT JOIN Employees ON Hours.EmployeeID = Employees.[Employee
ID]) LEFT JOIN TEmpOrCon ON Employees.EmpOrCon = TEmpOrCon.EmpOrConID
ORDER BY Hours.[Work Date] DESC , Employees.[Last Name];

Probably will have to put on either Time and Hours or footer on same and
then
The rest of the code will be on the main form TimeCards using a unbound
textbox using the values
of the Time and Hours form
I can send a small demo db if necessary.


Van T. Dinh said:
OK, much better picture in my head but please post the SQL String (of the
Query) being used as the RecordSource for the Subform [Time and Hours].

Also advise where you intend to use / place the result of the DCount().

--
HTH
Van T. Dinh
MVP (Access)


Dave Elliott said:
The Db is a Invoice db. it calculates the material, labor and the suggested
price to Invoice.
The main form TimeCards has as it's recordsource the query QDates using the
table Time
The sub-form named Time and Hours uses the query Time_Hours with 3 tables
Hours;Employees;TEmpOrCon
Sub-form is tied to main via master/child relationship of TimeID in the Time
table
Sub-form Time and Hours calculates the labor by Rate/Hours Total pay for the
day and grand total pay for the record
as well as overtime rate/ overtime pay...it shows the day worked by date and
also by Day, i.e. Friday
The calculations are already in place for the time / hours, this works fine.

Just need to calculate if more than 2 employees work on any given record on
the sub-form that is one record via the main form.
Example: 3 employees work on Friday, then the rate would be $65.00 an
hour
for the first 2 men, then 32.50 or 1/2 the rate for every employee after
that.
Total Invoiced for hours being (4) for all employees: $390.00
So the count of DAYS (On The Same Day) where the count of
EMPLOYEES
is over 3 needs to be found for the calculation to take place.
Hope this explains better.
Click to expand...
Click to expand...
 
The db is a invoice database. purpose of timecards form is to record allinfo
for a invoice, such as employee time, vendor material, payments to invoice,
employee commission. date of invoice and how many times sent and by who,
history of payments, count how many employees worked on what job, what date
and their hours, this used to calculate the expenes for the job, i.e.
timecard as well as material used so invoice price can be calculated.
Time is not calculated on this form, only days, dates. The ONLY thin I need
help with is the sub-form Time And Hours on the main form TimeCards, thats
it.
as for the rocordsource, this is easily found via the forms in design mode.
the whole timecard is one (1) record.
datasource for main form is query QDates, but this does no have anything to
do with problem at hand.
recordsource for sub-form time and hours is a query named Time_Hours.
Call me if you have any questions or else e-mail you number and I will call
you.
The database is very complex, but we are ONLY dealing with one form, the
sub-form, Time and Hours
the data is there but I need to see it in a different way.

Thanks,

Dave
 
Dave

I got you message enquiring whether I got your file (which I didn't) and
when I replied, the e-mail bounced back undeliverable.

--
HTH
Van T. Dinh
MVP (Access)
 
Re-sent this morning. 05/16/ @ 10:20am

Van T. Dinh said:
Dave

I got you message enquiring whether I got your file (which I didn't) and
when I replied, the e-mail bounced back undeliverable.
Click to expand...
 
Back
Top