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.
I don't think the below code is right, is it close?
Code:
Option Compare Database
Private Sub Test_AfterUpdate()
If Me.Test = "LR" Then
Me.Due_Date Date = [Date Received] + 4
End If
End Sub
Just trying to add 4 days to a field. (the Date Received field is in the format m/dd AM or PM)
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?
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.
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.
It is like a date calculator.
1. I have 12 unbound text boxes in the unbound form in 6 pairs.
2. Text1 receives the arrival date and Text2 receives departure date, Text3 receives arrival date and Text4 receives the departure date and so on.
3. A person may have any number of arrivals and departures between 1 to 6.
4. The person should not stay in this country for more than 6 years.
I need to calculate from the final departure date, the total number of days the person stayed in this country and the remaining allowable years months and days the person can stay in this country ( deducted from the 6 years period ).
5. Please note the final departure date can be in Text2 or Text4 or Text 6 field.
Anyone help please ? I'm still fairly new to ACCESS so forgive me if I'm completely on the wrong track !
I wish to add a control button to a form that will prompt me to enter a date and then calculate and display a date nn (fixed) number of WORKING days in the future.
I've seen some functions suggested in the forum and as I'm unfamiliar with this topic, I have tried putting together some code (see below) related to an On Click event for the button. I'm having syntax problems and have been battling away at this for some time without success.
Suggestions please !
Steve
Private Sub cmdDate_Click()
Dim FirstDate As Date ' Declare variables.
Dim Number As Integer
Dim dtEndDay As Date
Dim dtinterimday As Date
Dim intcount As Integer
Dim lnginterimdate As Long
Dim intdays As Integer
Dim Msg
FirstDate = InputBox("Enter a date - dd/mm/yy")
Number = 20
dtinterimday = FirstDate
intdays = Number
Do Until intcount = Abs(intdays)
If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1
lnginterimdate = dtinterimday
If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then
intcount = intcount + 1
End If
End If
Loop
Msg = "New date: " & dtinterimday
MsgBox Msg
End Sub
Hi
I have 3 date fields. I am trying to work out number of days when
=[FIELD1 - FIELD2 OR FIELD3]
should do the calculation in field 4 NUMBER OF DAYS. but it comes up with date only. FIELD1 will always be filled, if field 2 is field then the number of days must be based on that. but if field2 is blank then it should be field1 - field 3. I may need a help on the formula does it need any if, else statement or not. If so can someone please help
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?
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
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
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
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;
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.
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 RelatedHi...
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
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
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
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)
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 RelatedI 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.
My table : Id Start End
1 2013.11.20 2014.01.10 2 2014.01.17 2014.01.30 3 2014.01.06 2014.01.27 4 2014.02.04 2014.02.23 5 2014.02.07 2014.02.17 6 2014.02.21 2014.03.08
How to calculate total number of unique days? Date ranges are overlapped.
This is the criteria of the query I am using
<=Now()+91
How can I change that so I can input the number of days when prompted ...
I am trying to calculate the number of days between incidents.
If I have an incident that happened on the 10th August and another on the 15th August then this would be 5 days, the next incident occurred on the 28th August so that would be 13 days. The problem I am having is how to get access to use the latest date rather than the first date.
I need a litle help to solve a syntax qustion:)
This is my query:
Forfall: Date()+([svarfrist].[svarfrist])
The case is to use this day' date and add the number in the table "svarfrist". The point is that the user are able to change the nomber of pluss days from time to time.
This qery running, but it only give bad number of days...
Hope somone have the time to give med ab advice.
Morten
HI, this should be relatively simple, but for some reason I cannot figure it out.
I have a field titled "DEADLINE" - a user inputs a date in this field (the datatype for this filed id date/time)
What I want to do is, on the form create a text box which takes the deadline date - todays date to give me the number of days to the deadline date. Once the dealine date has passed I then want to turn the counter to Red to show it is overdue. If there is no dealine then I want the field to say something like "No Deadline Set"