Good day to all: I found your website a couple of days ago as I was searching for resources that can help within my particular problem in writing a script for an Access Query. The issue is to calculate a month and year situation. What I need is to create a query that will pull up how many months and years an employee has been working at a (now) date. For example: Employee A as been employed 2 years and 4 months and Employee B as been employed 7 months. I've found a good script for the year calculation, but for the script for the months is what I have trouble writing. What I am getting is these types of results 60 months = 5 years or 1 and 0 which I am not looking for. I am really a novice in writing scripts and I will really appreciate all the help I can get. Thanks MCLR
4  6906   MMcCarthy 14,534
Recognized Expert Moderator MVP
Good day to all: I found your website a couple of days ago as I was searching for resources that can help within my particular problem in writing a script for an Access Query. The issue is to calculate a month and year situation. What I need is to create a query that will pull up how many months and years an employee has been working at a (now) date. For example: Employee A as been employed 2 years and 4 months and Employee B as been employed 7 months. I've found a good script for the year calculation, but for the script for the months is what I have trouble writing. What I am getting is these types of results 60 months = 5 years or 1 and 0 which I am not looking for. I am really a novice in writing scripts and I will really appreciate all the help I can get. Thanks MCLR
I've had to guess at the field and table names but the following should work for you. -
SELECT EmployeeID, EmployeeName, Int(DateDiff("m",[StartDate],Now())/12) AS Years, DateDiff("m",[StartDate],Now()) Mod 12 AS Months
-
FROM Employees;
-
I've had to guess at the field and table names but the following should work for you.
-
SELECT EmployeeID, EmployeeName, Int(DateDiff("m",[StartDate],Now())/12) AS Years, DateDiff("m",[StartDate],Now()) Mod 12 AS Months
-
FROM Employees;
-
I just tested the script on the query that you gave me and it does work!!! I really want to thank you for all your help and to let you know that with help like this, I do learn a lot about script writing. Again thanks, MCLR
MMcCarthy 14,534
Recognized Expert Moderator MVP
I just tested the script on the query that you gave me and it does work!!! I really want to thank you for all your help and to let you know that with help like this, I do learn a lot about script writing. Again thanks, MCLR
You're welcome.
That is absolutely Brilliant. Much shorter than the script I've been using.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: rong.guo |
last post by:
Hi Group!
I am struggling with a problem of giving a date range given the start
date.
Here is my example, I would need to get all the accounts opened between
each month end and the first 5 days of the next month. For example, in
the table created below, I would need accounts opened between
'5/31/2005' and '6/05/2005'. And my query is not working. Can anyone
help me out? Thanks a lot!
|
by: BlackFireNova |
last post by:
Using Access 2002
I am writing a report which draws data from several different tables. I
can't link all the tables in a query, as some can not be related without
truncating the data. I plan to use sub-queries and sub-reports to
filter and display the data in the unrelated tables in my report. The
common information is a user-inputed date range.
I want to avoid having the user prompted for the and
variables repeatedly. Somehow I...
|
by: Brian Jorgenson |
last post by:
I am looking for a formula to put in my query to pull data based on
the last 3 months. It starts with the current day and will go back 3
months.
Here is my wrkflow language for example:
CURRENT_DATE = current date
PAST_DATE = CURRENT_DATE - 3 months
|
by: Terry |
last post by:
I have a database that I would like to query for a specific date
range.
I want to create a report based on this query that will pull any
records from the last 7 days.
Is this possible? Can someone tell me how it is done?
Thanks.
|
by: Tony |
last post by:
Hello everyone,
Okay so here is my pickle! I have a field called ARCH_DATE within a
table which has a date stored like "9/1/2004 23:50:00". What I would
like to do is create a date range which prompts a user to ONLY to
enter a format like "mm/dd/yy". I am not interested in the time
component, I really am interested in the date. I have exhuasted my
resources on this one, any help would be greatyl appreciated.
Tony
| | |
by: carl.barrett |
last post by:
Hi,
I have a continuous form based on a query ( I will also be creating a
report based on the same query).
There are 2 fields:
Date Obtained and Date Of Expiry
I want a further 3 columns to the right of these 2 fields to show the
|
by: rohayre |
last post by:
Im a long time java developer and actually have never done anything
with java scripting. I'd like to write a short simple script for
calculating a date in the future based on today's date and a letter.
Can I use javascripting to create a webpage to allow a user to enter a
letter and then click a button to find a future calendar date? I'm just
not sure how much user interaction scripting allows. Does java
scripting allow buttons, textfields...
|
by: geek491 |
last post by:
I want an SQL that displays all the days within the range given.
SELECT <days> from <dual> where days between '01/01/2006' and '12/12/2006'
Dual table is precent in Oracle but what is its equivalent in ACCESS?
If i run the above query with a different date range i should get all the days between those two date ranges.
|
by: Hugh Middity2 |
last post by:
Hello, We have an access report based on a query in which a date range is entered. Is there a way of keeping the date range to print on the report? Thanks
|
by: ruvi |
last post by:
Hi,
I am using Vb 6 and Access.
I am getting syntax error in the following sql query.
strSql = "SELECT tblInvoice.ProjID,tblInvoice.CusID,tblInvoice.InvoiceNo,tblInvoice.InvDate,tblInvoice.Total,tblInvoice.Paid,tblInvoice.Balance,tblCustomer.CustomerName FROM tblCustomer RIGHT JOIN tblInvoice ON tblCustomer.CusID=tblInvoice.CusID WHERE tblInvoice.InvDate >= #" &sDate& "# AND tblInvoice.InvDate <= #" &eDate& "# AND...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
