Date/time Period Criteria Confusion
Jan 30, 2008
Hi there
I have created a database in Access XP (2002). In a nutshell, the database records numbers of people attending a seminar; which can take place any number of times per week, and so hence can take place any number of times per month.
I have set up the query so that it can run immediately after a seminar to show the attendants who attended the seminar on that current date "Date()" in the criteria box. However, how would I go about setting it up so that it shows who attended every seminar in the current week or month?
View Replies
ADVERTISEMENT
Aug 3, 2005
Hi,
Over the past three months I have been trying to develop an application which can repeat appointment entries.
I would like to have the option to repeat an appointment entry for a given number of weeks.
For example if an appointment entry was placed for today (Wednesday), there would be a command to create additional duplicate enties for the same day of the week for a given period of weeks ie say every Wednesday for the next 52 weeks.
Any help on how I could approach this would be very much appreciated.
Thanks
View 1 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
Dec 1, 2013
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday
EXIT FUNCTION
ELSE
IF [PunchOut] ISNOT Between Midnight and 0559 hours
EXIT FUNCTION
ELSE
DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
View 4 Replies
View Related
Jun 10, 2012
How do I enter and store a period of time that may be of any length: years (.. unlikely but ...), days, hours, minutes, seconds (or even micro seconds) that I can later add to other dates or times stored in my database? How do I create a sensible data entry mask for yyyy/ddd/hh:nn:ss.999 and then convert this to a field compatible with date/time functions? And what format should this data take (be converted to ..?) and be stored in?
View 2 Replies
View Related
Dec 27, 2005
Hi Guys,
Does anyone know how to write the criteria which includes both the date and time. for example, I want it to be the system's date and my specified date. e.g. (date() 14:00:00)
What will be the correct syntax?
Thx!!!:D
View 2 Replies
View Related
Dec 28, 2006
I'm trying to build a query for a customer that will automatically run without making them enter a date and time.
Between [Enter Start Date] And [Enter End Date]
Is there a way to make a query auto run a date and time with out entering a date and time?
Thank you
View 6 Replies
View Related
Sep 13, 2006
OK... what am I forgetting?
I have a linked SQL table with a Date/Time field called DATE. ALL I WANT TO DO is pluck all the records with a single date from that table.
Predictibly, the date field has values like this:
9/11/2006 12:40:46 PM
If in the MS-Access query I use criteria of #9/11/2006# I get other dates also. I have tried variations on Date() or DatePart but those either fail or don't work. Sheesh.
ULTIMATELY, I want to restrict records to a specific date, and then a certain hour of that date, so if you can include this bit of additional logic in your response, that'd be great too. Note: I am trying to structure a query, not write code.
Thanks!
View 2 Replies
View Related
Jan 9, 2008
Hello, I am having a little trouble trying to get a criteria to work with a query in a database that someone else has passed to me.
In an InputTable there is in a field called DateIn with properties of date and time (general date). I want to be able to create a report that will allow the user to just chose to run the query for one day. Eg. Run the query using a dateinput of 2/01/2008. It just returns all of the records and ignores the criteria. I do not normally have this problem and was wondering where I should be looking.
SELECT InputTable.OrderNo, InputTable.[Customer Name], InputTable.Postcode, InputTable.ReturnsRef, InputTable.Description, InputTable.Unwanted, InputTable.POReturn, InputTable.Faullty, InputTable.Damaged, InputTable.Mispicked, InputTable.Replacement, InputTable.ItemRefund, InputTable.PostageRefund, InputTable.QReason, InputTable.QResolution, InputTable.DateIn, InputTable.Type
FROM InputTable WHERE (((InputTable.DateIn)=#1/2/2008#)
Many thanks in anticipation.
View 10 Replies
View Related
Aug 2, 2006
Can you help we have a data with a list of jobs that scores points, I need to build a query or scirpt that will add 5 points every 30 day fro the date the job was submitted this will help old jobs come the top of the list.
View 1 Replies
View Related
Apr 8, 2008
We sell products out of vending machines and once in every few days (time span between readings may be different every time) read data from the machines using flash drive. Each reading from all machines produces an Excel file:
Reading_Date | Machine_ID | Product_ID | Amount_sold
2008-04-08 | 1 | 1 | 26
The only date that we have is the day on which the data has been read and this tells how many sausages were sold from the date of last reading till the date of current reading (after each reading counters are set to zero).
The problem is to get average amount of product X sold from machine Y on every calendar day. For a total rookie like me it seems like creating another column that would divide the Amount_sold by the number of days between two consecutive readings and put this average amount for every calendar day between these two readings. May it be done this way? How? Another way? How? I would be very grateful if anyone could help me and explain the solution in an accessible way.
View 4 Replies
View Related
Jul 9, 2014
I have designed an access application that logs emails that come into a department. However, the team leader has asked me to create a report to work out if emails were answered within a 24 hour period.
Is there a method to do this. It is not something I have attempted to do within access before.
View 6 Replies
View Related
Jan 8, 2015
I am using access 2013 and have an issue creating a query with employee hours for a two week period.
For starters I have a table with the following:
Employee Number, Date, Time in, Time out, Description, Rate Code, Week end, Pay Period. (there are a couple others but they are not needed for the query)
My table is set up this way so that each employee can clock in and out 1-4 times a day based on what they are doing. For example I may work from 6-8 in the office then 8-10 in the field. The office has one rate code and the field has a different one so i cant just be clocked in from 6-10.
At the end of the pay period I create a report and send it to an accouting firm who does the payroll. Right now I have to manually enter in each employees time. I was hoping that by using this table I could create a query that will give me the calculations I need in order to create the report.
In my query (to Start) I need to have the following with it set up per pay period.
Employee Number Rate Code Regular Hours Overtime Hours
How to set it up so that it calculates the hours based per rate code with the given pay period.
View 11 Replies
View Related
Apr 9, 2015
I am trying to get a count from a table, but only for a certain time period. Here is a little bit of my table:
STRMIDtrDateDT trDT
1617 3/25/2015 7:30:00 PM0
1530 1/8/2015 4:40:00 AM10
1532 1/12/2015 9:20:00 AM60
1533 1/14/2015 4:50:00 AM1
What I am currently doing is querying a table for the Count(*) for the day before. This works all well and good, but now I am wanting to break it down by a time range from the trDateDT field.
View 14 Replies
View Related
Jun 7, 2013
I've been struggling with getting a report to simply count records (I don't need any detail other than the counts). Here is what I have:
Table CompressorRoundsT contains these fields (many more actually but these are the ones I care about for this purpose):
Date, Shift, Round
Possible records in the shift field are 1st, 2nd, 3rd and the round field has Rnd 1 and Rnd 2.
For each date, I need to count the number of records per shift and per round. For example, the report should show that on June 7th, 1st shift has 14 records for round 1 and 14 records for round 2. Same for 2nd and 3rd shift. I don't need any other details of what the records contain, just the counts.
I've had some success but the layout ends up too spread out because it is including the individual records instead of just showing the counts and it isn't grouping the way I want.
It's a report for management to see how many readings (rounds) were missed in a period of time.
View 2 Replies
View Related
Aug 10, 2005
I am creating a query with criteria for a dates column. The column cotaining dates in my Table has many empty fields. I want to limit the criteria for my dates to >#1/1/2004#, but I don't want Access to exclude all the empty fields because I want all the data displayed for the purposes of my report.
What do I do? I'm not terribly familiar with Access, so please explain as completely as possible. Thanks in advance!
View 3 Replies
View Related
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
Jul 5, 2005
HI all,
I am still new in Access databases :o
I have a table with 'General Date' column. So I have to create query wich extracts records between Last record (via Date field) and 'for example' 10 days before. But Last record in Date column may differ from Now().
When this is done I have create calculations with extracted records using agregate functions.
I'm trying to use Last function for Criteria putting it into Date field and substracting with -10 (for 10 days) but it doesn't work.
Please help! :confused:
View 7 Replies
View Related
Oct 23, 2013
I have a pre-established database with a form that calculates total sales and tax for a time period i specify. I want to add a box that will display the number of sale records. Ex. if i had sales of 50,000.00, and that came from 200 sales i want the box to show the number 200.
View 1 Replies
View Related
Jul 13, 2015
Could I change my unbound textbox (txtperiod) on the Report to display the time period.i.e.
Weekly, Monthly, Quarterly
Criteria is BeginDate and EndDate on main form.
View 1 Replies
View Related
Jul 22, 2014
I'm in the process of creating a database in Access 2010 that has two front ends, basically. What I want to be able to do is lock out one of those front ends for a specific period of time every day.
View 2 Replies
View Related
Jan 29, 2014
Calculating the rates over a period of time. I think the attachment will give you a clear picture of what I am looking for.
To get the cost for a particular period I want the rates to be updated as per the given period. How I could get the calculation in the mdb format...
View 4 Replies
View Related
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
Jul 24, 2014
I have a form (frmMetrics) with 2 Combo Boxes: "Year" and "Quarter" These are just number fields (which might be my problem?)
I have a date/time field in a query, and I want the criteria to be based off the year and quarter selected in frmMetrics.
View 2 Replies
View Related
May 16, 2005
Hi,
I am looking for guidance on how to get a query field to display a numeric period from an existing field that contains a date.
ie:
01/01/2005 = 1 (1st Month)
01/05/2005 = 5 (5th Month)
Etc.
Thanks
Darrell...
View 2 Replies
View Related
Mar 31, 2008
Can anyone help?
want to establish the criteria for a report to include 6 months (into the future) from todays date. Have tried dateadd function but this doesn't seem to allow dynamic (automatic) start of period date
Cheers
View 3 Replies
View Related