Query Date And Hours Fields

Sep 9, 2005

Ok I have a tbl with the following fields:
Name
Date
Activity
Hours

I have a form with these fields on it and two additional fields:
Total Hours
Hours this Month

Those fields need to do just what they say, calculate the total hours and calculate total hours for the current month only.

Here is the code that I have thus far, that is not working:
SELECT tblVolunteers.Date, tblVolunteers.Name, Sum(tblVolunteers.Hours) AS tot
FROM tblVolunteers
GROUP BY tblVolunteers.Date, tblVolunteers.Name
HAVING (((tblVolunteers.Date)=Month(Now())))
ORDER BY tblVolunteers.Name;


If I take out the "=Month(Now)) portion I can get the total hours, so that part is done, I just figured that out..... I get nothing back on the above sql statement.

What am I missing?

Chuck

View Replies


ADVERTISEMENT

Figuring A Start Date Based On End Date And Hours

Dec 27, 2006

I manage training for a large organization and am able to get a data dump that contains class end dates and class hours. Based off of those two, is it possible to determine a start date (excluding non-business days)?

For example, a 2 day class would be 16 'Hours' with an 'End Date' of Friday the 15th. Is there a formula that would give me Thursday the 14th as a 'Start Date?'

More importantly, if 'Hours' is 80 and the 'End Date' Friday the 15th, would it be able to give me a 'Start Date' of Monday the 4th? Can Access account for holidays?

View 2 Replies View Related

Problem With Date (hours)

Dec 14, 2005

Hello.
I have this continous forms which gets data from a query. The query has a field that sums date(hours), and I want them to show more them 24, for example, 42:30.

If I use general number it shows something like 1.2303256 and if I use short date it show something like 02:00 for 26 hours.

If I use a simple code is shows the SAME value in ALL the continuos form.

Which code should I put at the forms field so it would work, that is, it shows a diferent short date in ALL the continous form?

Thanks
Filipe Lopes

View 2 Replies View Related

Queries :: Subtract 8 Hours Of Work From Result Hours

Nov 29, 2014

I want to subtract 8 hours of work of the Result of employee work hours/

There is attachment...

View 3 Replies View Related

Subtracting 12 Hours From A Date-time Field

Nov 13, 2007

I am trying to subtract 12 hours from a time-date field when the payperiod does not equal 01.

in a query I have:

work date: iif([payperiod]="01",[StartTime],[starttime]-#12:00:00#) - Access added a PM before the second hash so it looks like this:

iif([payperiod]="01",[StartTime],[starttime]-#12:00:00 PM#)

When I run the query I get a message box: "syntax error (missing operator) in query expression '00:00:PM#,2))". I am unable to open the query to correct the error. I can cop the unaltered query from a back up database.

My question is how do I subtract the 12 hours.

Thanks

Steve

View 3 Replies View Related

Using DSUM With Textboxes - Display Total Of Hours Based On Date

Jan 19, 2015

I am having difficulty getting a second textbox to display the correct total of hours based on a date entered into a first textbox.

Setup:

Table Name: TestTable1
Fields: 'RequestDate' & 'Hours'
Form: 'Form1'
2 Textboxes Unbound: Named 'Date' & 'Total'

What I am trying to accomplish:

Based upon a date entered into the "Date" textbox I want the "Total" textbox to display the total hours associated with that date.

What I have tried:

I have tried using, in the control source property of the "Total" textbox, many iterations of both Sum(IIF & DSum(

I am currently using the following:

=DSum("[Hours]","TestTable1","[RequestDate]='Forms! Form1!'Me.Date'")

I have tried this without the Forms designation; without the "Me" designation; Etc.

Some attempts return the Error or Name error while other efforts return a blank textbox...

View 4 Replies View Related

Subtract 7 Hours From The Query

Apr 19, 2007

4/11/2007 1:01:28 AM
4/12/2007 2:02:52 AM
4/13/2007 4:21:30 AM
4/14/2007 2:22:21 AM
4/15/2007 3:34:53 AM
4/16/2007 4:02:37 AM
4/17/2007 5:03:15 AM
4/18/2007 7:17:34 AM

I need helps again
How can I subtract 7 hour from the query

Thanks

View 3 Replies View Related

Total Hours (or Minutes) In A Query

Oct 9, 2007

I have a table wherein the time worked by 25 employees are recorded. This table has the start time, endtime, break time and late times. The Late Time is the number of minutes that the employee is late to work. I am required to do a query of the team's (all 25 staff) late times per month. I have done a query which shows the late times of the individual on the dates that they were late to work and created a crosstab query for that.

I am going around in circles. How can I have a total of the team's late times in a query? Please, could someone please point me in the right direction?

View 6 Replies View Related

Queries :: Query Group By Hours

Aug 10, 2014

I have a table with a list of records, each record has a timeFrom this I have a query, what I would like the query to do, is group the records in the query by hours.

View 1 Replies View Related

Between Date Query/multiple Fields

Nov 13, 2006

Hi! My question is similar to Hondasteed earlier today but a little different. I have a database that has 6 date fields. I would like to develop a query where the user enters a start and end date and the query checks all six of those fields for the appropriate dates and shows only those records who have dates in that range. The dates would change each month as we ran the query again. I would appreciate any help as this has really had me stuck and I'm guessing it's a simple solution. Thanks!

View 6 Replies View Related

Creating A Query Not Between 2 Date Fields

Aug 18, 2011

I am trying to finish off my database with this last query. I have two fields named date_to and date_from, within a table tbl_non_avail . I need to show people who are available not between the from and to dates.

View 3 Replies View Related

Queries :: Crosstab Query - Sum Of Hours Worked

Feb 12, 2014

This crosstab query has the employee's name as a row heading, each calendar day as a column heading and the sum of Hours worked as the value.

TRANSFORM Sum(tbl_Tracker.Hours) AS SumOfHours
SELECT tbl_Employee_Master.Full_Name
FROM tbl_Employee_Master INNER JOIN tbl_Tracker ON tbl_Employee_Master.user_no = tbl_Tracker.User_Id
GROUP BY tbl_Employee_Master.Full_Name
ORDER BY tbl_Employee_Master.Full_Name, tbl_Tracker.TheDate
PIVOT tbl_Tracker.TheDate;

It returns data that looks like this:
FullName 2/3/2014 2/4/2013 2/5/2014
John Smith 8 0 8
Joe Jones 0 8 0
Cathy Wise 8 8 8

I want the data returned to look like this:
2/3/2014 2/4/2014 2/5/2014
John Smith Joe Jones John Smith
Cathy Wise Cathy Wise Cathy Wise

The hours worked are not important - only the names associated with the dates.

View 2 Replies View Related

Query: Most Recent Date From Several Fields In Two Tables

Jul 15, 2005

I have a parent table [Case] and a child table [Action], in a one-to-many [Access 2003].

The parent has an open date and a closed date.

The child has a received date and a completed date.
The child may contain more than 1 record that matches the parent.

Date fields for activities that have not yet happened are blank.

A typical example might be one parent and two child entries for a total of six dates fields.

I am after only a [single] most-recent action date of the six [there could be a tie for that most recent date, and then there would be two records returned in the result].

I am working for a table that includes a list of every parent record with the name and date of the most recent, or latest activity date.

After two days I decided to ask for help.

Thanks,

View 1 Replies View Related

Averaging Date/time Fields With A Query

Apr 15, 2008

I have a table in which each record includes a field showing the time that an event started and then another field which stores "how long did it take", these two fields are both defined as a short Time. I then have a query that runs through the "how long did it take" fields to produce an average time for the whole sequence of events. Two problems:

1. The average comes out as a decimal like 39553.367942 instead of several hours and minutes.

2. When I try to access this value using DLookup on a form I get "#error"

Any suggestions?

thanks

View 2 Replies View Related

Query That Grabs Most Recent Date, And Associated Fields

Dec 10, 2007

The database I am working with contains several different tables and a variety of information. I need to create a query that accesses information from two different tables. Though the tables contain other fields, the fields I am most interested in are:

Table 1: Protocol Information
"Protocol ID", "Title", "Expiration Date"

Table 2: Regulatory Submission
"Protocol ID", "Description of Submission", "Submitted By", "Date of Submission".

The way the data is stored, each protocol has a Protocol ID, and there is only one record per protocol in the "Protocol Information" table.

However, each time these protocols are submitted to our review board, a log is created in the "Regulatory Submissions" table.

I need to generate a query that pulls only the most recent submission date, so that the query output would look like this:

"Protocol ID", "Title", "Description of Submission", "Submitted By", "Date of Submission" (Where this is the most recent one), "Expiration Date"

From other forums and posts, I tried to create an SQL aggregate function, using Max or Last to get the most recent date. I was successful, but only if the query contained just the "Protocol ID", "Date Submitted", and "Description of Submission" fields. Adding any more, or creating a query to use the outputted data didn't work (it could be that my second query was not written correctly.) I am very novice at creating complex queries, and at SQL.

One additional complication: There are two values in the "Description of Submission" field of the "Regulatory Submissions" table that I am not interested in. They are "Adverse event" and "AE Summary Log". I can successfully filter them out using a criteria expression, but integrating all of these pieces has not been easy (or possible, yet).

I'm out of ideas. This is really complicated, and I apologize. Any assistance would be greatly appreciated.

View 14 Replies View Related

Tables :: Linking Date Fields In A Query?

Nov 27, 2013

I'm having difficulty linking these two tables' date fields in the query qryDivVolShiftCount - what I'm doing wrong?

View 2 Replies View Related

Seniority List Query With Two Date Fields

Jun 3, 2014

I made a basic Seniority List for my work place using a simple query that pulls info from a main Employee Table. Easy enough using just a simple query with a sort. Problem is, about a dozen or so of the Employee have left and since been rehired. Their Seniority is based on their Rehire Date and not their Original Hire Date. In the main Employee Table I am pulling this info from, there are two Date Fields...

[Hire Date]

[Rehire Date]

Is there a way to build a query where it can check both fields, use the more recent date, then copy the "winner" in a 3rd field?

View 5 Replies View Related

How To Sum Fields In A Query With Different Date Criteria From Other Table

Aug 31, 2015

Table 1: contains sales summed by salesrep by week_date for 5 years + current
Table 2: contains sales rep, start_date and end_date

Query: Sum sales by rep where (start_date >= week_date and end_date <=week_date)

Each rep has different start and end dates.

View 7 Replies View Related

Select Query To Find Empty Date Fields

Jan 26, 2008

I am trying to create a select query on "ApprovedDate" where no approval is recorded. IsNull returns an expected type mismatch. Any ideas?
Regards:confused:

View 2 Replies View Related

General :: MS Query Criteria With Two Fields If Either Contain A January Date

Jun 16, 2015

Basically, I have a table showing our clients and when their next service dates are:

I need the query to show NEXT SERVICE DUE dates in January, AND/OR if NEXT LOLER has any January dates.

These should show as either or both fields have January dates:

Next Service Due: February
Next LOLER: January

Next Service Due: January
Next LOLER: December

Next Service Due: January
Next LOLER: January

I managed to do the NEXT SERVICE DUE with January dates, but not both

View 3 Replies View Related

Queries :: Query To Pull Information From Two Date Fields Into One

Jan 21, 2014

I need to know a query to pull information from two date fields into one. I have a BuriedStartDate field and an AerialStartDate field. In a third field I have PlacementStart. I would like to put the date into the PlacementStart field which ever is the oldest date bewtween the BuriedStartDate and AerialStartDate. It has been awhile since I have done any queries on Access 2007. I am completely self taught in Access.

View 2 Replies View Related

Creating Expression On Multiple Date Fields In A Query

Jun 12, 2014

I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:

Student Name
Start Date
Midpoint Date
Hours

It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]

Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))

View 14 Replies View Related

Queries :: Filter Query To Calculate Sum Of Fields In Date Range

Nov 18, 2014

I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.

All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.

I've been trying with Totals in the query and crosstab queries but am not familiar with them.

View 4 Replies View Related

Modules & VBA :: Date Field To AutoPopulate Other Date Fields To Future Date

Oct 24, 2013

I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"

I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =

Code : DateAdd(d,2,[IncidentDate])

so I removed the parenthesis and nothing happened

Code : DateAdd d,2,[IncidentDate]

I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either

Code : DateAdd d,2,[ContainDueDate]

I'm not sure if I'm even using the right function to get what I want.

View 4 Replies View Related

Queries :: Getting A Query On Multiple Checkbox Fields To Work With A Date Range?

Nov 7, 2014

I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.

I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.

An example of the parameters used for one of the checkbox fields in Query Builder is as follows:

Field: SumAnger: Sum([Anger]*-1)
Table: Default as only one table
Total: Expression
Show: Checked

This works fine.

My latest parameters for the date range are this:

Field: [cDate]
Table: Default as only one table
Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM!
Show: Checked or Unchecked makes no difference
Criteria: Between [From Date:] And [To Date:]

This gives a statement in SQL view of:

SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate]
FROM tblCommsLog
WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));

what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.

View 1 Replies View Related

Display Fields As Column Showing Date One Month After The Date In Field Above

Jun 2, 2015

I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.

The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.

I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!

Now I could do that in Excel, but I don't know how to get Access to do it.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved