Formatting Dependent On Days Elapsed
Nov 10, 2006
Hi all,
i am fairly new to the use of VB so (1) I am not sure if this is possible and (2) i wouldn't know where to start really so any help is greatly appreciated. I guess that in order to achieve whet i eventually want to (assuming it is possible that is) will be based on using some element of DateDiff. I have searched the forum and whilst i now have a basic understanding of the DateDiff function, i could find nothing that would help solve my problem.
On my form - which is a continuous Form displaying a number of the same type of records - i have a number of fields including InvoiceDate and TaxInvoiceDate. What i have been trying to achieve is the following:
1. Should the number of days since the InvoiceDate and the current date exceeds a certain i would like to format the InvoiceDate field, for example, if more than 30 days but less then 45 days have elapsed then the field could be this and if greater than 45 days then this. This indicates overdue accounts, with me so far? This i have managed to implement this using Conditional Formatting.
2. However, if a date is included in the Tax Invoice Date (meaning that the account has been settled / paid) i want to return / leave the text normal colour / font. This second point i guess will not be achievable when using conditional Formatting?
So, i believe that in order to achieve both points i need to use VB? If so, can anybody help me by suggesting suitable coding and where on the form / fields this code should go.
(Grovel Time) I should add that it only recently that i started using Access and developing my own databases but i have learn't so much from this forum - who knows sometime in the future i may also be able to partake by answering queries myself;)
Regards
Alan
View Replies
ADVERTISEMENT
Dec 3, 2013
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
[ShiftLength] =DateDiff("n", [PunchIn],[PunchOut]) = 572 minutes.
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?
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
Sep 11, 2014
I have a form called subfrm_vactions
rowsource is a query called qryVacations
the query should calculated two things
1 - the difference between start_vac and End_vac in days to calculate the vacation period and put the value in field called Period (working well)
2-the work days which the period between the last day in Previous record (End_Vac) and the (Start_Vac) in the next record (didn't work)
the result is the difference between (End_Vac) and (Start_Vac) in the same record which i don't want
simply i want to calculate the work days.
View 5 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
Dec 14, 2004
Hello everyone,
I have problem with my code here. I am using this code to determine if the LogDate with a weekday of Sunday can be declare as Sunday Work or Regular Work. The 1st week of my entry can determine if the LogDate is Sunday then compute if it reaches a 7 days working, if he is absent even 1 (one) day in his previous working days within the week. His Sunday LogDate is considered as Regular Days instead of Sunday Work. Can anyone tell me what is wrong in this code?
The problem here is the computation of 7 days after 1 week. There should be a 7 days computation every week per month in able to declare Sunday Work or Regular Work. I tried this code but it did not work on 2nd week, 3rd week and 4th week of the month.
How come on 2nd week, Sunday (LogDate) is not considered as Sunday Work even he completed the 7 days (working days) per week? I need help on this ASAP Thanks
Tim
Below is the code i'm using for defining Regular days and Sunday Working Days:
Private Sub LogDate_AfterUpdate()
Dim rst As DAO.Recordset, rs As DAO.Recordset
Dim db As DAO.Database
Dim dteToday As Date, dtePrior As Date
Dim i As Integer
dteToday = LogDate
dtePrior = DateAdd("d", -6, dteToday)
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
rst.FindFirst "[HolidayDate] = #" & dteToday & "#"
'See if the date entered is a holiday
If rst.NoMatch Then
Holiday.Value = 0
Else
Holiday.Value = 1
End If
If Weekday(dteToday) = vbSunday Then 'If dated entered is a Sunday determine if
Dim strSQL As String 'it will be a regular day or a Sunday
strSQL = "SELECT [LogDate] "
strSQL = strSQL & "FROM tblLog "
strSQL = strSQL & "WHERE [LogDate] >= #" & dtePrior & "#"
strSQL = strSQL & " AND [LogDate] <= #" & dteToday & "#"
'Create a recordset of records from date entered and 7 days prior
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveLast
i = rs.RecordCount 'Count the number of days between the two dates
If i = 7 Then 'If there are seven days then it is a Sunday
Sunday.Value = 1
Else
Sunday.Value = 0 'If less than 7 days then it is a regular day
End If
Else
Sunday.Value = 0
End If
DayofWeek.Value = Format(dteToday, "dddd")
End Sub
View 1 Replies
View Related
Jun 14, 2013
Is there a way i can convert the weekend days into working days for example.
5/4/13 1:30 PM is on saturday what function can I use to make this day read 5/6/13 8:00 Am which is monday. Same thing for holidays.
View 1 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
Apr 3, 2013
Client has asked me to create a report showing summary of monthly sales by day. That was easy. I created a query for the month the user selected and then summarized and group the data by day. Client like the result but would like to see zeros on the report for non sales days. Non sales days are days like holidays and there are no sales.
I am thinking of creating an table with 31 days of zero values and then join the two tables in a query? Or, should I create a temporary table with code and then merge the two tables which the existing query which I can then use for the report?
View 4 Replies
View Related
Dec 4, 2006
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
<=DateAdd("w",-12,Date())
Can anyone help, thanks for your time!!!
View 8 Replies
View Related
Dec 4, 2006
I have a query and I need the records to display 3 working days before the 15 working day deadline.
I used the following in the criteria box below the received date field and it doesn't pull the correct number of workdays, it's pulling calendar days instead.
<=DateAdd("w",-12,Date())
Can anyone help, thanks for your time!!!
View 6 Replies
View Related
Oct 14, 2006
Hi
Newbie to Access so bare with me.
I am making a database to log working hours onto for different contracts I work on.
The fields I am having a problem with are "start date and time", "End date and time" and then "Elapsed time"
I need to populate these fields in the format 13/08/2006 17:20 etc.
How do I set the date/time format to do this and then how do I calculate the elapsed time in days,hours and minutes ? (perhaps just hours might do instead of days and hours) I also need to take out non working time ie 6pm through to 8am to make the calculations correct.
Everything else works fine bu I am stuck on this now.
Thanks.
View 5 Replies
View Related
Feb 28, 2006
I am trying to build a form to calculate time elapsed/time difference.
Example if one turns up for work at 1900hrs (start) and ends his duty at 0200hrs the next day (end), how much time has elapsed? I am trying to use this argument :
Elapsed: IIf([end] > [start], Format([end] - [start], "Short Time"), Format(1 - [start] + [end], "Short Time"))
Unfortunately, I am not grtting the desired result. Could anyone please assist?
Thanks
View 2 Replies
View Related
Dec 26, 2006
I rent out a property , on the rental statement I have a field [Datepaid], [RentpaidFrom] and [rentpaidTo]. I would to have a field that can calculate the rent that is paid to show if the rent is in arrears or in Advance. I guess the code would be something like (datediff ???? etc) but i have no idea what it should be. the calculation should appear on the query as i.e.2 days in arrears or 12 days in advance etc
your help would be much appreciated
merry christmas to you all
Jabez
View 4 Replies
View Related
Jul 19, 2006
I have been trying to help a user calculate elapsed time. I referenced the GetElapsedTime from the following Microsoft article
http://support.microsoft.com/kb/210604/
It displays ok on a form or inserting on the detail in a report like it suggests
but the user would also like to total up the elapsed time on the report. Not sure how to do that with the way this function is built.
View 4 Replies
View Related
Feb 22, 2006
I have attached a really simple db where in a query I'm trying to determine the elapsed time between A sent date and a received date. Can some tell me what I'm doing wrong? Thanks..
View 1 Replies
View Related
May 15, 2006
I have nearly achieved this from an example on the Microsoft site but am having problems with the subforms. Can anyone take a look and if possible provide me with guidance as to what I am doing wrong. The instructions are included.Any help with this would be very much appreciated. Kind regards. Bernard
View 6 Replies
View Related
Jul 5, 2014
I am creating query that calculates time interval so that this is used to calculate pay. Employees clock in at [time_in] and clock out at [time_out]. Sometimes time_out is on the next day of time_in.So if is substract time_out - time_in I will get a negative number.I am therefore using the following formula to extract the time interval; Interval: Format([time_out]-[time_in],"Short Time")
this gives out a result such as 3:30 when in fact I would need 3.5 to calculate pay by multiplying with rate.How do I convert 3:30 to 3.5. From the searches I get that I need to multiple 3:30 by 24. But when in put done in the query Interval: Format([time_out]-[time_in],"Short Time")*24
View 8 Replies
View Related
Oct 8, 2011
I am currently in the process of creating an Employee Database. I have a 'staff information' table with several fields, one of which is a 'contracted hours field'. I have a spreadsheet linked to this database and therefore require this field to be formatted correctly in hours and minutes. For example...I wish to store a decimal time of 37.5 as 37:30.
View 1 Replies
View Related
Mar 24, 2007
I have a database with two fields [RECEIVED] and [DISPATCHED] which unfortunately are text fields which I can't change (linked via ODBC). Time is entered in these fields as military time.
I created a query with [DISPATCHED]-[RECEIVED] which works unless the time spans different hours. Example 1605-1555 returns 50 instead of the correct 10.
Any idea how I can use a query to calculate this accurately. I am an intermediate user. Any help appreciated.
View 3 Replies
View Related
Nov 7, 2007
I have a query that calculates elapsed time from TimeIn and TimeOut formatted to hours and minutes. I need be able to mulitply the elapsed time by the hourly wage in order to find the hourly pay.
How do you format the time so that it is compatible to multiply with the wage?
My SQL statement is below. Thanks for your help.
SELECT tTimeCards.ValetFirstName, tTimeCards.ValetLastName, Format([TimeOut]-[TimeIn],"hh:mm") AS HoursWorked, [HoursWorked]*[tValets]![HourlyWage] AS HourlyPay
FROM tValets INNER JOIN tTimeCards ON tValets.ValetID = tTimeCards.ValetID;
View 8 Replies
View Related
Aug 22, 2007
ok i have 4 form fields
dateleft
timeleft
datereported
timereported
and i have a form field that needs to be calulated.
ElapsedTime
i was thinking the output in the elapsed time could be something like
4 days & 8 hours
but i have no idea how to do this, can someone please help?
View 1 Replies
View Related
Feb 24, 2014
Here is the table info I'm working with:
class status changeby changedate statustracking
INCIDENT-RESOLVED- Person A-2/20/2014 9:05 -
INCIDENT-RESOLVED-Person b-2/20/2014 11:57 -
INCIDENT-QUEUED-Person b-2/19/2014 13:57 - 01:24:08
INCIDENT-QUEUED-Person c-2/19/2014 15:21 - 19:29:58
INCIDENT-QUEUED-Person c-2/20/2014 10:51 - 01:06:00
I need to be able to add the statustracking time together per person per status (so person c would need 19:29:58 + 01:06:00 added together). Access keeps trying to add dates to the field or reconfigure the numbers to date ime or zeros if I use any of the data type other then Memo or text.
View 1 Replies
View Related
Jul 8, 2015
I have been trying for about 8 hours to calculate elapsed time between two fields into a third field in Access 2007. Unfortunately, the expression
IIf(IsNull)([Final Suspense]),DateDiff("d",[Routing Status Date],Date()),DateDiff("d",[Routing Status Date],[Final Suspense]))
is not working. Image enclosed....
View 6 Replies
View Related
Mar 14, 2006
Dear All,
I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.
Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.
'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays
Dim intCount As Integer
StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate
Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
'xxxxxxxxxxxxxxxxxxxxxxxx
Can someone please direct me to the right path.
Your help is highly appreciated.
qwerty70
View 1 Replies
View Related
Sep 5, 2013
I have a large table with a number of fields. I have written the query that I want but can't quite work out how to get it to do the main thing I need. I have several fields that I need to generate in the report but what I want it based on is the field called Date Approved.
When the Date Approved has been Active for more than 4 weeks I want to pick it up in the report. I don't want to set a date to start from but pick up everything that has passed 4 weeks in "Date Approved".
View 1 Replies
View Related
