I have a scheduling database that requires keeping track of events on a 24 hour basis. I need to keep track of all the events through out the day by date and time. I want the user to only have to enter the time instead of entering a date and time. What is the best way to allow the user to enter only a time, but when the time is saved to the database, it’s saved as a date and time using the current date? I’ve tried using the date add function but that doesn’t work. Does anybody have any ideas.
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
Hi, I have been tasked to create a database that will be accessed through a dial-up connection to our server. What I want to know is which is faster 1 Using a seperate table for dropdowns or 2 Using the lookup facility of a table. cheers
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.
Access 2003. I am trying to save a calculated field (Item No) from a form to the accosiated field within the Products table
I have a hidden text box (itemNo) which is bound to the relevant field and a second text box with the following formula
="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000") as its source
What I am trying to do is to provide an item number for each item of stock based on the Category short name (Catshort) and the incremented item ID Number, with a TV prefix.
I am aware that its a bad itea to place calculated field into the table if the calculation is likely to change, however once my calculation is stored it has no need to change.
I have tried the beforeUpdate method within the ItemNo textbox
Private Sub ItemNo_BeforeUpdate(Cancel As Integer) Me!ItemNo = Me!ItemCalc End Sub
but nothing happens (in that the table is not updated)
Any way of putting together an incremental item number based on the selected category with a "TV" prefix....
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 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?
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.
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
so i created a system to have events booked, and i am trying to check time availability of the event room available, but i dont know what wrong. it either my query or vba code. i have attached the attachment,
Embarrasing question, but my serches have proved fruitless.
Saving a Date field (Short Date) is saving as 1/02/2008 or 27/02/2008 in the table. Formatting this data in a query as dd/mm/yyyy displays correctly, but the mid function mid([Date],4,2) still picks up the table format, not the query format
My objective is to extract the 02 (month) using the mid function in a query. Beacuase the length of the day component varies from 1 to 2 characters, this is proving impossible
Is there a way to save the date as 02/02/2008 in the table?
Tried using input mask dd/mm/yyyy but kept getting invalid format error
Wonder if someone can help please. I'm quite new to Access so please bear with me.
I have a data field in my database consisting of both a date and time.
I then have a form containing two fields where the user can type a 'To' and 'From' date to extract the records that they are interested in. The query behind this uses the 'Between[Enter The Date] And [Enter The Date]' coding.
The problem is that because the field contains a time it doesn't return any records when I run the query.
Can anyone offer a bit of guidance on how I could ignore the time part of the field perhaps by adapting the above.
I have a column/field named [DateTaken] which contains test dates and times in the same cell. I am needing to find those with a test time less than 2:30 pm or <14:30pm.
Hi, I'm using a web based form to store dates (and other records) in an access database. I use the dd/mm/yyyy date format (europe. go figure) Anyway, the problem is that when submitting an ambiguous date, as in 11/01/2004, access sees it as 01-NOV-2004 rather then 11-JAN-2004, as it should.
Why does this happen, and how can I fix it. I have no problem doing the fixing in the access file itself or in the SQL command that populates it- whatever works best.
I'd like to have a button on my dashboard that gives the user the ability to back up the database and save it with a different date. For example, existing database called "Fishing.accdb" and I'd like to have code behind a button that allows the database to be saved as "Fishing0711.accdb"
I'm trying to avoid the user having to go into the File Save as menu system....I've tried the docmd.copyObject method but can't get it to work
I have a query that reports the number of students that are currently enrolled in a given number of schools within an area for that given day that the query is ran. This number can fluctuate greatly day to day, so is there a way to create a table which can hold this information so that I can go back in history and look up what our numbers were on whatever given day (date). (The example below is just a representation of what I actually have.) The School number would be unique, as would the date (I assume), I would like to see all of our school on whatever given date to see what our numbers are or were.
I have an example of the query currently I have set up in access 2007:
School # School Name Qty Date 001 BLUE 300 02/26/2014 002 RED 468 02/26/2014 003 YELLOW 10 02/26/2014
VS
School # School Name Qty Date 001 BLUE 310 02/27/2014 002 RED 400 02/27/2014 003 YELLOW 8 02/27/2014
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code: Private Sub Update_DOD() Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset Dim DOD As String rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code: CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
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.
In VBA, how to compare date variable with date/time field from table, as when defined the data type in table, date/time option is the only choice for date data type even though I don't want the time portion.
I am trying to compare a value in my databse produced by the date() function, short date format, to one that is exactly 24 hours after the value recorded by the date() function. If the value in the database is 24 hours prior to the current date(), I need to flag a text box a diffrent color to alert the user. I am unsure on the If statement that I will need to produce this result.
Any help would be greatly appreciated!!!! Thanks Mikeco555
I have a report that have a datebox(Short date) and a timebox(Short time) and also a box that contains numbers like 36:59 and so on. That number is hours and minutes, I would like to take the date, time and add my 36:59 and get a new date from that and put that in another box. The date and time is used as planned date and planned time for some work to happen. 36:59+planned time+planned date=should be planned finishing date and time. Does anyone know what I have to do, to make this happen ???
How can I create a field in a query, extracting the date portion of a date/time field? The format is mm/dd/yyyy hh:mm:ss. I need just the mm/dd/yyyy so I can prompt the user for a date range, without them having to key in the date mm/dd/yyyy 00:00:00 to mm/dd/yyyy 23:59:59. Please Help
I have a table called : tblTemp contain the field: EndTime (type: ShortTime)
A combo box: cboTime
How can i assign value for cboTime using the field: EndTime from tblTemp
I tried:
dim db as Database Dim rec as DAO.recordset set db = currentDB() Set rec = db.OpenRecordSet("tblTemp") 'Go to appopriate record cboTime = rec![EndTime]
But i recieved the message: You can't assign a value for this object ...
Okay i know you can make a form post the date and time with the =NOW() command, is there a way to make it so it shows the current date and time? so you can watch the day go by in seconds if i wanted to?
