Forms :: Calculated Fields - Formatting Box As Date / Time
Jan 13, 2014
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.
Formatting issue regarding elapsed time calculated using DateDiff().
I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.
[PunchIn] = 11/23/2013 8:11:28 AM [PunchOut] = 11/23/2013 5:43:30 PM
Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:
=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct
=Format(([ShiftLength]60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example
=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?
I have a time/date field. The format for the field is short time hh:mm I created a Private Sub for when the field is double clicked the time is populated.On Double Click:
Private Sub Time_of_Call__DblClick(Cancel As Integer) Me.[Time of Call:] = Time() End Sub
It works great. I double click the field and "poof" the time appears. My problem is when the time appears it is in hh:mm:ss..I need it to be in 24 hour/ military time.
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.
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.
Apologies if this has been asked before but I have a problem with being able to format a field to accept an hours and minutes value where the hours is likely to be greater than 23.
I have a form that is related to the number of hours that people spend in respite care. Entry and exit dates are provided in a standard way - date and time - and I have a calculating field that calculates the number of hours and minutes that a person spends at the respite centre based on the entries made in the entry and exit fields.
Because the calculating field is based on a module and I need to have the calculated amount included in the table, I have set up a VBA code to copy the result of the calculation into a controlled field elsewhere in the form.
However, I also need the form to do a simple calculation whereby hours spent away from the centre are subtracted from the total hours calculated by the module, so I need the copied value to be in a format where the calcualtion is able to be performed.
In a nutshell I guess I am looking for a time format for the field that will allow the recipt of a value greater than 23 hours.
Of course perhaps I need a more sophisticated VBA script to perform the calculation behind the scenes but I admit to not being particularly at ease with VBA scripting. If anybody has any suggestions about either a format or a suggestion for a VBA script, I would be very appreciative.
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 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.
I have form with a label and input textbox and a cmmdbutton.In that text box you can fill a number and that number is the scale in minutes.When i click on the cmmdbutton, a table or query shows up with the time and its scale.For example if i put in the form, the number 10 in the textbox and click on the button, A table must show up with the values 00:00, 00:10, 00:20 ,00:30,00:40,00:50,01:00, etc till 23:50.
I want to use an expression to compare 2 dates and calculate the date of the last set of accounts for a company. The user will enter(DD/MM) of the company year end e.g. 31/12. I then want to compare this with todays date (in another field) to ascertain if the month has already passed in the current year and hence calculate the last year end. i.e. If (current month>company month end), year end date is DD/MM/YYYY (where YYYY is taken from today's date), year end date is DD/MM/(YYYY-1). I am not a programmer and although I understand database theory (from the teaching perspective) I am not expert in developing databases.
I have created a simple booking DB, i am try to summarize the booking by centre and date so that when a user checks if there is availability the will be able to see how many have booked an activity and how many spaces are left, i have created a new field that calculates the spare places
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.
Now I can create a Query that adds, subtracts a specific column, but im having a hard time trying to get the information into a form since it don't seem to allow me to pick the query field which is calculated as a datasource. Can someone tell me how to make a calculated query column appear on a form as a field? Such if I type in monday in the regular table, the form will add a few days to the next available game? I have the information/code already in a query that displays the query right in a new table using the Expr method but I can't get the form to just simply display the field. Someone please help!
I see a few people asked close to the same question but nobody never answered so I am thinking it is impossible
Is there a way to use conditional formatting with a date? For instance, in my form I'd like the date field to turn red once a particular date has passed (to show that memberships are expired). Can this be done or am I better to just format a report of upcoming expiration dates?
I have a form where the record source is a table.The form has about 5 calculated fields on it. The screen flickers quite badly and at the bottom says calculating. Sometimes the database locks up.
To stop this flickering i created a query so that the calculations are done in the query. Then i linked the form to the query instead of the table. Am i doing this correctly as i didn't think the table would show any new data that i entered into the form but it does seem to work. Is it because i am only using one table.
I have a form with 2 sub forms which have identical fields from different records. I want to use conditional formatting to indicate a mismatch between 2 of the identical fields. I have been told I should use [NewField] <> [OldField] but I don't know how to complete the syntax. If I call the first sub_form_1 and the second sub_form_2 and the field name is path_length on both, how do I compile the statement?
I've just created a form and I'm trying to move and size the fields but they seem to be grouped together and whatever I do to one happens to all of them. I select one field and just that field has the orange outline but when I move it they all move.
I'm still very new to Access.I have a form with subforms.Some of the fields are calculated fields.I cannot figure out what some of the fields will not update automatcally. Once I click out of the form and then back in, it will update.Tried many things like, Refresh, Requery both on the form and on the text box(es).I wish I could figure out how to attach my test database to show you.
I am preparing an invoice and I have a field called deposit .Now this field calculates %40 of the total of the order .but if the user wishes they can enter what they like .Now this field must be stored in a table for ever and a day and must not change or the invoice will be useless .I can see no other way of doing this and my research tells me (allen Brown) that this is sometimes necessary .
I have a form in datasheet with the fields "Date In" and "Date out" among other.
I want to be able to Change the backcolour of the "Date In" to red, yellow, or green depending on the length of time elapsed between current date and date in. This works fine on the conditional formatting, but now I only want to apply this formatting IF the "Date Out" is empty.
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?
i have a continues form that lists all my active items and I'd like to use it to quickly check if they have all the data inserted, ie entries in each separate table. it looks like this
I perform a simple check on form load chk = DCount("[phID]", "preh", "[phbID] = " & Me.tID) where me.tid is the item ID and this gives me 0 or 1 and writes ok, or missing into the appropriate box.Problem is it tkaes value for the first item and repeats it for eah other, so I get ok or missing throughout the continuos form.How can i force a check for each line?
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!
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.
