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 ....
First, I am trying to get a query to return records between a certain date range. In the form I have DateFrom and DateTo unbound text boxes from which faculty select the date range. I know I have done this before, but I cant figure out how to create a field name and write the criteria for the source query in design mode!
I tried [Form]![FormName]![DateFrom]<[Form]![FormName]![DateTo] in the criteria but I can't seem to write a valid field name that doesn't alter the criteria in some way...
Second, in the same form, faculty enter the StudentID for the student they wish to get records for. How do I write the code to show a msgbox when there is no such ID in the event they enter an StudentID incorrectly.
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.
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 RelatedI have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
but this returns errors when I try to run it.
I'm using Access 2010.
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 have two questions, both the same query.
I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?
This is what I have currently in my query
CO_resp_rcvd (date field)
Criteria: Between [Start Date] And [End Date]
(I need null values as well because there will be some if the CO has not responded yet but needs to)
Formula:
This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?
Not sure how to combine it to work - the wrapper is a bus day function
This is what I have so far in the query
CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())
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
[code]....
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 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 have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
View 1 Replies View RelatedI 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.
View 4 Replies View RelatedI have a query that selects usage records from a table where a data field falls within a rolling 12-month period that ends on a month and year selected by the user on a form.
PARAMETERS [Forms]![frmReport]![cboMonth] Short, [Forms]![frmReport]![txtYear] Long;
SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((DateDiff("m",[dtUsage],DateSerial([Forms]![frmReport]![txtYear],[Forms]![frmReport]![cboMonth],1))) Between 0 And 11));
I also have a query that pulls data between two dates that the user specifies using two textboxes on a form.
SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtStartDate] And [Forms]![frmReport]![txtEndDate]));
On the form I have an Option Group control that sets the visible properties of the 4 controls;
12-Month Rolling
cboMonth - Month Combobox
txtYear - User entered year
Date Range
txtStartDate - User entered Start Date
txtEndDate - User entered End Date
What I was planning on doing was adding two new textboxes (txtQryStart & txtQryEnd) and depending on what option is selected, change the dates accordingly
blnDateRange = (Me.optDate = 1)
blnMonth = (Me.optDate = 2)
If blnDateRange Then
Me.txtQryStart = Me.txtStartDate
Me.txtQryEnd = Me.txtEndDate
End If
If blnMonth Then
If Me.cboMonth = 12 Then
EndMonth = 1
EndYear = Me.txtYear + 1
StartMonth = EndMonth
StartYear = Me.txtYear
Else
EndMonth = Me.cboMonth + 1
EndYear = Me.txtYear
StartMonth = EndMonth + 1
StartYear = Me.txtYear - 1
End If
Me.txtQryStart = DateSerial(StartYear, StartMonth, 1) - 1
Me.txtQryEnd = DateSerial(EndYear, EndMonth, 1)
End If
Now I can use one query to accomplish both types of query;
SELECT UsageID, dtUsage, dblUsage
FROM tblUsage
WHERE (((tblUsage.dtUsage) Between [Forms]![frmReport]![txtQryStart] And [Forms]![frmReport]![txtQryEnd]));
This approach works. It saves me from having to duplicate the queries and some future headache if anything needs to be changed in the queries.
What I was wondering is there an better/simpler way to do this?
:confused:
My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:
Field A Field B Field C
123456 AAAA 1/8/13
123456 BBBBI 1/8/13
123456 CCCC 1/8/13
123456 DDDD 1/8/13
123456 EEEEEE 3/10/13
123456 FFFFFF 3/10/13
123456 GGGG 3/10/13
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13
The desired results would be to return all records with the last/max date, so yield:
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13
I have tried the max & last functions, yet no success.
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 m trying to make form which filters my records and generates a report..here's where I am
Code:
Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Code:
Like "*" & [Forms]![Form1]![Text6] & "*"
the code is in report record source
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
I'm trying to produce a query that shows all records of patients that have a 'non-active' status (stored in the 'Patient Details' table) and haven't had any deliveries after 31/10/2011 (date stored in the 'Deliveries' table). I've tried a few different ways including using NOT IN (which access didn't like!) but I'm still no closer to getting the correct records.
View 2 Replies View RelatedI am trying to create a simple database to keep track of employee Car Insurances and MOT information.I am trying to create a query that will show me the following:
When the field "motexpiry" is Empty OR has a date within 30 days from todays date (including if today's date is in the field) OR the date is in the past.It also needs to show records with the same criteria for the field "insuranceexpiry".
And needs to show records where the field "cowensform" is blank.These are all OR queries, so that as long as ONE of all of those criteria is met, the record shows up.Once that query works, I need a very similar query but only showing records where one or more of those criteria is met, but only if the record also has "Oldham" in the "area" field.
I can then copy that query and edit the "Oldham" bit to have a query for each of our area offices.I tried putting "Oldham" in the criteria line of the area field in the query design, but it seemed to have no affect.
I have a query that is hard coded with a date range.
SELECT tblCase.CaseId, tblCase.ReqReceived, tblCase.Letter_AMPI,
FROM tblCase
WHERE (((tblCase.Letter_AMPI) Between #4/1/2014# And #3/31/2015#)) OR (((tblCase.ReqReceived) Between #4/1/2014# And #3/31/2015#))
ORDER BY tblCase.CaseId;
I would like to create a parameter query that allows me to only enter the year and the query would append the rest of the date range. So for example, if I prompt the user to enter the date and they enter 2014, the query would know that it means Between #4/1/2014# And #3/31/2015# or if I enter 2015, it means Between #4/1/2015# And #3/31/2016#.As well the date would need to go into both fields ReqReceived and Letter_AMPI.
I have what I think is a simple query returning the names of students that have been dismissed since September 2012 using a "WithdrawnDate" field. The query pulls a lot of information from other related tables (about 6 different ones), and has two expressions.
When the criteria is set to either "Is Not Null" or a date range (which is all I need), it does not return the complete set of records based on the data that fits the criteria in the main table?
Could there be some sort of join preventing all records from being returned?
I am trying to calculate the total hobbs time (Ending Hobbs - Starting Hobbs = Total Hobbs) based on a user inputed date range. The query that I created (see attachment) doesn't seem to give me what I'm wanting.
View 14 Replies View RelatedI have an database that uses a couple of different date ranges, so I created a table that shows the different date ranges that may be required (xReport Dates) so I didn't have to keep manually editing queries or entering dates every time.
I have one query that appends data from one table into another based on a date range that you need to manually enter when prompted; I can't seem to get it to refer to my xReport Dates table for the range.
Its currently set up as below:
INSERT INTO 001_M_Gross_Telesales ( UpdateDate, OMSNumber, MediaRoute, ExecName, SaleType, Name,
[Reporting Campaign], [Reporting Team], [Sales Leader], [Reprting Name], [Media Route2] )
SELECT Max(L_ExecTracker.UpdateDate) AS MaxOfUpdateDate, L_ExecTracker.OMSNumber,
L_ExecTracker.Campaign, L_ExecTracker.ExecName, L_ExecTracker.SaleType, Z_Ref_Agent_Table1.Field23,
[Code] .....
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.
I just wanted to know how can i set a parameter on the following cross tab query to filter dates. Date field is [pdate By Day].
TRANSFORM Sum([PettyMaster Query2].Amount) AS SumOfAmount
SELECT [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
FROM PettyMaster, [PettyMaster Query2]
GROUP BY [PettyMaster Query2].[Petty Cat].Field2, [PettyMaster Query2].[pdate By Day], [PettyMaster Query2].ProjLoc, [PettyMaster Query2].descriptionofpay, [PettyMaster Query2].projno
PIVOT [PettyMaster Query2].PettyCOA.Field2;
Is there a way to show the earliest and latest dates of a report generated by a non-date field?
E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)