Date/Time Query And Totals

Jan 6, 2005

I have a query that will return a total number of hours
between to specific dates. I would also like to have that
same column return the total number of ours regardless of the
dates. Can anyone please help me?

View Replies


ADVERTISEMENT

Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria

Aug 17, 2006

Hello buddies :D, do you have any idea how to make this work?

To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.

This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif

View 10 Replies View Related

Queries :: Query Time Difference AND Monthly Totals

Mar 21, 2013

I am VERY, and I mean VERY new to Access. I've been racking my brain all afternoon and googling like crazy. I just completed two levels of training on Access 2010 and have never worked with the program before. I already set up my tables and now I am on to querying. I have a table with several columns, two of which are "Start Time" and "End Time". I already created a query using the DateDiff function to calculate the time difference for each record. It output a new field with the time difference in hours. Now, I want to sum the totals of the time differences by month and I cannot for the life of me figure it out. My new query has Date (m/dd/yyy) and Hours.

View 14 Replies View Related

Sum Totals Query Using A Date Parameter

Sep 13, 2005

Hi,

I hope someone can help this is infuriating me as I am by no means an Access expert , I'm just above beginner.

I am putting together a databse for our Credit Controllers to manage their arrangements to pay arrears.

The Main table consists of the fields.

CustNumber---Surname---Promise Amount---Actual Amount---Date

13456 Nakamura £25 £20 20/09/05

45323 Petrov £55 £55 13/09/05

21245 Hartson £23 £21 21/09/05

67543 Sutton £45 £26 25/09/05

23541 McGeady £50 £50 24/09/05

This table is input by a form.

What I want to do is to produce a Query which will total the Promise Amount column and The Actual Amount column using a Date Parameter.

For example I want to know how much has been brought in between 20/09/05 and 25/09/05 therefore producing the result that I'll get a total of £143 for the Promise Amount Column and £117 for the Actual Amount Column. I have tried using the "sum" in Totals, but I think the date parameter is affecting that, the date parameter query I'm using is :

Between [Enter the beginning date:] And [Enter the ending date:]

I'm using Access 2000. Can anyone help?

Thanks in advance

Steph

View 3 Replies View Related

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Code:
Between [StartDate:] And [EndDate:]

And

Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related

Totals Query/sums/grand Totals

Sep 4, 2007

Hey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!

View 14 Replies View Related

Year To Date Totals, Month To Date, Week To Date

Oct 9, 2005

Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.

I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.

View 9 Replies View Related

Queries :: Date Field With Time - Query For Date Only And Get All Records

Apr 26, 2013

I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.

View 11 Replies View Related

Queries :: Getting Date / Time Range - Date And Time Are Separate Fields

Mar 13, 2014

I have a database with date and time each stored in a separate field. Now I want to query the database based on a start date/time and an end date/time. I started with the code below but it only returns events within the same time range on each day when what I really need is every event from a specified date and time through a specified date and time.

SELECT myTable.ID AS myTable_ID, myDate, myTime, FirstName, LastName
FROM Staff INNER JOIN myTable ON
Staff.ID = myTable.StaffID
WHERE myTable.myDate >= #3/2/2014#
AND myTable.myDate <= #3/3/2014#
AND myTable.myTime >= #8:00PM#
AND myTable.myTime <= #11:00PM#
ORDER BY myDate desc

In the above example what I want is every event from 3/2/2014 8:00PM until 3/3/2014 11:00PM. But what I get instead is every event between 8:00PM and 11:00PM on 3/2/2014 and every event between 8:00PM and 11:00PM on 3/3/2014.

View 4 Replies View Related

Sum Time Of Specific Field Giving Wrong Totals

Jun 13, 2013

I'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples.

Table

- BusArrivalTime
- BusDepartTime

Query

In my Query I'm making a new field like this. It correctly figures out the difference.

BusWaitTime: DateDiff("n",[BusArrivalTime],[BusDepartTime])

Report

Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.

=[BusWaitTime]60 & Format([BusWaitTime] Mod 60,":00")

Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source

=Sum([BusWaitTime])60 & Format([BusWaitTime] Mod 60,":00")

...and it's summing my records, but the value is off by a few minutes and I cant figure out why.

In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead.

View 7 Replies View Related

Queries :: Aggregate Sales Totals Within Specific Time-frame?

Mar 23, 2013

stuck with this problem on trying to calculate the aggregate sales totals of a product within a specific time frame. The query that I have built instead divides each sum by date, where it should be grouped according to product instead.

Here are some screen shots as to how it looks in Access. [also see attached ZIP if you don't want to unbreak links ]

[URL]

As you can see, the PRODUCT_ID column is not combining together according to their IDs.

[URL]

This how my Design View looks.

The SQL for my current query is:

SELECT PRODUCTS.PRODUCT_ID, Sum(SALES.SALES) AS SumOfSALES, SALES.TRANSDATE
FROM PRODUCTS INNER JOIN SALES ON PRODUCTS.PRODUCT_ID = SALES.PRODUCT_ID
GROUP BY PRODUCTS.PRODUCT_ID, SALES.TRANSDATE
HAVING (((SALES.TRANSDATE)>=#9/1/2008# And (SALES.TRANSDATE)<=#12/31/2008#));

View 3 Replies View Related

Query To Combine A Date And A Time

Apr 28, 2006

Hi,
I am trying to create a general date field by combinging a date (short date) and time (medium time) field. The fields are named recDate and recTime for the two I am combining, and cmbDateTime for the result field.

When I run the query, the result I receive is an errror stating that it is a data type mismatch.

What am I missing?

Thanks in advance,
Jake

View 4 Replies View Related

Queries :: MS Query With Date And Time

May 20, 2014

I have a table with the following records date/time,id,barcode.id is related to another table to retrieve fname and lname and barcode is related to another table to retrieve packageid

so my query looks like date/time,fname,lname,packageid

what i need is the ability to retrieve all records for yesterday from 00:01 to 23:59 ..im sure there will be an easy way fo doing this - i also need the ability to change 23:59 to possibly 02:00 into the following day for longer shifts

View 3 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 Of A Date/Time Field With Other Criteria

May 13, 2005

I'm trying to setup a query to pull only the records that have the latest date in Time Scanned for each different serial number.

I have a table with 4 fields: Serial Number, Model Number, Location and Time Scanned. Some records will have the same Serial Number repeated with different Model numbers,locations and times scanned.

I can't figure out how to query only the Last Time Scanned for each different Serial Number. I've tried using "Last" for critria on the time scanned field, but I need that for each different serial number.

It's probably easy...but I'm not able to get it. Thanks!

View 2 Replies View Related

Date Time Query No Pulling Correct Dates!!

May 11, 2006

Hi

I have a table with

"starttime", "endtime", "agent", "phonenumb" and a datediff calculation that calculates the diff between "starttime" and "endtime"

the "starttime" and "endtime" columns are in "10/05/2006 12:57:55" format, i.e. the column holds both the date and time

The data is stored in an sql DB and is accessed by an odbc link using Access 2003

I want to be able to run a query within access that lets me chose "agent" and a specific date or a range of dates.

Any help would be greatly appreciated

Thanks

Ben

View 1 Replies View Related

Query Records With Todays Date But I NEED The Time Included

Dec 13, 2006

I have a basic trouble ticket type database that I built. It records troubles and auto fills the date and time when a new records created. I want a simple query that will display the number of calls that each person has taken today.

The problem being that If I use >Date()-1 I will get calls from this time yesterday until now. Is there a way to round down the time to today only? I don't want to lose my time stamp.

View 8 Replies View Related

Change Text To Date/Time Field In A Query

Feb 7, 2008

I have created a table called - "Test"
The properties of the table is listed below

Table Name: Test
Field Name: ADMDAT2 (Text)
DISDAT2 (Text)
Operation Date (Date/Time)

I have written a query to populate a field where the Operation Date is between the ADMDAT2 and DISDAT2

Expr1: IIf([ADMDAT2] Is Null,"",IIf([Operation Date]>=[ADMDAT2] And [Operation Date]<=[DISDAT2],"Match"))

Unfornately it returns and ERROR message... I believe this may be because, the data type of the field, matching a Text with a Date/Time, I have rerun the query using a sample table where all the fields are Date/Time, and it work perfectly.

What i need help with how do i convert a text field into a Date/Time in a query?

So i can place that in the query before i populate the Test table. therefore it all should be date/time

thanks in advance

View 1 Replies View Related

Update And Delete Query Of A Record By Date At The Same Time..

Oct 9, 2006

I have two tables. PreOrder and Order Tables. When our customer actually order a product, I want all information of that product to move from PreOrder table and append it to Order table. Currently, my user has to run an append query to add the record from PreOrder and add it to Order table. After that, she has to run a delete query to delete the record from PreOrder table. Since our query is setup to run by date, and order number..My user has to type the same thing twice. Is there anyone out there know the easy way? Thanks in advance..

View 1 Replies View Related

Queries :: Finding Records In A Query Where Date / Time Has Been Used?

Aug 15, 2013

I have a database that is used (partially) to enter appointments during a day. In the appointment column I've entered the date and time as dd-mmm-yyy-hh-nn-ss. I'm now trying to search for appointments entered on a specific day through a user input ([Enter Date] in the query criteria) Problem is, this only returns records where the time hasn't been entered and the time shows as 00:00:00.

I've tried CDate which gives me add/mmm/yyy return. if I try to search against that I get nil returns though. As a test I put DATE() in the criteria but it then says that it's an invalid foremat. I've also tried various machinations of "Like" and "Between" without success.

View 5 Replies View Related

Queries :: Changing Date / Time Format Using Query

Jun 26, 2013

I am having difficulties changing a date/time field like '22/04/2013 09:14' to make it look like '2013-04-22 09:14' using a query.

View 3 Replies View Related

Date / Time With A Wild Card As Criteria In Query?

Apr 21, 2015

How may I write a criteria in the query where I can pull records with date/time data type (01/31/15 1:30:23 PM) based on today's date?

Example using today's date (4/21/15):

Date()= 4/21/15

The query will pull these records:
ABC 4/21/15 9:14:40 AM
DEF 4/21/15 10:50:02 AM
GHI 4/21/15 2:32:35 PM
etc.

The time does not matter as long as the date is 4/21/15.

I have tried using these formulas:
=Date() & *
=Date(Now)

but these are no success.

View 3 Replies View Related

WorkUnit Totals By Month Via Date Range

Jun 19, 2007

Below is the code I have for a query that shows me the number of WorkUnits via a date range that is put in via calendars. This works great. It gives me one total for the date range. What I would like to be able to do is see the WorkUnit totals by month via a date range. If I put in the date range: Jan 1-April 30 I would like to get four WorkUnit totals instead of one. I would like to see the WorkUnit totals for each month. Can anyone assist with this? I asked a similar question like this yesterday but he SQL for this code is different and I have not been able to figure out how to apply yesterday's answer to this one. I have tried everything I can think of to no avail.


SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals]
FROM [Select Distinct [WorkUnit]
FROM WorkUnitsFaultsMainTBL
WHERE BuildID IN ("G004","E818","N005","F813","D024","C879") AND
PossibleCause NOT IN ("Out of Stock") AND
[TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND
[Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;

View 2 Replies View Related

Reports :: Auto Populate Date And Time Every Time Form Is Updated

Nov 8, 2013

I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?

View 3 Replies View Related

Reports :: Attendance Database - Show Time In And Time Out For Specific Date

Apr 3, 2014

I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.

Please see the attached screenshot db from attendance machine.

an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.

View 4 Replies View Related

Queries :: Count Number Of Tasks - Group By Date / Time In A Query

Aug 14, 2013

I want to count the number of tasks by department by week. I need the time so my date the task was added is formated as a date/time.

I created a query and added the department (twice so that I can group and count), and transaction date. I clicked on totals and added the count function under the department. I added this criteria to the task date: between [start date] and [end date].

Problem is that it's grouping by day and each one is different because all times are different. How do I group these by day and not time?

View 7 Replies View Related







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