I created a Contact database where I have the date and time as two separate fields in one of the tables. I have them defined as such, but would prefer that the fields wait until I enter data into them and not auto fill with the current information. Is there a property of these fields I can set so that these fields do not get auto filled? Again this database is too large to send, so I hope that you can help without it.
View RepliesI 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'm working with a linked table that has a seperate field for the date, and a seperate field for the time (which show in the linked table as '11/30/1899 9:46:00 AM'). I need to calculate the time difference between 2 entries.
How can I merge the 2 fields into a combined date/time field for my calculations?
Thanks,
Sup
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!
Hi,
I'm combining two date/time fields in a query to an integer. The first field has the date, the second the time. I'd like the resulting integer to be without the opening 0. How can I do that?
That is, these are the two fields:
2006-09-14 (date/time)
15:00:20 (date/time)
And I'd like those two combined to be 60914150020 (integer) in the third field in the query.
Grateful for advice!
I have a table in which each record includes a field showing the time that an event started and then another field which stores "how long did it take", these two fields are both defined as a short Time. I then have a query that runs through the "how long did it take" fields to produce an average time for the whole sequence of events. Two problems:
1. The average comes out as a decimal like 39553.367942 instead of several hours and minutes.
2. When I try to access this value using DLookup on a form I get "#error"
Any suggestions?
thanks
Hi,
Im having a problem adding the date and time to a new record using a form. The 1st method i used was by inserting the default value set to Date() & Time() functions, this worked fine for over a year, but now on some computers on the network the following error pops up "function is not available in expressions in table-level validation expression".
The 2nd method I tried was to add a macro with 'setvalue' commands to the After Update event of one of the first field's that was entered, this entered the same information into the Time & Date field once the first field was updated. This now brings up the macro fault screen and the 'Halt' button has to be pressed to clear the error.
This only happens on the older computers that access the database, the new computers don't bring up any errors.
Can anyone help me with this problem??!
PTDAccess
I have a DateAndTime field in table. This field is about a due date. I want if today is a due date relevant one of the dates ms access alarm me.
Like reminder software I want entry date and alarm me in relevant day. How can I do this?
For anyone that might be able to help me out, I'd very much appreciate it, as this is now number 2 stupid workaround that I'd like to resolve before I need counselling...;)
I have a Date/Time field that I'm importing into my database via .csv files. The field is setup like the example below in every .csv file:
4/2/2007 8:30:00 AM
Access keeps throwing errors and deleting all the field values in this field whenever I try to import. I know that Access does this when a Date/Time field includes data that is not delimited, but these field values seem to be perfectly formatted to me... what can I do to stop Access from chucking these on import?
Right now I'm just importing the data into a "text" field, and then changing it to "date/time" afterwards, but I'm worried that once the table gets too long I won't be able to re-index all those records anymore and my database will be useless since it depends on that field being "date/time" format...
All those better than I feel free to show me how and where I've been stupid:D
In my form I have a calculated field that works out the time elapsed for a job, worked out as the QTY/RUN RATE which gives me the time in hours. However I couldn't format the box as a date/time, as it is a calculation and the times may go over 24 Hours which results in the date/time giving me something like 31/12/1899 16:00 for example.
I've got around this now and I've formatted the results as HH:NN:SS using a public function in a module, but the problem is this is now stored as a text field. I've tried setting this to a number field but the formatting doesn't carry over. I've tried setting a custom format in the table as HH:NN:SS but then all I get is just a bunch of 0's, no calculation results.
So how to format this as a number field properly?
Also for any extra information:
- I know you're not supposed to store calculated results, but this is slightly different, the calculated field is a standalone text box and the bound field is a formatted text box.
- The reason I have to store the calculated results is because my boss wants to eventually see a chart of the total hours of jobs each day, hence why I'm asking if this can be done as a number field.
I have a form that contains a subform. On the main form, there are 3 fields: [IncidentNumber], [OpenDate] and [OpenTime]. The subform, [CtrlLog Detail] , contains the log entry details for each incident number. Every incident number can have many log entries. Two of the fields on the subform are [EntryDate] and [EntryTime]. The fields work correctly and the forms are fine.The issue Im having is when a new incident number is created the [OpenDate], [OpenTime] and [EntryDate], [EntryTime] MUST be equal because of some filter queries for statistics. Right now the user must physically type in the date/times in these 4 fields when they create a new incident number, which means I'm having lots of data entry errors.
Using default value on either [EntryDate] or [EntryTime] on the subform doesnt work because it doesnt create a primary key for the underlying table and the subform will not allow any other entry (due to some other linked values) until that PK is created. The forms are based on tables linked by [Activity_ID] as the primary key. I want to create some VBA code to see that if the Incident number is a new number (meaning the user must type in the new incident number creating the primary key for [Activity_ID]), that access will automatically populate the [EntryDate], [EntryTime] fields to match the [OpenDate], [OpenTime] fields on the main form. The user will manually populate the [OpenDate], [OpenTime].
If the number is an existing number (and the user is simply adding a log entry to the incident number) then I want [EntryDate], on the subform to default to the system date/time ([EntryTime] should remain blank for user entry) while keeping [OpenDate], [OpenTime] unchanged. When the [EntryDate], [EntryTime] fields are populated on creation of a new incident number, I want the PK for the underlying table ([Log_ID]) to be generated.
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
View 8 Replies View RelatedI 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.
Any way of setting up a table containing the following date/time fields.
StartDate
StartTime
EndDate
EndTime
Ultimately I will need to be able to subtract these date/times to get a total time between the two. Should I combine the start date and times in one cell or keep them separate.
In either scenerio, how do I subtract the two in a query for a report?
I am trying to add the current date and time into separate fields after an ID is entered.
Code:
Option Compare Database
Private Sub ID_AfterUpdate()
Me.Date_Received = Date()
Me.Time_Received = Format(Now(), "hh:mm AMPM")
End Sub
I have a table called Neutron2015. I want the current date and time to complete when entries are made in two other fields. So I am looking for this date and time to populate in EXLStart when an entry is made in Processed By, and also the current date and time to appear in EXLEnd when an entry is made in Completed By with the bold word being the field names.
Is this possible, and how difficult is it?
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
There are three tables. An [Action Register] table, a [Calls] table and a [tblContacts] table. The Contacts are common to both.
The Calls table records calls to customer by date
The Action Register table records issues that Customers send in by Open date.
I am trying to make a query where we see the latest date the customer was contacted regardless of which table.
I created two queries.
qryLastCallDate finds the max date from the Call table:
SELECT Max(Calls.CallDate) AS MaxOfCallDate, tblContacts.ContactName
FROM Calls LEFT JOIN tblContacts ON Calls.ContactID = tblContacts.ContactID
GROUP BY tblContacts.ContactName
ORDER BY Max(Calls.CallDate);
qryLastIssueDate finds the max date from the Action Register table:
SELECT Max([Action Register].Open) AS MaxOfOpen, tblContacts.ContactName
FROM tblContacts RIGHT JOIN [Action Register] ON tblContacts.ContactID = [Action Register].Contact
GROUP BY tblContacts.ContactName
ORDER BY Max([Action Register].Open);
The problem I am having is that if I use Left Join I can see all the records from the Calls table but not all from the Action Register table. And vis versa if I use Right Join. This is because sometimes we have calls but no issues in the Action Register table and sometimes issues with no calls.
This is my Left Join query using a Min Max Module I found here: [URL] ....
SELECT qryLastCallDate.ContactName, qryLastCallDate.MaxOfCallDate, qryLastIssueDate.MaxOfOpen, qryLastIssueDate.ContactName, DateValue(MaxOfList([MaxOfCallDate],[MaxOfOpen])) AS [Last Contact], ([Last Contact]+21) AS NextCall
FROM qryLastCallDate LEFT JOIN qryLastIssueDate ON qryLastCallDate.ContactName = qryLastIssueDate.ContactName;
How do I get to see ALL the records from both queries.
I have two tables, and I'm trying to create a crosstab query...I think. One table is Contracts, one is Contacts. Each contract could have multiple contacts, but they are numbered (1, 2, 3...etc.) based on importance. I want to create a query that keeps each contract on one line, and separate fields for each contact and each field of the contact. So a contact will have Title, First, Last, Address, etc. So I want my query to show as follows:
Code:
Contract-----Title1-----First1-----Title2-----First2
ContractX Mr. James Mrs. Sally
I of course need this to be dynamic, so if a contract has 9 contacts, there are fields up to Title9, First9, etc.
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 ....
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?
View 3 Replies View RelatedI 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.
View 4 Replies View Relatedso 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,
View 5 Replies View RelatedI have an inventory database i am working on but am too new to access to know all the inner workings. Quick summary of what I need to accomplish:
I have a databse with several tables each representing different types of parts that we sell. Each table is similar in that the primary key for each is the part#, beyond that though the different types of parts have different specs that go with them. (example: for enclosures, the dimensions are needed but the amperage is not, for circuit breakers the opposite would be true.)
Next I have an inventory table, each record has a unique Control number that we assign as the primary key, then just a part #, notes about its condition (some parts are used) , a shelf location, and a price and date.
I have a very small sample database that i can email to anyone interested in looking at it.
Basically what i think i need to come up with is form that has several aspects to it. A user enters a "CATALOG NUMBER" into a combo box at the top of the form. tabbing out of that field then shows the specs of that catalog number below. the problem is the part numbers would come from a number of tables like the BUS PLUGS, MCCB'S, ENCLOSURES tables, all of which have different types of data. Finally, at the bottom of the form would be a result of a search through the INVENTORY table returning the multiple lines of line by line data where the CATALOG NUMBER matches the one specified above to show the records with the same catalog number but different notes, prices and different shelf locations. anyone care to take a look?
undefined
Hi, can anyone help? Like an idiot I veered away from my intention to put a password on an Access database which holds only a table and a few queries and explored other security options. I now cannot get in to the database at all and get a message telling me that I do not have the necessary acces srights and to consult my administrator. When I look at the properties, I am shown as the administrator, which makes sense but despite trying various, desperate things, I cannot get back in to take the security off. Needless to say I did not create a copy first, so have learned a lesson the hard way. Any advice gratefully received.