如何在文本框中计算员工的连续休假日期？ [英] How to count consecutive vacation dates for employees in text box?

问题描述

大家好，


我开始在VBA中找到一个相当不错的基础，但我无法解决这个问题，即使在网络搜索中徘徊通过其他似乎远程相关的帖子。所以我希望一些（总是）令人敬畏和有帮助的Bytes脑力可以被抛到我的路上。


如果它甚至可能，我希望能够算上假期我的员工连续服用的日期（无论是假期还是周末都没有关系，所有这些日子都被列为假期并连续服用）。


在一个名为[frmVacations]的表格中，我有一个名为[tblVacationsubform]的子表单，它允许我输入并显示为每位员工拍摄或安排的休假日期。子表单中的相关字段标题为[EmployeeName]，[VacationType]，我可以从中选择v / d（休假日）d / d（自行决定日）s / o（预定休息日）等等。我只关心计数？v / d？。最后我当然有一个领域[VacationDate]。子表单从标题为[tblVacations]的表中提取信息。


这样做的原因是，例如，如果员工与公司有10年的合作时间他/她获得4周的假期。这些周中的2周可以分开并在间歇性的个别日子中进行，但另外2周需要以固体块（即连续5天）一起服用。所以这是需要的，以便更好地跟踪并确保员工不会意外地分散比允许更多的休假周。


我已经有一个文本框能够梳理标题为[tblVacations vac query]的查询并计算多少？v / d？是为每个员工，但我希望有一个文本框（我已经创建并标题为[txtConsecDays]）能够查看表/记录集或查询并计算在5个块中连续采取的天数我的最终目标是有一个文本框反映？1？ （周）如果一名员工连续一起服用5天，那么？2？ （星期），如果他们在那之后又一起又花了5天......这会让事情变得更容易跟踪并避免在为30多名员工安排假期时滑倒。


我的员工每周只工作4天，因此对他们来说度假周显然只有4天，但这是一个问题，如果有人可以帮助我，我会稍后解决这个问题。第一个主要问题。


希望我已经清楚我需要什么，并且包含了如果这个场景甚至可以实现所需的所有信息。我确实试图解决这个问题，但似乎再次出现在圈子里，最终意识到这个问题可能不得不在VBA中完成。与往常一样，我非常感谢任何人都可以帮助的见解和建议。

Hello all,

I?m beginning to get a pretty good foundation in VBA, but I can?t wrap my head around this problem, even after trolling around the net searching through other posts that seemed remotely related. So I am hopeful that some (always) awesome and helpful Bytes brain power can be thrown my way.

If it?s even possible, I would like to be able to count the vacation dates that my employees have taken consecutively (doesn?t matter if they are holidays or on the weekend, just all days listed as vacation and taken consecutively).

In a form titled [frmVacations], I have a subform titled [tblVacationsubform] which allows me to input and show vacation dates taken or scheduled for each employee. The relevant fields in the subform are titled [EmployeeName], [VacationType] from which I can choose v/d (for vacation day) d/d (for discretionary day) s/o (for scheduled off day) etc?. I?m only concerned about counting ?v/d?. And lastly of course I have a field [VacationDate]. The subform is pulling it?s information from a table titled [tblVacations].

The reason for this being that, for example, if an employee has 10 years with the company he/she gets 4 weeks of vacation. 2 of these weeks can be broken up and taken in intermittent individual days, but the other 2 weeks needs to be taken together in solid blocks (i.e 5 days taken consecutively). So this is needed in order to keep better track and make sure the employee isn?t accidently breaking up more vacation weeks than is allowed.

I already have a text box able to comb through a query titled [tblVacations vac query] and count how many ?v/d? are taken for each employee, but I would love to have a text box (which I have created and titled [txtConsecDays] ) be able to look through the table/recordset or query and count how many days are being taken consecutively in blocks of 5. My ultimate goal would be to have a text box reflect ?1? (week) if an employee has taken 5 days together consecutively, and then ?2? (weeks) if they have taken another 5 days together after that, etc... This would make things so much easier to track and avoid slip ups when scheduling vacations for over 30 employees.

I do have employees who only work 4 days a week, and therefore a vacation week for them obviously only consists of a block of 4 days, but this is a problem I would try to tackle later when/ if someone can help me with this first main problem.

Hopefully I?ve been clear in what I need and have included all the information needed if this scenario is even possible to implement. I did try to mess around with queries for this problem but again seemed to be running in circles, and ultimately realized this problem probably had to be done in VBA. As always, I really appreciate any insights and suggestions anyone can help with.

推荐答案

[VacationDate]字段中的数据是如何格式化的？

How is the data in the [VacationDate] field formatted?

感谢您查看这个gnawoncents。


在子窗体拉出的表[tblVacations]中，数据类型[VacationDate]的格式为Date /时间。

Thanks for taking a look at this gnawoncents.

In the table [tblVacations] that the subform is pulling from, the data type [VacationDate] is formatted in is Date/Time.

每条记录只分配一天？换句话说，一系列记录会是这样的：


EmployeeName..VacationType..VacationDate

---------- - ------------ ------------

Doe，John ..... v / d ..... ...... 1月12日10

Doe，John ..... v / d ........... 1月13日

Doe，John ..... v / d ............ 1月14日

Doe，John ..... v / d ...... ..... 1 1月10日

Doe，John ..... v / d ........... 10月16日

史密斯，简...... v / d ........... 1月10日

史密斯，简...... v / d ........... 3月3日10美元
史密斯，简... v / d ........... 2月10日

史密斯，简...... v / d ........... 2月3日

史密斯，简...... v / d ........... 2月10日

史密斯，简... v / d ........... 2月5日10美元

......你会认为John Doe有一周，简史密斯为零？如果这不是您的设置，请提供您的数据示例。

Is only one day assigned to each record? In other words, would a series of records look something like:

EmployeeName..VacationType..VacationDate
------------ ------------ ------------
Doe, John.....v/d...........12 Jan 10
Doe, John.....v/d...........13 Jan 10
Doe, John.....v/d...........14 Jan 10
Doe, John.....v/d...........15 Jan 10
Doe, John.....v/d...........16 Jan 10
Smith, Jane...v/d...........1 Jan 10
Smith, Jane...v/d...........3 Jan 10
Smith, Jane...v/d...........2 Feb 10
Smith, Jane...v/d...........3 Feb 10
Smith, Jane...v/d...........4 Feb 10
Smith, Jane...v/d...........5 Feb 10

...And you would count John Doe as having one week and Jane Smith as zero? If this isn''t your setup, please provide an example of your data.

这篇关于如何在文本框中计算员工的连续休假日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！