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
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"
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.
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.
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
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.
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?
I have a form that request information from the user (StartDate, StartTime, EndDate and EndTime) the problem is that it's not working. The only way I can get any data to show is when I remove the StartTime and EndTime. Only then will it pull the items from the StartDate and EndDate.
Here is what I have as my criteria: Between [Forms]![OpPROD_ALL]![StartTime] And [Forms]![ OpPROD_ALL]![EndTime] And Between [Forms]![ OpPROD_ALL]![StartDate] And [Forms]![ OpPROD_ALL]![EndDate]
The users will be able to request a report based on a start and end date along with a start time and end time.
Side note: this is to pull date for 3rd shift (Example) 4/14/2013 10:00PM - 4/15/2013 10PM
I try to find the ID corresponding to the maximum date+time grouped by Machine. In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)
The way to extract the maximum date + time is not difficult
SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine FROM tbl_TexControl GROUP BY tbl_TexControl.Machine;
But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.
I have a query that contains a complete_date including time (this is automatically datestamped when the user presses a button)At the end of the day, I want to show how many records where processed that day but cannot figure out how to display records between 06:00am and 08:00pm on todays date.
Also, the same applies to selecting a range of dates (eg. the full week), How do I display the records that fall between Monday - Friday including the time.
I have finger print machine and i already connect to it and get all log.then i tray to get data and here is the code that im using
SELECT Format(CHECKINOUT.CHECKTIME,"dd/mm/yyyy") AS CDate, IIf([CHECKINOUT.CHECKTYPE]=I,Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm")) AS StartTime, Format(CHECKINOUT.CHECKTIME,"hh:nn:ss ampm") AS EndTime, USERINFO.USERID, USERINFO.Name, CHECKINOUT.CHECKTYPE FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID=USERINFO.USERID WHERE (((Format([CHECKINOUT].[CHECKTIME],"dd/mm/yyyy"))='10/04/2014'));
I have four columns: date1, time1, date2, time2. All are strings.I want to concatenate date1 & time1. Then date2 & time2.Then I want to do the following calculation and the answer to expressed as number of days:
datetime2 - datetime1.
I am using MS Access 2007 and the results are being grouped by a different variable. Ideally I want to complete this action as one complete statement in the SELECT statement of my query. This is very easy to do in Excel but I'm baffled by MS Access!
I have operators that enter equipment downtime information into the database. I need to create a report that pulls all records that have a start date and time based on a start date (field name FromDate on form) at 7:00 AM to an end date (field name ToDate on form) at 6:59 AM and shows the total duration in hours for each record.I'm having difficulty getting my query to display the correct results for the date and time range. Duration calculations work perfectly.Here is my main table only using fields pertinent to this post:
Field Name Data Type Description Exception_Number AutoNumber Primary Key ExceptStartDate Date/Time Start Date formatted as Short Date ExceptStartTime Date/Time Start Time formatted as Medium Time ExceptEndDate Date/Time End Date formatted as Short Date ExceptEndTime Date/Time End Time formatted as Medium Time
I have a totals query that displays the sum of what products we ship each day. A process in the system automatically assigns the date/time to a ShippingDate field when an item is marked as despatched.
The totals query allows users to view what products are shipped between a period specified by the user.
The problem I am getting is that when the user inputs dates into fields [txtStart] and [txtEnd], the query fires up but will not display any records as a start time 00:00:00 and end time 23:59:59 was not input.
I don't want users to have to do this but cannot think of a way around it. My criteria code in the query for the shipping dates is
Code: Between [Forms]![Switchboard]![txtStart] And [Forms]![Switchboard]![txtEnd]
Can I append the start / end time onto the code above somehow or is there another method I can utilise that is probably so glaringly obvious I have missed.
Usually a query allows me manipulate a field property sheet but in this case there is nothing obvious to select like format.
I have a query which is displaying a date/time field a record was input. in my query I'd like to only display the records where the date/time based on what today is i.e. Monday it will display <Friday or yesterday for Tue to Fri.
I am collecting data every hour on the hour and I would like to group this data according to Shift (A, B and C) by Date. The only problem I am having is- how can I keep the overnight shift together as one date? 10pm - 6am
I was able to do a simple query where I was able to assign an A, B, or C depending on the field with the Short Time format using nested IIF statements. From there I was able to group by shift (A, B, or C) per date. However the C Shift is split up.
Example. Can Sunday October 5th 10pm to Monday October 6th 6am be grouped as one date.
Trying to import some data from a linked Excel spreadsheet into a local table. One of the fields is a Date/Time type and is recorded in EST (Eastern Standard Time). I want to keep this field for posterity but also add a separate field with the corresponding time as per BST
For clarity, daylight savings time comes into effect this year on 26th Oct in the UK and 2nd Nov in the US. So generally, there is a 5 hour difference between the two time zones, apart from the period between these two dates, when it is only 4 hours.Here is my query - I am using a SWITCH function to create the BST field
Code: INSERT INTO tblTransactions SELECT ltbPayments.ID AS Reference, ltbPayments.VALUEDATE AS ValueDate, ltbPayments.LOCALAMOUNT AS Amount, ltbPayments.USDAMOUNT AS AmountUSD, tblAccounts.AccountID AS AccountID, ltbPayments.TRANSACTIONTIME AS TransactionTimeEST, SWITCH(DateValue(ltbPayments.TRANSACTIONTIME) < DateSerial(2014,10,26) Or DateValue(ltbPayments.TRANSACTIONTIME) >= DateSerial(2014,11,2),
[code]....
So - how do I explicitly specify the output of the SWITCH function to be in Date/Time format (I presume, by default, it's returning Text, which contradicts the table properties of tblTransactions & the TransactionTimeBST field?...)
I have a repairs db that records [serial number] and [date in for repair], and other info.
I've been asked to make a report to summarize the number of units [serial number] that come back for repair by time interval between [ship date] and the first [date in for repair].
the [serial number] and [ship date] are in a linked excel file table which contains unique [serial number] entries.
The [serial number] and [date in for repair] are in the access table, there may be multiple [serial number]s entered with different [date in for repair] as in repaired multiple times.
1st query: I want to list all the [serial numbers] in the access table with only the earliest [date in for repair] returned.
2nd query: then I need to compare the 1st query results list of [serial numbers] and [date in for repair]to the excel table list of [serial numbers] and [ship dates],
I've found the =DateDiff('d'[ship date],[date in for repair])
I haven't done a query with two data sources before and not sure how to go about getting the DateDiff for each serial number
Once I get results from the DateDiff, for each serial number, I am comfortable handling the Sum(IIf statements to summarize the results and do the serial number range select criteria in the form.
I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.
Code: tblequipment equipmentid equipmentnumber (user defined ID)
tblrentals rentalid rentaldate rentaltime fromparty (c for customer, e for employee, o for other) frompartyid (foriegn key to either customer, employee or other) toparty (same as from) topartyid (same as from)
tblrentaldetails detailid rentalid equipmentid
How can I get a list of ALL equipment showing the most recent date and time, also showing the respective toparty and topartyid? I can get it fairly easily, except for including toparty and topartyid.
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waste.
