Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Calculate Employment Years In Query?


I'm trying to calculate the amount of years employed in a query and I keep getting "Data Type Mismatch in criteria expression".

I'm putting the expression in the YearsOfService Field that I've created that I would for the total in years to populate.

Here's the field expression:
DateDiff("yyyy",[EmpHireDate],Date())+(Date()<DateSerial(Year(Date()),Month([EmpHireDate]),Day([EmpHireDate])))

Thanks for any help...

FYI EmpHireDate is a Date/Time Field and YearsOfService is a Number field

Thanks for any help...


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Calculate Age In Years And Months
I need a formula or a function that will return a person's age in years and months. All the functions and formulas that I have seen to date and that I have used only calculate a person's age in years. Can anyone help?

View Replies !   View Related
Years Query
Hi,

I would like to be able to run a query that lists the number of years members have been subscribing (up to the current year).
i.e. to show the year he joined, and how many years he has been a member (shown as a number).

Is there a way to do this easily?

thanks

Adrian

View Replies !   View Related
DESPERATELY In Need Of Help On Query Last 2 Years
Hi,

How do I query the last 2 years vs. current date?

Basically, I want to see the record from today date back to 2 years.

Please assist...

Thanks,
Isabel

View Replies !   View Related
Anyone Looking For Employment In Raleigh, NC
A previous employer came to me for help. He says their access developer has resigned and wanted to find out where he could locate a replacement. I thought I'd start here and see if anyone in this area is in the market.

View Replies !   View Related
My Query Stopped Working After 3 Years???
I have a burron to run a query that auto populates 'start time' with NOW().
It has worked for 3 years and now I get an error message see attached. The only thing I did was change the program fromm 2000 to 2002.

any and all suggestions welcome.

Thanks

View Replies !   View Related
Query Weeks Over Multiple Years
I have a database that I am using to report metrics based on seasonally available data. What I am running into is the problem that the seasonally entered data doesn't start on the same date every year.

An example:
I have data that was entered into my database last year starting July 26, 2004. This is considered part of week 1 of my season and I want to compare that data to records that started being entered on August 15, 2005. Which is week 1 of the 2005 season.

Records include the following information: id, date, other irrelevant details

Does anyone have any ideas on how I might be able to accomplish this? Thanks in advance for your help!

View Replies !   View Related
Inner Join Mutiple Years With 1 Query.
Is there away that I could have 1 select query capture the information I need for multiple years as opposed to having to create a seperate query for each year? Here is my code:

SELECT [Application Data - NE].Year, Julian.Calendar04, [Application Data - NE].CCN
FROM Julian INNER JOIN [Application Data - NE] ON (Julian.Julian = [Application Data - NE].Julian) AND (Julian.Year04 = [Application Data - NE].Year)
WHERE ((([Application Data - NE].Year)="04"));

View Replies !   View Related
Calculate Median In Query
Hello:

I have read the Microsoft article at http://support.microsoft.com/kb/q95918/ several times. However, I am looking for an expression I can place in the field name to calcluate the median of another field in the same query. Is this possible? If not, I don't understand how to call the function created in the Microsoft article.

Example (fields in the query)
Expr1
Expr2:Median(Expr1)

Thanks in advance.

View Replies !   View Related
Query To Calculate Busiest Day
i would like to run a query that works out the busiest day in a month but i also want to run a range from two dates and be able to convert them to

mon ? entries
tue ? entries

and so on

is this possible?

many thanks

michelle

View Replies !   View Related
How To Calculate Field In Query
Hi Friends,

I have made one form based on query. Its a invoice entry form. I have price, quantity and amount. I want when i enter price and quantity, amount should be calculated. I know its very simple for you. Any suggestion.

thanks

mithani

View Replies !   View Related
Calculate Balance Query
Hi All.
I have Request, Device and Receive table. All table has DeviceID. I create query to calculate total amount of each type of devices which was requested by link Request and Device tables. And query to calculate total amount of each type of devices which was received by link Receive and Device tables. I would like to create Balance query to calculate:
Total_Receive - Total_Request = Balance
If devices in Request and Receive table is the same result is correct. But if in Receive table add new type of devices I cannot see amount of new device in Balance query. How to fix that problem?
Thanks.

View Replies !   View Related
Calculate Yes/No Fields In A Query
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return? If so, can you share how this could be accomplished. Thanks

View Replies !   View Related
Calculate Median Using Access Query
How can i calculate median using access query. Is there something as simple as aggregate functions like SUM, AVG etc for calculating Median? Please help.

Thanks

View Replies !   View Related
Calculate Query's Field With Two Result
Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.

View Replies !   View Related
Query To Calculate Conditional Field
Dear all,

I have a query that is based on orders table and contains "Qty", 'UnitCost', and "ctualUnitCost", in addition to yes/no control to indicate if "SalesTax" is applicable.

I want an expression in the query to calculate total cost in one of 2 conditions:
if no sales tax then TotalCost = Qty*UnitCost
if sales tax is applicable then Total cost = Qty*UnitCost*1.1

how can I write this expression

thanks

View Replies !   View Related
How To Calculate Cumulative Values Of This Query Fields?
Hi everybody. I got a access 2000 query that lists :

1)weekno
2)year
3)project (project number )
4)QweekylyReportHeader (project description )
5)customer (customer that requested this project)
6)department (department number and name that implements this project)
7)Projectleader ( project leader name and number that is responsible for this project)
8)Task (Task number that is done for this project )
9)task description (description of task )
10)employee ( employee number who is working in this project )
11)name (Employee name and initial and last that works for this project )


12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this
13)salary (amount of salary given to this employee) ===>i want cumulative for this


I want to create another query that lists :

A)cumulative value of hours worked on particular project task up that point.
b)cumulative value for wages given for that project task up that point.

http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample)

The above query ONLY lists hours worked and wages gives for particular project task only during
each week.But i want hours worked and wages give for particle project task up to that point in week. For
example a project task might have implemented last week but not this so i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular
project task.



Notes:


- There is a possibility that during a particular week no task been implement for particular project.
- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report

http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database)
http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view)


query that display hourly wages of certain project during each week


SELECT
querythisweek.weekno,
querythisweek.Year,
querythisweek.Project,
QweeklyReportHeader.Customer,
QweeklyReportHeader.Department,
QweeklyReportHeader.description,
QweeklyReportHeader.ProjectLeader,
querythisweek.Task,
dbo_Task.description,
querythisweek.Employee,
[lastname] & ' ' & [initials] & ' ' & [insertion] AS Name,
querythisweek.hours,
querythisweek.Salary
FROM
dbo_Task
INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;




code for querythis week( calcualte the salary and hours worked)





SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours
from dbo_Hourly_wages a
where dbo_Hours_worked.Employee = a.Employee
and dbo_Hours_worked.Project = a.Project
and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno)
from dbo_Hourly_wages b
where b.Year < dbo_Hours_worked.Year
or (b.Year = dbo_Hours_worked.Year and b.weekno <= dbo_Hours_worked.weekno))) AS Salary
FROM dbo_Hours_worked;

View Replies !   View Related
Need Query To Calculate On Time Deliveries As A Percent
Hello:

This is for the purchasing guy at my client's office. He has 9 vendors he wants to track their on time delivery performance. The data comes from the purchase order tables in his accounting software, to which I've linked via ODBC.

So far so good. I've created a query to pull only those 9 vendors from his total population, then ask for the date range to query by date for the date range (start and end date), and use an expression to calculate the difference between the date wanted and date received so he can tell if the delivery was on time or not. (ex: date wanted: 3/10/06 date received 3/13/06. 3 days difference = late delivery)

The twist is he wants another field so he can mark whether he considers the delivery late or not depending on extenuating circumstances he keeps locked up in his cranium, despite what the date difference results tell him. So, I created a combo box and he can select "Yes" or "No." If he selects YES, a value of 1 is written to the table in that field, if he selects NO, a value of 2 is written to the same field.

So, I need the query to group by vendor, count the total number of entries for that month by that vendor, count the number of "1"s and express that total as a percent of total deliveries.

To summarize, vendor A had 8 deliveries, 5 were on time, meaning he had a 62.5% delivery score. Vendor B had 10 deliveries, 7 on time, meaning a 70% delivery score.

Sorry for the long winded explanation, but perhaps someone can assist. All help appreciated.

View Replies !   View Related
How To Calculate Cumulative Values Of This Query Fields?
Hi everybody. I got a access 2000 query that lists :

1)weekno
2)year
3)project (project number )
4)QweekylyReportHeader (project description )
5)customer (customer that requested this project)
6)department (department number and name that implements this project)
7)Projectleader ( project leader name and number that is responsible for this project)
8)Task (Task number that is done for this project )
9)task description (description of task )
10)employee ( employee number who is working in this project )
11)name (Employee name and initial and last that works for this project )


12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this
13)salary (amount of salary given to this employee) ===>i want cumulative for this


I want to create another query that lists :

A)cumulative value of hours worked on particular project task up that point.
b)cumulative value for wages given for that project task up that point.

http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample)

The above query ONLY lists hours worked and wages gives for particular project task only during
each week.But i want hours worked and wages give for particle project task up to that point in week. For
example a project task might have implemented last week but not this so i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular
project task.



Notes:


- There is a possibility that during a particular week no task been implement for particular project.
- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report

http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database)
http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view)


query that display hourly wages of certain project during each week
Code:SELECT querythisweek.weekno, querythisweek.Year, querythisweek.Project, QweeklyReportHeader.Customer, QweeklyReportHeader.Department, QweeklyReportHeader.description, QweeklyReportHeader.ProjectLeader, querythisweek.Task, dbo_Task.description, querythisweek.Employee, [lastname] & ' ' & [initials] & ' ' & [insertion] AS Name, querythisweek.hours, querythisweek.SalaryFROM dbo_Task INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;

code for querythis week( calcualte the salary and hours worked)

Code:SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours from dbo_Hourly_wages a where dbo_Hours_worked.Employee = a.Employee and dbo_Hours_worked.Project = a.Project and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno) from dbo_Hourly_wages b where b.Year < dbo_Hours_worked.Year or (b.Year = dbo_Hours_worked.Year and b.weekno <= dbo_Hours_worked.weekno))) AS SalaryFROM dbo_Hours_worked;

View Replies !   View Related
Add Months And Years
Hello mates, :cool:

I'm a bit confused with date codes. What I'm try to complish here is that how to add years and months from separate fields to text box, where's difference between two dates as an years and months? So basicly first I read year and month values from text boxes to a integer and then I'm going to add them to somewhere at last, but I'll get like 1 year and 14 months for result so, could you hit me with some tips or tricks what I should try out.
I'll approciate your time to help me out with this and thanks for advance. =P

View Replies !   View Related
Working With Years (please Help!)
Firstly, when designing tables, how can I format Date/Time to just the Year? I've tried syntax yyyy, but when I enter a year after opening the table, I get a type mismatch error.

Secondly, providing I have the correct custom format for Year, how can I enter the following dates?
1744-1745
Circa 1925
Before 1690
1800s

And would I be able to run queries on them, showing the dates in ascending order?

Any help would be much appreciated!

View Replies !   View Related
10 Consecutive Years
I have atable the is storing ID, attendance_year and meeting code. Iw ould like to query the table and identify the IDs that have 10 consecutive attendance years no matter when it may have occured. So IDs have more than 10 row of data but not necassrily 10 years in a row.

How can I do this?

table looks like

123|2003|WIN03
123|2001|SUM02
456|2001|WIN01
456|2000|WIN00
456|2002|SPR02
456|2006|SUM06

View Replies !   View Related
Happy New Years
Happy New Years guys, all the best. hope the year will be good to us all.

View Replies !   View Related
Sum - Two Years In Different Fields
How do I set up a query to sum data separately by year in the same row of data? I would like to see on one row a sum for year 2005 and also a separate field to sum for 2006.

Help me please!

EWJRichardson

View Replies !   View Related
Same Data Many Years = Confused!
Iím after some database advice. It will probably be something simple I canít see for the woolÖ

Iím putting together a database that describes specifis small area geographies (Scottish Census output areas). For small area there will be various training category variables with associated counts and a general cost for all training in that geography. On its own this isnít really a trouble but the database will grow over time with the same data (more or less) appearing each year to add to the database. I have a master geographical index that is used to aggregate small area data to higher areas so I could :-

- have a series of year based tables linked through the small area field and queried on a year look up basis.
- store each years data into the same table as the last and use a year identified to separate the records
- add new fields for all variables for each year across one table although once the number of fields reach 255 or so it would fall to bits.

I think the third is the worst option but I really would be grateful if anyone had any thoughts you could share with me on the best design approach to take.

Any help gratefully received!

Thanks...

View Replies !   View Related
Multiple Years Data
Need some help figuring out the best way to handle this.
I have one table - tblEngineVolumes which has columns for each month Jan-Dec for the year 2005 with fields for each printer type.

Type Jan Feb Mar.....
PrinterA 100 50 300
PrinterB 500 25 250

I now have to figure out a way to have both 2005 and 2006 volumes for each printer.

My option so far is are below (there will never be any more than 2 years involved)

Type Jan 05 Jan 06 Feb 05 Feb 06
PrinterA 100 150 50 60
PrinterB 500 400 25 35

OR

Type Year Jan Feb
PrinterA 2005 100 50
PrinterA 2006 150 60
PrinterB 2005 500 25
PrinterB 2006 400 35

Would appreciate any thoughts/suggestions

Thanks,

Toni

View Replies !   View Related
Calculating Fiscal Years
Hi,

I have the following function:

Function GetFiscalYear(ByVal x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function

I call on the function in my query to determine the FiscalYear for a date.
Expr1: GetFiscalYear([ProdDate])

It sends back the correct Fiscal Year. However, when I try to put criteria in for Expr1 to show only Fiscal Year 2007 instead of all fiscal years, it gives me a data mismatch type error.

The [ProdDate] has the following formula:
Prod_Date: IIf([40Day]="True",DateAdd("d",-40,[Date Code]),DateAdd("d",-50,[Date Code])) where [40Day] returns True or False to determine if I should subtract 40 or 50 days from the Date Code. The Date Code is in the format ##/##/#### and is sometimes null.

I have tried everything I could think of and am completely stumped. Thanks in advance for any help!!!

View Replies !   View Related
Drop Down List Of Years
Hi folks,

I want to populate a drop down combo box with a list of 10 years (current year +/- five years). I can enter them as a Value list but I don't want to have to update the list every year. Neither do I want to allocate a table to maintain a list.

Any ideas as to how I can auto populate the combo with a 10 yr span (5 previous and 5 next) and default to current year?

Thanks,

Dave

View Replies !   View Related
List All The Years In A Combo Box
I hope someone will be able to help....

I have minimum year and maximum year value (for example 2006 is minimum & 2009 is maximum. How could I get a combo box to display all the years within the range of the minimum and maximum year (in this case, it should give me an option of 2006, 2007, 2008, 2009).

FYI - This database is used to track projects and each project has a commencement date and an estimated completion date. the minimum year is derived from the commencement date and the maximum date is derived from the estimated completion date. I am building a search form whereby the year selected in the combo box should display all the projects commencing, continuing, and ending that year.

Pls help...

Thanks in advance.
jan :confused:

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved