Queries :: Show Data - Query Based On User Selected Time And Date Range
May 17, 2013
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
View Replies
ADVERTISEMENT
Aug 21, 2013
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.
View 4 Replies
View Related
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
Jun 14, 2013
I have multiple buildings that I own. Each building earns a monetary amount each day. Some days they earn $0, some days they earn $1,000. This is all kept track in a data base in which someone manually enters the information each day.The three fields are:
Building
Date
Profit
I have narrowed a query down to one building, and I am interested in two things. How do I write a query that shows a 30 day peak (the most profitable 30 days). In other words. I want to see the following
Building: GNB Tower Date: 02/14/13 - 03/15/13 Profit: $25,162
The next thing I am interested in is a 7 day peak from within these 30 days. This shouldn't be too difficult if I can figure out how to query these 30 days, I can query 7 days out of those 30 days. So this bigger mind stumper is the first one.
out of a date range of months and months (Let's say 10/12/13 - 6-14-13).Once I figure this part out, I am sure I can figure out how to compile all buildings into one large report with their 7 day peak and 30 day peak.
View 1 Replies
View Related
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
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
Jul 7, 2014
I have the following project that works as a timesheet manager:
Users enter in a form the employee name/date/hours worked/role/overtime (yes or no)/rate... on a daily basis. At the end of every week, they must create a report depending on the dates given in a query.
I have only one table (tbl_EmployeeHours) and a query to calculate the number of hours between two dates.
At the moment a report gives me all the information above (employee name/date hours worked etc..) but what I am trying to is a bit different.
I need Access to create a report (based on a date range given by the user) that would give me: per unique name, the number of normal hours worked in total, next column, the number of hours worked as overtime (rate 1.5), then, the number of hours worked as overtime (rate 2).
This means there are 3 different totals. For example, in the date range given, someone might appear twice but in the report he will appear only once with 3 different totals...
My guess:
I would say I need to create a different query for the 3 different totals (normal hours, overtime 1.5 and overtime 2). For each query you would need to ask the user to input the date range (which would not be so user-friendly I agree...). Then, I am not sure how access would create that list of names and combine the 3 different results from the queries...?
View 14 Replies
View Related
Oct 12, 2014
I want to make an application that reminder Radio Broadcaster to read ads based on airing hour.
I have a question , how to make reminder pop up based on selected hour or time and date of that advertisement.
View 3 Replies
View Related
Nov 21, 2013
I have a monthly system data report I wish to manipulate in Access.
I have created a table to match the 3 fields in the data report; the date, widget ID and number of widgets made.
Widgets are made every day (including weekends and bank holidays) but there will only be a data entry on days where the number of widgets made changes from the previous day ie if Widget ID 33 was made 5 times on 05 Oct, 06 Oct, 07 Oct but on 8 Oct there were 7 made, the data would look like…
Date.................Widget ID..................Number Made
05 Oct ..................33.............................. .5
08 Oct ..................33.............................. .7
I would like to write a query that shows the data for every day, duplicating the data for the previous day in the instance the same number is made. The output should look like..
Date.................Widget ID..................Number Made
05 Oct ..................33.............................. .5
06 Oct ..................33.............................. .5
07 Oct ..................33.............................. .5
08 Oct ..................33.............................. .7
The data is monthly. There are so many different widgets on the report that it’s pretty much guaranteed there will be at least one entry on the first date and the last date in order for a query to find the full date range – as opposed to a user having to manually enter the date range.
Using the example above where the first entry is on 05 Oct it should not be assumed that 01-04 Oct was zero, the query should then look for last entry in the previous month’s data stored in the database (I realise the first time I do this some form of manual adjustment would have to be made as no previous data would exist).
View 1 Replies
View Related
Jan 10, 2014
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]....
View 3 Replies
View Related
Apr 16, 2014
I have an error database that I'm attempting to build. I need the results of the query to show User ID when there are no errors during a defined time frame.
View 9 Replies
View Related
Jan 13, 2014
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
View 9 Replies
View Related
Apr 18, 2013
I have a report, based on a query, where the field name is "Date of Work". The criteria in this query field is >=[Start Date] And <=[End Date], which works fine. However I need to have the start and end dates to show in the resulting report header based on the query.
View 1 Replies
View Related
Apr 8, 2013
I have a query that I want to "filter" based on a date derived from a Build statement (Expression). The Build statement works I can even sort by "Ascending".. But when I try to add a "Between [Startdate] And [Enddate] statement in the Criteria box, returns nothing".
Build statement : ReInvestDate: IIf([Eligibility]="SCI",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="TS",DateAdd("d",+1825,[PrevInvestDate]),IIf([Eligibility]="S",DateAdd("d",+3650,[PrevInvestDate]))))
View 8 Replies
View Related
Jul 29, 2013
Context is I have a TABLE 1 that has a record for every package shipped with various details about it, include how it shipped, and the date. Table 2 is a key to go from the ship method to a better grouping we deem service level. Table 3 is a Fuel Surcharge (FSC) Table. It includes various FSC % that vary by different service levels and different date ranges. In the end I need to know the amount of pkgs shipped (by month and servicelevel) and the relevant FSC.I have several tables in my database but we should be able to focus on 3 to explain my situation and only the relevant fields:
TABLE 1 (Main Data):
ShipDate
ShipVia
Table 2:
ShipVia
ServiceLevel
FSCType
Table 3:
FSCType
StartDate
EndDate
FSCPercent
DESIRED RESULT:
Month
ServiceLevel
FSCPercent
PkgCount
Table 1 is Left join on Table 2 based on ShipVia
Table 2 is Left join on Table 3 based on FSCType
The issue is Table 3 is dependent on Table1.ShipDate being between the date ranges as well.
There are overlapping date ranges, however they are differentiated by the FSCType field.The only semi-feasible solution I currently have is to expand Table 3 to include every date, and not date ranges. This will create an issue due to size of date ranges etc.
View 8 Replies
View Related
Aug 29, 2013
I have recently set up a button that exports multiple queries to one Excel file, using TransferSpreadsheet code. This works great and saves a lot of time, the only thing is on many of the queries I have a date range set, so you have to enter a date range to get the results.
Is there away that once I press the button I enter the date range once and than it exports the rest of the data based on this range, rather than entering the same dates multiple times.
View 1 Replies
View Related
Jun 13, 2007
Let's see if I can explain this situation effectively...
I've been handed a database that is set up to track tutoring sessions of students. The original creator designed the database for 1 semester. I am trying to make it functional for x number of semesters. So here's my dilemma: I have several reports that are based on queries. I would like for the user to be able to run the report and it prompt for them to enter a date range, which would then output desired results for that range (I thought this would be more efficient than setting up the db to run reports by semester, so that the user would be able to narrow their results).
So in corresponding queries, I am adding the following criteria to the "Date" field:
Between [Enter Beginning Date] And [Enter Ending Date]. The query produces the right results, just not exactly in the way I would like to see them. This is because the query tracks the number of hours per mentor (the person giving the tutoring) and does a sum.
So in my results, it is summing the hours for every change in date occurrence. For example, the results should look like this (on the report):
Student 1
Mentor A xtotal hours
Student 2
Mentor A xtotal hours
Mentor B xtotal hours
Instead what I'm getting is a sum of hours for every different date, as follows:
Student 1
Mentor A xtotal hours
Mentor A xtotal hours
Mentor A xtotal hours
Student 2
Mentor A xtotal hours
Mentor A xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
Mentor B xtotal hours
And so on...
Maybe I'm approaching this is the wrong fashion? I haven't had much luck searching google or access help... maybe I'm searching with the wrong strings. :)
Any advice would be greatly appreciated.
View 6 Replies
View Related
May 23, 2005
Hi everyone, I am hoping to learn something new about access today. What I need is to be able to run a query that gives me rows of data based on paramenters that I specify. For example, I will query this all just on one table and I want to come up with an output that is in a nice format as follows:
Parameter..........................Member Count
01/01/04 thru 01/20/04...............298
02/01/04 thru 02/20/04...............287
03/01/04 thru 03/20/04...............301
04/01/04 thru 04/20/04...............254
And so on, usually for a calendar year.
Basically each member has an effective date, like member A might have 01/15/04 effective date and member B might have 01/02/04 effective date. I need to capture the count of members by month based on their effective date range. I would like to do it all in one query like above instead of just running a query for January, then February, then March, so on.
Is there a way to do this? Right now I am just running a query and getting one member count at a time and copying that number to an excel spreadsheet. it works okay, but is time consuming. I would rather semi-automate things for me. Any ideas?
Oh and I am new to access, by no means an expert, so be gentle with me.
Thanks,
Hanna
View 2 Replies
View Related
May 21, 2015
I would like to make a query that count simultaneous connection.
Here is my tables
Users (ID_User, Name)
Date_logon (Id_on, Date_logon, time_logon, Num_user#)
Date_logoff (Id_logoff, Date_logoff, time_logoff, Num_user#)
Here is a part of a query that display users date and time of log on and log off.
Users Date_logon Time_logon Date_logoff Time_logoff
Utilisateur1 13/05/2015 17:38:42 13/05/2015 18:52:15
Utilisateur2 13/05/2015 17:12:21 13/05/2015 17:30:24
Utilisateur3 13/05/2015 16:59:08 13/05/2015 17:16:06
Utilisateur4 13/05/2015 16:54:14 13/05/2015 18:32:13
Then I put that result in a pivot table in Excel. In line we have time logon by date and in column we have time logoff by date.
Here is the issue : In Excel for the 4 lines above, it counts 2 logon at 4PM and 2 logon at 5PM. We should have 4.
View 14 Replies
View Related
Nov 3, 2005
Ok this might be basic stuff i'm a bit dumb
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
Please help the dumb
View 3 Replies
View Related
Feb 4, 2008
I have form with two date text boxes. One is start date and the other is end date. I want to query a table and only pull records that the date field is between the start date and end date.
my code I am working on is:
Dim startdate2 As String
Dim enddate2 As String
Me.start_date = startdate2
Me.end_date = enddate2
Dim SQL As String
SQL = SELECT FROM 'project' WHERE Date between 'startdate2' and 'enddate2'
DoCmd.RunSQL SQL
I am getting a error on the sql = statement, I can't seem to get the statement right.
View 4 Replies
View Related
Aug 24, 2015
I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.
Code:
DoCmd.RunSQL DelSummarySQL
Here is my full code
Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date
[code]...
View 2 Replies
View Related
Oct 8, 2014
Is it possible to have the user select which field is added to the query through a parameter? In my query I have a table where each record has a part number and has sales number for each month. When the user runs the query I want them to be able to select which month is returned.
My table basically looks like this:
PartNumber,Jan,Feb,Mar,Apr,May etc.
01PartNumber,15,20,30,25,28 etc.
02PartNumber,12,14,12,16,20 etc
I want the user to enter "Mar" when prompted and return the parts numbers with values from "Mar".
Access 2007
View 10 Replies
View Related
Nov 8, 2013
I have a form where someone enters a 'Job' to be done which in turn creates a record in a table with all this information
On the form I have a field called 'Repeat Frequency' where the user can choose how often they would like this 'Job' to repeat. e.g. Monthly
Then, on another form someone else can check what 'Jobs' should be done on that day
I have managed to get this to work fine using the DatePart function and it displays all jobs to be done that fall on the current day (e.g. if they choose to repeat it weekly, it will look for all entries where the datePart "d" [DateToBeDone] = Date()-7, -14, -21 and -28)
The problem is that i have been limited to the options of 'repeatability'
What i would like to do is change the options from 'Weekly', 'monthly' etc and instead have a field makrked "Repeat every so many days"
Is there a criteria i can use in a query where i can show records every so many days? i.e. if there is a job in the system that should repeat every 7 days and the [DateToBeDone] is set to 31/10/13, it would return this record on 07/11/13, 14/11/13, 21/11/13 and so on?
I Have tried DatePart ("d", [DateToBeDone]=DatePart ("d", (Date()-[RepeatDays] but what happens is it only shows it if the day is that many number of days in the past and not multiples as i said above.
View 7 Replies
View Related
Aug 22, 2005
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
What should I do for solve my problem?
View 1 Replies
View Related
Aug 22, 2005
I have some project run on MS-Access as front-end with database linked to MS-SQL Server. I have some column of table contain Date-Time data that store data as General Date format (ie 01/01/2005 08:00:00). I create some form for my staff to key in a data of lab test that they will be key in only time with out date value. On form, I show this value as time only too. But I want to use this data with Date value for some calculate as backgroud process.
So...
In case of new data, Database will be store my data as CurrentDate with Time that my staff key in.
In case of data update, Database will be store my data as ExistDate with Time that my staff may update.
What should I do for solve my problem?
View 2 Replies
View Related
