Count Dates

[Laudrup]

I have a database that allocates people to jobs, which is based on the
Resource Scheduling database.

I would like to show on a Form the current amount of shifts an
employee has worked, by counting consecutive dates from a given date
(ScheduleDatetxt on the form) That appear within the tables for each
employee.


Relevant tables used in the form
Employees
Employ_Ref (text)
Surname....

ScheduleDetails (Where jobs allocation is saved)
ScheduleDetailsID
ScheduleID
Employ_Ref (text)

Schedule (job date is saved)
ScheduleID
ScheduleDate


Is there anyway I can do this using VB or SQL? Any help would be
greatly appreciated.

 

[Doug Munich]

If you make a form based on your employee table and in the form header
put a textbox to hold the given date then on the body of the form you can
put an unbound text box called say "txtShiftCount" and in the form's On
Current event handler put code like:

Private Sub Form_Current()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str1 As String

Set db = Application.CurrentDb

str1 = "SELECT Employees.Employ_Ref, Schedule.ScheduleDate "
str1 = str1 & "FROM Schedule INNER JOIN (Employees INNER JOIN "
str1 = str1 & "ScheduleDetails ON Employees.Employ_Ref =
ScheduleDetails.Employ_Ref )"
str1 = str1 & "ON Schedule.ScheduleID = ScheduleDetails.ScheduleID "
str1 = str1 & "where Employees.Employ_Ref = " & CStr(txtEmploy_Ref)
str1 = str1 & " and Schedule.ScheduleDate >= #" &
CDate(ScheduleDatetxt ) & "#"

Set rs = db.OpenRecordset(str1)
rs.MoveLast
rs.MoveFirst

txtShiftCount = rs.RecordCount


End Sub

then that should populate the unbound text field with the correct count.
You should add the same code to the after update event handler of the
ScheduleDatetxt textbox and make sure that textbox is formatted as date.

Doug

 

[Laudrup]

Thanks so much for you help, I tried using the code but got the
following error message;

'Run-time Error 3135'
Syntax Error in JOIN operation'

Any ideas as to why im getting this message?

 

[Laudrup]

Please ignore my last post, ive managed to fix that problem. However
I now get the following error message;

'Run-time Error 3464'
'Date type mismatch in criteria experssion.'

Any help would be appricated.

 

[Doug Munich]

The criteria expression is

"where Employees.Employ_Ref = " & CStr(txtEmploy_Ref)
str1 = str1 & " and Schedule.ScheduleDate >= #" &
CDate(ScheduleDatetxt ) & "#"

so I would guess that either [Employees.Employ_Ref] is not a string (text)
type or [Schedule.ScheduleDate] is not a date type. You can try changing
CStr(txtEmploy_Ref) to txtEmploy_Ref or CDate(ScheduleDatetxt )
to ScheduleDatetxt and see if either of those helps.

Doug

 

[Laudrup]

Thanks again....I gave that a try but still get the same error
message.

Employees.Employ_Ref is a string (text) type and Schedule.ScheduleDate
is a date type so i cant see why im getting ''Data type mismatch'


Any further help would again be appricated.

 

[Doug Munich]

Hmm... you could try removing the date criterion from the code and see if
it runs (even though you would get the wrong result). If it still didn't
work then remove both criteria.

If it does work at some point then you can try returning the criterion
you removed but give it a literal value like...

str1 = str1 & " and Schedule.ScheduleDate >= #"12/25/2007#"

and see if that works.

Doug

 

[Laudrup]

I tried again to make the changes but still get the same error
message, I did however manage to figure out the problem is with the
Employ_Ref not the Date. But havnt managed to discover why.