Forms :: Conditional Formula For Monitoring - Automatic Computation Of Number Of Days
Jul 8, 2013
I would like to know how to have conditional formula (code) for my monitoring. I have the following fields for form [1]:
Date today
Date Encountered
Date Closed
No. of Days
I need an automatic computation of the No. of days until it is closed. Below is the computation I want to have:
If it is not yet closed: [Date today] minus [Date Encountered]
If already closed: [Date Closed] minus [Date Encountered]
I tried using a code builder:
If Forms![1]![Date Closed].Value<>""Then
Forms![1]![No. of Days]="[Date today]-[Date Encountered]"
Else
Forms![1]![No. of Days]="[Date Closed]-[Date Encountered]"
End if
but there is error.
View Replies
ADVERTISEMENT
Apr 12, 2013
I am trying to create an automatic unique 'number' (actually text) in a form. Here is an example of the format...
1456.R1
1456 is the project number
R stands for revision
1 is the first revision
So, in this database there could be 1456.R2 etc. but there could also be other project numbers, say 2323.R1, 2323. Looking for expression I need to enter to have Access look up the last revision for a specific project and then add 1 to it?
View 10 Replies
View Related
Jan 24, 2014
I have to count the number of days each week (7 days) that a person has worked - simple enough.
But - what if they come off the job within the week?
I have been using the DateDiff function which is fine.
In the database they have a Mobdate and a DeMobDate but if the DeMobDate falls within the week the whole thing falls apart?
View 2 Replies
View Related
Apr 14, 2014
I have a form where a start date is inputted (Inputfrm , StartDate) and a form where the end date of the process is recorded (Inspectionfrm , EndDate) and these both record in the table InputTbl as StartDate and EndDate respectively.
I have created a union query which shows a list of all the dates where there is work recorded (WorkingDatesQry and the column of list of unique dates is "WorkingDate"), and as we run a highly varied schedule depending on time of the year and order numbers I cannot just use a query which says Monday-Friday or Tuesday - Saturday.What I am trying to do is to find the number of days between StartDate and EndDate where there is a date recorded in the WorkingDates query.
View 1 Replies
View Related
Dec 9, 2013
i have a form where I enter two dates i.e. a commence day and a final day and I also have a save button.I would either
a. have a control on the save button which pops up a message if the final day is greater than the commence day and so preventing the save record
b. Or if the final day is entered and is recognised as a date before the commence day then to pop a message saying so and preventing the save record.
View 6 Replies
View Related
Jul 29, 2015
Is there a way to replicate NETWORKDAYS formula in Excel to Ms Access?
View 2 Replies
View Related
Apr 18, 2014
how to have a different conditional colour formatting for a plus and a minus number
If the field value is a Plus Number
The background colour will be Green
If the field value is a Negative Number
The background colour will be Pink
View 6 Replies
View Related
Nov 26, 2004
Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?
There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.
Thank you
View 6 Replies
View Related
Apr 27, 2015
I have a field that displays a date on a form.
I set the conditional formatting to be
Value is Between Date() and Date() + 14
It would only apply the formatting for dates that were equal to the date and +5 days.
I then tried Between Now() and Now() + 14
Same results
I then tried Between Date() and DateAdd("d",14,Date())
No change
I checked the property for the box and the back style is normal.
I am making the back color a light red. I tried to change the fore color red. Nothing.
I displayed what Date() was giving me in a text box control and I displayed what Date() + 14 was giving me in a text box control. They both were showing the correct dates, but my date, which was in between them, was not changing colors.
View 14 Replies
View Related
Oct 23, 2006
hi Iam new to this can anyone help i want to be able to enter phone numbers the code that ive found only seems to allow me to enter first 3 numbers were actually the areA code has 4 can any one give me correct formula
View 5 Replies
View Related
Feb 15, 2005
Howdy,
I'm missing something because my calculations are off on a form. I have a form with a textbox labeled "txtStartupTemperature". The value I insert here will affect another value in a textbox labeled "txtStartupTCF". If my temperature value is <= 25, I will use one formula for my calculation. If my temperature value is >25 then I will use an alternate formula. Both formulas involve exponentials. Here's the code I developed for my public function:
Public Function TCF(ByVal sngValue As Single) As Single
Select Case sngValue
Case Is <= 25
TCF = Exp(3480 * (1 / 298 - 1 / (273 + txtStartupTemperature)))
Case Is > 25
TCF = Exp(2640 * (1 / 298 - 1 / (273 + txtStartupTemperature)))
Case Else
End Select
End Function
In my form's code I've inserted the following:
Private Sub txtStartupTemperature_AfterUpdate()
Me.txtStartupTCF = TCF(Me.txtStartupTemperature)
End Sub
I tested this code with a temperature value of 15. This should calculate out to be 0.67. Instead I'm getting 0.34. Are my formulas written correctly? If so, what else could be going on?
View 4 Replies
View Related
Nov 10, 2006
Hi,
Im putting together a membership data base. the data base has an already existing client base which all have membership numbers in the form of first letter of there sir name, last to numbers of the year and a 4 digit number. So for example Joe Bloggs membership number would be B06001 his sister Betty Bloggs would be B06002 and the guy next door John Doh would be D06001.
What I would like to do it automate this system so everytime I make a new record when I input the sir name into the 'lastname' field on the form it checks the last Membership number and makes a new one. I wouild also like it to change the 06 to 07 when the year changes.
Ive done searches for things like this with Dmax etc but i really could use help with the code as I have no idea how to code this type of thing?
Any ideas on this oue would be great!
Cheers
Phill
View 14 Replies
View Related
Apr 5, 2008
I have a query which returns 2 dates. I need to add an expression which will calculate the number of weekdays (Monday to Friday) between the 2 dates.
Anyone help me?
View 1 Replies
View Related
Sep 9, 2006
Hi,
I need to work out a couple of calculations, the number of days between an engineer starting and finishing a project, the number of days between the project being put on the database in the first place and being completed by an engineer and finally, the difference between these. These maybe simple, but I have no idea tbh (still new to access).
I have a form with the date it was added onto the database, and the engineer fills in the date started and date finished as it happens.
These run off of 2 tables BookingIntbl and TechnicalServicestbl
[BookingIntbl].[Date], [TechnicalServicestbl].[Date Started] and [TechnicalServicestbl].[Date Finished]
I have then got the fields, 'Days with Engineer', 'Days in Repair Centre' and 'Day Difference' for the answers to be displayed.
Any help would be appreciated, Ben
View 2 Replies
View Related
May 4, 2006
In a database, I have the following date fields that I use to track projects that start and stop.
START1
STOP1
START2
STOP2
START3
STOP3
Is there a way I can use a query to calculate the total days the project was worked on? For example if I enter 1/1/06 in START1, 1/10/06 in STOP1, 2/1/06 in START2 and 2/15/06 in STOP2, 3/1/06 in START3 and 3/30/06 in STOP3, I need to calculate the total days. I need it to work regardless if there are 3 start/stops, 2 or just one start/stop.
I really appreciate the help.
Jim
View 14 Replies
View Related
Feb 8, 2007
How would I find if 2 dates fall between 2 other dates?
For example:
This would evaluate to True
StartDate 02/08/2007
EndDate 02/11/2007
StartTestDate 02/09/2007
EndTestDate 02/10/2007
This would also evaluate to true
StartDate 02/08/2007
EndDate 02/11/2007
StartTestDate 02/07/2007
EndTestDate 02/12/2007
This would evaluate to false
StartDate 02/08/2007
EndDate 02/09/2007
StartTestDate 02/10/2007
EndTestDate 02/11/2007
View 5 Replies
View Related
Jun 27, 2007
I'm looking to pickup the cases where the date last paid is greater than 60 days. Can someone help me
select id_case,
dt_last_pay
from noldba.case_rollup;
View 4 Replies
View Related
Mar 6, 2008
I imported an large Excel file with a date column consisting of 3 different date formats and need help on a query to extract the number of days. Examples of the date on that column:
7/6/2006
7/1/06-7/31/06
7/1-7/5
Appreciate any advise.
View 7 Replies
View Related
Dec 14, 2014
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?
View 8 Replies
View Related
Mar 15, 2006
I have a library system and i need to work out the number of days from the date the book was due and the current date. Could anyone help me.
View 2 Replies
View Related
Nov 1, 2006
Hi...
here's a good one...
I have created a database which allows people to enter in when they are off sick. They have to enter in the FIRST DAY of sickness and the LAST DAY of sickness. All of this information is stored in one big table - SICKNESS TABLE. There are no other date fields in that table.
I want to know how many people have been off for 5 TIMES OR MORE in a 365 day period (which may be say, from Oct 2005 to Nov 2006 - so not always in the same year).
I have managed to get a query that will count the number of times someone has been off; but that could be over, for example, 2 years.
How can I get it to tell me if someone has been off for 5 TIMES OR MORE in a 365 day period???
Thanks
Maria
View 7 Replies
View Related
Feb 11, 2007
I've been searching the Queries Forum for help on this issue. I have a database that records loading records for a Taconite facility. Some days contain many records and on other days there are no records because of no loading activity. I create reports that group these loading records by each day, but I really need a quick and dirty report that just shows me the number of days in any month in which we had loading activity (records present). I have not figured out a way to create a query which counts the number of days in a month which contain records. I'm hope I'm explaining this well enough for someone to help me understand what to do.
Thanks ... Erik
View 5 Replies
View Related
Dec 8, 2007
Hello all,
I have 2 tables:
- Employee
- PackageTracker (Contains PackageNumber, date received, date sent, status)
I would like to query:
- which employee has the package
- How long have we had the package (calculate only business days)
- What is the status
I would like the things mentioned above to be ran in one query.
I am stuck with the calculations. I found a macro code to calculate the business days but I am stuck.
Please advise.
Thank you,
John
View 14 Replies
View Related
Apr 22, 2005
I want the number of days between a specific date (roll out date) and 12/31/2005 so that
I can use the number to project revenue for this year.
This is what I'm using but it doesn't give me the right number. I don't know how to transform
something to a datetime variable so maybe that's the reason. Can you help me out?
Expr4: Day(12/31/2005-[Scheduled Roll Out Date]-0.49999)
View 1 Replies
View Related
Aug 7, 2005
At work I can enter +15 in a date field and it automatically sets the date 15 days after todays date. Our companies computer is operating on a ERP system. I would like to do that in an Access database I'm designing for a friend. Any hints.
View 2 Replies
View Related
Feb 6, 2014
I have a contract management database where contracts have a start date and an end date. I also have a date parameter set up whereby I can show live contracts between overlapping records.
I want to be able to count the number of days for each contract that is live between the dates of the parameters.
For example my contract might run from 1st to 30th November but I might want to report from 10th October to 10th November so the number of days I need the query to return is 10 days as my contract is not live before the 1st November. Or my query might run from 1st October to 31st December, then I would need it to show 30 days as it covers the whole of this contract.
View 3 Replies
View Related
