D
Daryl
Hi all
I have a subform that I use for a person to store their days working. This
subform's data is provided by a procedure shown below.SELECT Date, dbo.Weekday(DATEPART(dw, Date), 'd') AS DayOfWeek, StartTime,
FinishTime, LunchInMins, ServiceNO, CASE WHEN ISDATE([Date]) = 1 AND
ISDATE(StartTime) = 1 AND ISDATE(FinishTime) = 1 AND
ISNUMERIC(LunchInMins) = 1 THEN dbo.CalculateWorkingPeriod([Date],
StartTime,
FinishTime, LunchInMins) ELSE '' END AS WorkingPeriod
FROM dbo.tblTimesheet
WHERE (ServiceNO = @Service_NO)
ORDER BY Date
Notes: Weekday(day, style) is a User Defined function to show the day i.e.
'Mon', 'Tue', ...
CalculateWorkingPeriod(Date, StartTime, FinishTime, LunchInMins) calculates
and formats the time worked for that employee on the passed date.
These two pieces of information are NOT stored in the database either the
Jet or the Server versions.
I also have to say that I have this database working in ACCESS using the Jet
and I am converting it to a SQL Server back-end. I would like to have the
same functionality for the user that Jet provides.
The question / problem
With the Jet solution as soon as the user places a date in the [Date] field
on the sub-form the query places in the Weekday i.e. 'Mon' and as soon as
the user steps of the record the WorkingPeriod is shown to the user.
I am having difficulty in duplicating this feature. I can do so by
Refreshing or Requerying the subform's data source but this leads to the
subform being repainted and going to the first record. I can stop this by
using a Bookmark and using Refresh and then going back to the record last
edited or added but this is clumsy and still does not provide the Weekday as
soon as the Date is entered.
Could someone please help me out with this problem?
Any suggestions will be welcome...
thanks
daryl
I have a subform that I use for a person to store their days working. This
subform's data is provided by a procedure shown below.SELECT Date, dbo.Weekday(DATEPART(dw, Date), 'd') AS DayOfWeek, StartTime,
FinishTime, LunchInMins, ServiceNO, CASE WHEN ISDATE([Date]) = 1 AND
ISDATE(StartTime) = 1 AND ISDATE(FinishTime) = 1 AND
ISNUMERIC(LunchInMins) = 1 THEN dbo.CalculateWorkingPeriod([Date],
StartTime,
FinishTime, LunchInMins) ELSE '' END AS WorkingPeriod
FROM dbo.tblTimesheet
WHERE (ServiceNO = @Service_NO)
ORDER BY Date
Notes: Weekday(day, style) is a User Defined function to show the day i.e.
'Mon', 'Tue', ...
CalculateWorkingPeriod(Date, StartTime, FinishTime, LunchInMins) calculates
and formats the time worked for that employee on the passed date.
These two pieces of information are NOT stored in the database either the
Jet or the Server versions.
I also have to say that I have this database working in ACCESS using the Jet
and I am converting it to a SQL Server back-end. I would like to have the
same functionality for the user that Jet provides.
The question / problem
With the Jet solution as soon as the user places a date in the [Date] field
on the sub-form the query places in the Weekday i.e. 'Mon' and as soon as
the user steps of the record the WorkingPeriod is shown to the user.
I am having difficulty in duplicating this feature. I can do so by
Refreshing or Requerying the subform's data source but this leads to the
subform being repainted and going to the first record. I can stop this by
using a Bookmark and using Refresh and then going back to the record last
edited or added but this is clumsy and still does not provide the Weekday as
soon as the Date is entered.
Could someone please help me out with this problem?
Any suggestions will be welcome...
thanks
daryl